?
Solved

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

Posted on 2003-03-25
14
Medium Priority
?
716 Views
Last Modified: 2010-05-19
Running Office XP Professional at SP-2

I have a word macro that opens an excel file by:

myfile$=inputbox("enter filename")
x = Shell("C:\Program Files\Microsoft Office\Office10\EXCEL.EXE c:\aaatmp\" & myfile$ & ".xls", 4)

The first time I run it things work fine--the excel application is started, and myfile$ opens.  Naturally, my hidden personal.xls workbook also opens.

the next time I run the macro, a second version of the Excel Application opens up and gives a message "Personal.xls is locked for editing.  Every time I run it, I get another application and another message.

Is there any way to get shell to recycle the existing the application?

While I'm asking questions, I don't like putting full path in Shell("c:...offic10\excel.exe"). The simpler way, Shell("excel.exe") seems to work fine, but I've never seen that documented.  Do you think its safe to drop the path name from registered applications?

Bob
0
Comment
Question by:rberke
[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
14 Comments
 
LVL 14

Expert Comment

by:aelatik
ID: 8204859
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
0
 
LVL 27

Accepted Solution

by:
Dabas earned 500 total points
ID: 8204906
Answering your second question first:
Yes, it is a good idea to drop the path name from registered applications.

Now to your first question:

When you use Shell, you are opening a new process, and even though you have reached your objective, you do not have any futher control over it.

In this case, every call to Shell opens a NEW Excel window. (As opposed to being in Excel, then opening a second workbook... Excel is open only once, and knows that your personal.xls is already opened, so it will not open it again)

To overcome this problem, in your VB IDE, click on Tools -> References

Tick next to Microsoft Excel

Then declare the following at the top of your code:

Dim xl as New Excel.Application
Dim wb as Excel.Workbook

Instead of the Shell call, use the following code:

    Set wb = xl.Workbooks.Open(c:\aaatmp\" & myfile$ & ".xls")

    xl.Visible = True 'Excel has been running hidden until now

You should be able to open as many workbooks as you want this way. Since they are all "Children" of the xl object, Excel will be running only once, thereby solving the problem.

Dabas
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8204926
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
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Expert Comment

by:dmang
ID: 8205320
Is there a reason you are using the Shell command rather than instantiating with createobject?
0
 
LVL 5

Author Comment

by:rberke
ID: 8206218
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?
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8206255
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
0
 
LVL 5

Author Comment

by:rberke
ID: 8206438
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
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8206474
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
0
 
LVL 5

Author Comment

by:rberke
ID: 8206512
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
0
 
LVL 5

Author Comment

by:rberke
ID: 8206979
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


0
 
LVL 27

Expert Comment

by:Dabas
ID: 8207016
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
0
 
LVL 5

Author Comment

by:rberke
ID: 8207719
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
0
 
LVL 27

Expert Comment

by:Dabas
ID: 8207824
Bob:

Well done!

Using GetObject is an excellent idea!

Dabas
0
 
LVL 5

Author Comment

by:rberke
ID: 8207845
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
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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 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
Course of the Month12 days, 17 hours left to enroll

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