Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2003 Can I stop the 'update links' option when file is opened? | Links and Linking in Excel | |||
charts missing when 2003 file opened in 2007 | Charts and Charting in Excel | |||
data missing in charts original file 97-2003 opened 2007 and then. | Charts and Charting in Excel | |||
stop excel file opened as read only if already opened by another u | Excel Programming | |||
Excel 2000 file when opened in Excel 2003 generates errors? | Excel Discussion (Misc queries) |