Solved

Open only one instance of Excel file

Posted on 2004-08-10
4
331 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 33

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 33

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 33

Author Comment

by:Mike Eghtebas
ID: 11764872
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

744 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now