Link to home
Start Free TrialLog in
Avatar of Mike Eghtebas
Mike EghtebasFlag for United States of America

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(FilePath)
   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
Avatar of bramsquad
bramsquad
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mike Eghtebas

ASKER

Thank you  bramsquad!

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
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(FilePath)
   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