Bug when macro tries to open allready opened file

[Snoopy]

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

 

[MattShoreson]

search for 'function IsFileOpen' on the net.

 

[Chip Pearson]

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]

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