Mike Eghtebas
asked on
Open only one instance of Excel file
From VB 6 Form: LaunchExcelFile "MyFile.xls"
--------
In a module:
Sub LaunchExcelFile(FilePath As String)
Dim xlApp As New Excel.Application
Dim xlWkBk As Excel.WorkBoopk
Set xlWkBk = xlApp.Workbooks.Open(FileP ath)
xlAp.Visible=True
Set xlWkBk = Nothing
Set xlApp = Nothing
End Sub
Because "xlAp.Visible=True" will be removed from above code, I don't want to open a new copy of MyFile.xls with each use.
Thanks,
Mike
--------
In a module:
Sub LaunchExcelFile(FilePath As String)
Dim xlApp As New Excel.Application
Dim xlWkBk As Excel.WorkBoopk
Set xlWkBk = xlApp.Workbooks.Open(FileP
xlAp.Visible=True
Set xlWkBk = Nothing
Set xlApp = Nothing
End Sub
Because "xlAp.Visible=True" will be removed from above code, I don't want to open a new copy of MyFile.xls with each use.
Thanks,
Mike
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi bramsquad,
It seems a Public variable (Public xlApp As New Excel.Application in amodule) causes more problems it solves.
-------------------------- ----
A. Using the original code (discussed here as background to the question):
Sub LaunchExcelFile(FilePath As String)
Dim xlApp As New Excel.Application
Dim xlWkBk As Excel.WorkBoopk
Set xlWkBk = xlApp.Workbooks.Open(FileP ath)
xlAp.Visible=True
Set xlWkBk = Nothing
Set xlApp = Nothing
End Sub
Evalution of step "A" bove:
- Makes a new instance of MyFile.xls each time (not acceptable).
- When another excel file manually opens, after opening of MyFile.xls, it attaches itsel to the latest instance of MyFile.xls (not quite acceptable).
- If Other.xls file is manully has been launched and the user tries to open Other.xls again, the original Other.xls will maximize, ignoring a second latest request (normal behavior of excel). But,
- If Other.xls file has been opened first and then MyFile.xls is opened, using above code, a second instance of XL would appear (normal but...). Any additional request to open Other.xls (already open), will attach itself to the latest instant of MyFile.xls in read-only mode (this is no good. User will have two instance of the same file opened).
Conclusion: Above code is no good.
---------------------
B. Using your solution (making xlApp Global) dosn't change anything. The same behavior as discussed in section "A" bove.
---------------------
C. Using your solution plus moving "Set xlApp = Nothing" away (to form's unload event), opens only one instance of Excel no matter what file opens first attaching any additional file to that instance (this seems a acceptable solution). This way, I guess I will not be able to keep MyFile.sxl macro hidden (can't remove "xlAp.Visible=True" from the code because first istance being hidden, keeps Other.xls hidden when launced after opening of the macro. I have to protect my macro file by a password).
Final Conclusion: Use solution C and keep macro (MyFile.xls) read only; but add a code to close MyFile.xls from VB form after it is no longer necessary to have it open.
Any idea how I can close it? Could you give me code to do that or you want me to post a new question?
Thanks,
Mike
It seems a Public variable (Public xlApp As New Excel.Application in amodule) causes more problems it solves.
--------------------------
A. Using the original code (discussed here as background to the question):
Sub LaunchExcelFile(FilePath As String)
Dim xlApp As New Excel.Application
Dim xlWkBk As Excel.WorkBoopk
Set xlWkBk = xlApp.Workbooks.Open(FileP
xlAp.Visible=True
Set xlWkBk = Nothing
Set xlApp = Nothing
End Sub
Evalution of step "A" bove:
- Makes a new instance of MyFile.xls each time (not acceptable).
- When another excel file manually opens, after opening of MyFile.xls, it attaches itsel to the latest instance of MyFile.xls (not quite acceptable).
- If Other.xls file is manully has been launched and the user tries to open Other.xls again, the original Other.xls will maximize, ignoring a second latest request (normal behavior of excel). But,
- If Other.xls file has been opened first and then MyFile.xls is opened, using above code, a second instance of XL would appear (normal but...). Any additional request to open Other.xls (already open), will attach itself to the latest instant of MyFile.xls in read-only mode (this is no good. User will have two instance of the same file opened).
Conclusion: Above code is no good.
---------------------
B. Using your solution (making xlApp Global) dosn't change anything. The same behavior as discussed in section "A" bove.
---------------------
C. Using your solution plus moving "Set xlApp = Nothing" away (to form's unload event), opens only one instance of Excel no matter what file opens first attaching any additional file to that instance (this seems a acceptable solution). This way, I guess I will not be able to keep MyFile.sxl macro hidden (can't remove "xlAp.Visible=True" from the code because first istance being hidden, keeps Other.xls hidden when launced after opening of the macro. I have to protect my macro file by a password).
Final Conclusion: Use solution C and keep macro (MyFile.xls) read only; but add a code to close MyFile.xls from VB form after it is no longer necessary to have it open.
Any idea how I can close it? Could you give me code to do that or you want me to post a new question?
Thanks,
Mike
ASKER
Public xlApp As New Excel.Application
alon didn't work. For some reason, I had to move
Set xlApp = Nothing
to form Unload event as well.
Regards,
Mike