?
Solved

Open only one instance of Excel file

Posted on 2004-08-10
4
Medium Priority
?
371 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 8

Accepted Solution

by:
bramsquad earned 1000 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

777 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