VBScript code to keep Excel file open

Question

I have a VBScript code to open an excel file, run a macro and close it. Fine.

Now, the only thing I want to change is to leave the file open.

If I remove the 2 lines of code xlApp.activewindow.close and xlApp.Quit, then the workbook and the application are closed anyway, but they remain open in the background (Excel process still active in Task Manager). Hence, it is impossible to re-run the macro later on the same file by calling the script again (which is exactly what I want to do).

Why?

Here is the code:

Option Explicit

On Error Resume Next

MyTest

Sub MyTest()
    Dim xlApp
    Dim xlBook
    Dim fpath 
    Dim fname 

    ' Excel application running? if not, open Excel
    On Error Resume Next    
    Set xlApp = GetObject(, "Excel.Application")
    If xlApp <> "Microsoft Excel" Then
        Set xlApp = CreateObject("Excel.Application")
    End If
    Err.Clear

    ' correct Excel file open? if not, open it
    fpath = "D:\Desktop\"
    fname = "MyTest.xls"

    xlApp.Workbooks(fname).Activate
    If Err = 0 Then
        ' no error, so it has been possible to activate the workbook
        Set xlBook = xlApp.Workbooks(fname)
    Else
        ' unable to activate, so workbook was not open -> open it now
        Set xlBook = xlApp.Workbooks.Open(fpath & fname, 0, True)
    End If
    Err.Clear

    ' now run the desired macro in the excel file
    xlApp.Run "HelloWorld"

    ' WANT TO CHANGE THIS
    xlBook.saved = True
    xlApp.activewindow.close

    ' AND THIS
    xlApp.Quit


    Set xlBook = Nothing
    Set xlApp = Nothing
End Sub

Sorry if I was unclear. What does not work is to leave the file open. If I remove the 2 lines of code `xlApp.activewindow.close` and `xlApp.Quit`, then the workbook and the application are closed anyway, but they remain open in the background (Excel process still active in Task Manager). Hence, it is impossible to re-run the macro later on the same file by calling the script again (which is exactly what I want to do). — user986990, Oct 10 '11 at 16:53
Do you still leave those two lines in the script?: Set xlBook = Nothing Set xlApp = Nothing — , Nov 25 '11 at 09:07
Why do you want to leav the file open and then want to re-run the script on the same file - did you want to work on the file in the meantime? You could potentially look for the existing file when the script is rerun, or kill existing Excel processes (but the later option may cause your grief if you are actively uisng Excel) — brettdj, Dec 25 '11 at 11:55

score 3 · Answer 1 · answered Oct 10 '11 at 18:52

3

You just need to make your new instance of Excel visible. Do this right after creating it:

xlApp.Visible = True

answered Oct 10 '11 at 18:52

Jean-François Corbett

37,420
30
139
188

score 0 · Answer 2 · edited Jun 25 '13 at 20:32

0

This line of code will close your current activated workbook (by now, it is D:\Destop\MyTest.xls);

xlApp.activewindow.close

This line will quit Excel application;

xlApp.Quit

edited Jun 25 '13 at 20:32

Matt

74,352
26
153
180

answered Oct 10 '11 at 02:50

DzungHoang

1
1

I probably was a bit unclear descibing my problem. I am aware of what the 2 lines of code that you comment are doing. Please see my comment to the original posting for a better explanation of what is going wrong... – user986990 Oct 10 '11 at 16:50

VBScript code to keep Excel file open

2 Answers2

Linked