Solved

Open only one instance of Excel file

Posted on 2004-08-10
4
357 Views
Last Modified: 2012-08-13
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

0
Comment
Question by:Mike Eghtebas
  • 3
4 Comments
 
LVL 8

Accepted Solution

by:
bramsquad earned 250 total points
ID: 11763445
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11763545
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11764797
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
 
LVL 34

Author Comment

by:Mike Eghtebas
ID: 11764872
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question