Word opens Excel with Shell("excel.exe filename"). How to recycle instance of Excel and avoid locking Personal.xls

Try avoiding use of full pathnames, try this to excute excel.

Dim MyExcell
Set MyExcell = CreateObject("Excel.Application")
    MyExcell.Workbooks.Open "c:\fname.xls"
    MyExcell.Visible = True
    Set MyExcell = Nothing

Happy Programming...
Ayhan Elatik

I see you got a similar answer while I was preparing mine!

Although it is a good idea to set xl = Nothing eventually, (to return the pointer to memory), you should not do so immediately as suggested by aelatik.
This will cause a second Excel application to open when you get to this code again, which is exactly what we want to avoid.

Dabas

Is there a reason you are using the Shell command rather than instantiating with createobject?

Its great to have so much help!!!  

dmang-I use Shell for the most common of all reasons - ignorance.  I never had formal vb training, but once did a medium size project with Access 2.0.  Nowadays, I just start a Word macro and do my best.  Word Basic help is pretty inferior, but it lead me to shell, so thats what i used.

Now, since I'm self taught let's make sure my terminology is correct. When I say there is a single running instance of excel, I mean that a File/Exit will shut down all open Excel documents.  If there are multiple instances running, a File/exit will only shut down its own documents. (If you do an alt-tab in XP, there is a slightly different icon for an excel Document versus an Excel instance.)

I have tried both aelatic and dabas's suggestion. They seem to work the same, and are slightly better than shell, but not much.

I did some playing starting with this:

Sub macro1() ' assigned to alt-k for easy testing
Call macro2("c:\aaatmp\file1.xls")
Call macro2("c:\aaatmp\file2.xls")
End Sub

Sub macro2(file)
dim MyExcel
Set MyExcel = CreateObject("Excel.Application")
MyExcel.Workbooks.Open file
MyExcel.Visible = True
Rem Set MyExcel = Nothing
End Sub


calling Macro1 now launches two instances of Excel.  But two strange things:

1. Personal.xls is not opened. This is a minor problem for me, but I could live with it.
 
2. A File/Close, shuts down the whole instance, not just the open file. Its almost as if I did a File/exit.

I have tried changing the dim to be "static myexcel", and also tried making it a global variable. I've also tried unreming the set myexcel=nothing.  Nothing seem to change.

Any suggestions?

rberke:

You did not follow my suggestion.

Thge dim MyExcel has to be EXTERNAL to both macros.

The way you have it, the Excel object is created every time you run macro2, which defeats the object of the exercise.

Can you show the code you had when you tried making the variable global?

Dabas

Global myexcel
Sub macro1() 'assigned to alt-k
Call macro2("c:\aaatmp\file1.xls")
Call macro2("c:\aaatmp\file2.xls")
End Sub
Sub macro2(file)
Set Myexcel = CreateObject("Excel.Application")
Myexcel.Workbooks.Open file
Myexcel.Visible = True
Rem Set Myexcel = Nothing
End Sub

Try Set MyExcel = CreateObject at the beginning of macro1, instead of macro2.

The way you have it, you are creating two applications. By placing it in macro1 you are only creating one.

Dabas

dabas

Ooops, that was from aelatik.  I think your code is better and I haven't played with it enough.  I'll get back to you soon.

Bob

OK, I'm getting closer.
 
Test #1.  I open an excel document called file0.  I get xlsInstance#1 with 1 workbook.

test #2: I open word and call macro1. I get xlsInstance#2 with 2 workbooks(file1&file2).  (I really wanted to reuse xlsIntance#1.) Also, Personal.xls was not opened.

test #3: I call macro2 and successfully reuse xlsInstance#2 which now has 4 workbooks.  This shows I am making progress toward my goal.

test #4a. I do a File/Exit on xlsInstance#2

test #4b. I call macro1 and things get strange.  Excel opens up and the task bar shows file1 and file2, but they aren't really there. I do a file/exit to get out of word, and find I have to do it 3 times before I am fully out of word.

DISCUSSION:

I have no idea why Personal.xls isn't being opened.  This is only a small problem which I can live with.

The other problems are more severe. Our code creates its own xlsInstance then tries to reuse it. As long as the instance is still running, it can be reused with no trouble. but, once it is shut down at #4a, further attempts to reuse it appear unpredictable.  see REM #1 and REM #2 for possible fix.

Here is my code

Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Option Explicit

Sub macro3(file) '==================================\

rem #1 Ideally, we would put in code that says
rem      if ThereIsAManuallyCreatedInstance then
rem          set xl = ManuallyCreatedInstance
rem      else
rem          set xl = createobject(excel.application)
rem      endif

rem #2 Alternatively, we would check the program created instance is still available.
rem      if xl.StillActive(or whatever property is called) then
rem         msgbox "reusing old"
rem      else
rem         if xl is not nothing then
rem            set xl = nothing 'free memory from before
rem         endif
rem         set xl = createobject(excel.application)
rem        
rem      endif


Set wb = xl.Workbooks.Open(file)
xl.Visible = True
Rem Set Myexcel = Nothing
End Sub '============================================/

Sub Macro1() ' assigned to alt - k
Call macro3("c:\aaatmp\file1.xls")
Call macro3("c:\aaatmp\file2.xls")
End Sub

Sub Macro2() ' assigned to alt - j
Call macro3("c:\aaatmp\file3.xls")
Call macro3("c:\aaatmp\file4.xls")
End Sub

Bob:

I think you now understand exactly what the issue is about!

It is my belief that your original question as stated above has been answered.

Your new #1 and #2 options are not that simple to answer, and I would suggest you create a new question so that more experts can participate in the solution.

As to the personal.xls not opening, you can add it to the list in macro1

Dabas

For posterity, here is the final solution which does just about everything I want.  Notice that the variable are now local and temporary, so we don't need to do a set = nothing.

Thanks again to everybody for your help.  You guys are great.

Sub OpenExcel(file)
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
On Error Resume Next
Err.Clear
Set xl = GetObject(, "excel.application")
If Err.Number > 0 Then 'can't get instance so create one
 Set xl = CreateObject("excel.application")
 Set wb = xl.Workbooks.Open("C:\PERSONAL.XLS")
End If

Set wb = xl.Workbooks.Open(file)
xl.Visible = True
End Sub

Bob:

Well done!

Using GetObject is an excellent idea!

Dabas

The only thing I don't like about the solution is that personal.xls has to have its whole path listed.

Other than that it is pretty cool.

Point go to dabas, with an honorable mention to aelatik.

Bob