Bug when macro tries to open allready opened file

Hey guys
After a horrible nights sleep, caused by spooky VBA macro-gosts that
are harassing me and chasing me through the bright and flowering fields

of Visual Basic and into the dark woods of soul-searching lack of
VBA-qualifications, I have no other choice than ask You to conjur one
of these small devils in mind, by helping me solve my VBA-problem:

I try - and manage (hurray!!) - to create a macro that copy a specified

range from one workbook, then open a specified new one and finaly paste

the data (pluss - of course - do some other stuff) into this
predefinied workbook/sheet.
My problem is that my macro has rather bad manners when I try to run
the macro in the case where this specified workbook is allready opened.

How can I design my macro to check out and possibly close the file in
case it is opened, and open if it is not?


Proberbly an easy task (for a VBAxorsist), but still...so is swimming -

if one can.


If one of You feel the calling to give me peace in mind, I will be
greatfully happy and thankful for ever.


Regards
Snoopy


I have listet the beginning of my VBA-macro:


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Workbooks.Open Filename:= _
"Y:\\UnderlagDummy.xls"
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1").Select

search for 'function IsFileOpen' on the net.

You can test for a file open condition with the following code:

Public Function IsFileOpen(FileName As String) As Boolean
Dim FileNum As Integer
Dim ErrNum As Integer

On Error Resume Next ' Turn error checking off.
FileNum = FreeFile() ' Get a free file number.
' Attempt to open the file and lock it.
Open FileName For Input Lock Read As #FileNum
Close FileNum ' Close the file.
ErrNum = Err ' Save the error number that occurred.
On Error GoTo 0 ' Turn error checking back on.

' Check to see which error occurred.
Select Case ErrNum

' No error occurred.
' File is NOT already open by another user.
Case 0
IsFileOpen = False

' Error number for "Permission Denied."
' File is already opened by another user.
Case 70
IsFileOpen = True

' Another error occurred.
Case Else
Error ErrNum
End Select
End Function


Then, you can call this with code like

If IsFileOpen("C:\Test\Test2.xls") = True Then
' file is open by some program
Else
' file is not open
End If


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Snoopy" <[email protected]> wrote in message
news:[email protected]...
> Hey guys
> After a horrible nights sleep, caused by spooky VBA macro-gosts
> that
> are harassing me and chasing me through the bright and
> flowering fields
>
> of Visual Basic and into the dark woods of soul-searching lack
> of
> VBA-qualifications, I have no other choice than ask You to
> conjur one
> of these small devils in mind, by helping me solve my
> VBA-problem:
>
> I try - and manage (hurray!!) - to create a macro that copy a
> specified
>
> range from one workbook, then open a specified new one and
> finaly paste
>
> the data (pluss - of course - do some other stuff) into this
> predefinied workbook/sheet.
> My problem is that my macro has rather bad manners when I try
> to run
> the macro in the case where this specified workbook is allready
> opened.
>
> How can I design my macro to check out and possibly close the
> file in
> case it is opened, and open if it is not?
>
>
> Proberbly an easy task (for a VBAxorsist), but still...so is
> swimming -
>
> if one can.
>
>
> If one of You feel the calling to give me peace in mind, I will
> be
> greatfully happy and thankful for ever.
>
>
> Regards
> Snoopy
>
>
> I have listet the beginning of my VBA-macro:
>
>
> Selection.PasteSpecial Paste:=xlPasteValues,
> Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Application.CutCopyMode = False
> Selection.Copy
> Workbooks.Open Filename:= _
> "Y:\\UnderlagDummy.xls"
> Selection.PasteSpecial Paste:=xlPasteValues,
> Operation:=xlNone,
> SkipBlanks _
> :=False, Transpose:=False
> Range("A1").Select
>

Thanks guys
I intend to try out these advices. I just don't get it at once, but
will struggle forward to suksess - costing blood, swet and a couple of
beers
I wish You a very nice day and easter Holyday