Excel 2003 - VBA File already opened

[thomas]

Hello,

I want to open an Excel file through VBA and i first test if it's already
opened or not. If it's already opened i just reactivate it.

My problem is that sometimes the file is re-opened because it's already
opened but in an other instance of Excel.

How can i check all the instances of Excel?

Thanks

 

[Spreadsheet Solutions]

This might be one solution.

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error Goto 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub

 

[thomas]

thanks but does it work whatever the instance of Excel?


"Spreadsheet Solutions" <[email protected]> a écrit dans le message de
groupe de discussion : [email protected]...
This might be one solution.

Function IsFileOpen(FileName As String)
Dim iFilenum As Long
Dim iErr As Long

On Error Resume Next
iFilenum = FreeFile()
Open FileName For Input Lock Read As #iFilenum
Close iFilenum
iErr = Err
On Error Goto 0

Select Case iErr
Case 0: IsFileOpen = False
Case 70: IsFileOpen = True
Case Else: Error iErr
End Select

End Function

Sub test()
If Not IsFileOpen("C:\MyTest\volker2.xls") Then
Workbooks.Open "C:\MyTest\volker2.xls"
End If
End Sub

 

[Spreadsheet Solutions]

Thomas;

I think I read it wrong.
Do you mean that you (or some one else) might have two Excel windows open at
the same time ?

Regards;
Mark Rosenkrantz

 

[thomas]

Yes this is what i meant.

I noticed that sometimes the file was not noticed as opened and i guess this
is because it was opened in an other instance of Excel.
If opened in the same instance, no problem. It was noticed as opened


"Spreadsheet Solutions" <[email protected]> a écrit dans le message de
groupe de discussion : [email protected]...
Thomas;

I think I read it wrong.
Do you mean that you (or some one else) might have two Excel windows open at
the same time ?

Regards;
Mark Rosenkrantz

 

[Spreadsheet Solutions]

Well;

Then I don't have any idea.
I never ran into such problem and quit frankly at the moment I do not have
an answer...

Sorry...

 

[Jon Peltier]

I don't have the specific code on hand to do this, but I have done it in the
past.

Essentially you have to use Windows APIs to check out each main window on
your system, see which are running Excel, and see which child windows of the
Excel windows represent the particular workbook.

- Jon