Excel 2003 - VBA File already opened
 

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

Excel 2003 - VBA File already opened
 

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
---

More, or other : http://www.cpearson.com/Excel/ISFILEOPEN.ASPX


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands - Those who live some 18 feet below sea level
--
E:
W: www.spreadsheetsolutions.nl
--
"thomas" <nomail wrote in message
...
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

Re : Excel 2003 - VBA File already opened
 

thanks but does it work whatever the instance of Excel?


"Spreadsheet Solutions" a écrit dans le message de
groupe de discussion : ...
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
---

More, or other : http://www.cpearson.com/Excel/ISFILEOPEN.ASPX


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands - Those who live some 18 feet below sea level
--
E:
W: www.spreadsheetsolutions.nl
--
"thomas" <nomail wrote in message
...
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

Excel 2003 - VBA File already opened
 

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
Uithoorn
Netherlands - Those who live some 18 feet below sea level

"thomas" <nomail wrote in message
...
thanks but does it work whatever the instance of Excel?


"Spreadsheet Solutions" a écrit dans le message de
groupe de discussion : ...
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
---

More, or other : http://www.cpearson.com/Excel/ISFILEOPEN.ASPX


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands - Those who live some 18 feet below sea level
--
E:
W: www.spreadsheetsolutions.nl
--
"thomas" <nomail wrote in message
...
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

Re : Excel 2003 - VBA File already opened
 

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" a écrit dans le message de
groupe de discussion : ...
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
Uithoorn
Netherlands - Those who live some 18 feet below sea level

"thomas" <nomail wrote in message
...
thanks but does it work whatever the instance of Excel?


"Spreadsheet Solutions" a écrit dans le message de
groupe de discussion : ...
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
---

More, or other : http://www.cpearson.com/Excel/ISFILEOPEN.ASPX


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands - Those who live some 18 feet below sea level
--
E:
W: www.spreadsheetsolutions.nl
--
"thomas" <nomail wrote in message
...
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

Excel 2003 - VBA File already opened
 

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...

--
Mark Rosenkrantz

"thomas" <nomail wrote in message
...
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" a écrit dans le message de
groupe de discussion : ...
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
Uithoorn
Netherlands - Those who live some 18 feet below sea level

"thomas" <nomail wrote in message
...
thanks but does it work whatever the instance of Excel?


"Spreadsheet Solutions" a écrit dans le message de
groupe de discussion : ...
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
---

More, or other : http://www.cpearson.com/Excel/ISFILEOPEN.ASPX


--
Regards;
Mark Rosenkrantz
--
Spreadsheet Solutions
Uithoorn
Netherlands - Those who live some 18 feet below sea level
--
E:
W: www.spreadsheetsolutions.nl
--
"thomas" <nomail wrote in message
...
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

Excel 2003 - VBA File already opened
 

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
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"thomas" <nomail wrote in message
...
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