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

LVL 34
Mike EghtebasDatabase and Application DeveloperAsked:
Who is Participating?
 
bramsquadConnect With a Mentor Commented:
this code is the reason youre creating a new instance everytime its ran

Dim xlApp As New Excel.Application

your best bet would be to try to define the variable globally and then reset the values in the function you have written...just keep in mind every NEW instance of the excel object you create will be independent of the previous ones, thats why its opening up a window everytime you run this function

~b
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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
0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
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

0
 
Mike EghtebasDatabase and Application DeveloperAuthor Commented:
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.