Hiding the Excel application during run-time

Question

I'm trying to hide Excel during a long script in which I do some web-scraping. I'm able to hide the application just fine, the problem is that when I change .Visible back to True, I'm getting 1-2 more additional applications (just empty Excel shells). I'm guessing one of these are my PERSONAL.xlsb workbook, but I'm not sure what the other one is - sometimes I get one extra, sometimes I get two. The only way I can close these shell files is by ending the EXCEL.EXE process via task manager.

I've tried hiding just the main window (Windows(1)) as well to no avail (it just hides the workbook, not the application):

Sub Test()

Windows(ThisWorkbook.Name).Visible = False

Application.Wait (Now + TimeValue("0:00:05"))

Windows(ThisWorkbook.Name).Visible = True

End Sub

How can I just have my main workbook re-appear?

Sample code:

Sub Test()

Application.Visible = False

Application.Wait (Now + TimeValue("0:00:05"))

Application.Visible = True

End Sub

Edit: This is on Windows 7, Excel 2016

Edit2: Running just Application.Visible = True by itself also gives me these two phantom applications.

Edit3: The issue definitely has to do with having macros stored in the PERSONAL.xlsb file - when I go onto a fresh computer and add a new macro to this workbook, I can reproduce the issue. However, I'm still not sure how to avoid it...

Task manager:

The script that opens Excel from Filemaker Pro:

Open URL [With dialog:Off; "C:\Users\Username\Desktop\TestFile.xlsm"]

Inside TestFile.xlsm:

Private Sub Workbook_Open()

Application.Visible = False

'Refresh a query in the Excel workbook that is linked to Filemaker Pro

'Webscrape, webscrape, webscrape from a worksheet inside this Excel document
'to a hidden Internet Explorer Window (ewww, IE!)

Application.Wait (Now + TimeValue("0:00:05"))

Application.Visible = True

'Either close Excel completely or reload my main instance of Excel

End Sub

I've realized that I can just completely quit Excel with Excel.Application.Quit, but I haven't decided if I want to exit out right away, or repaint a UserForm in Excel that summarizes the import process

Answer 1

I was able to reproduce your issue. First, I tested hiding the Application without having PERSONAL.xlsb loaded, and it worked fine. Then I loaded PERSONAL.xlsb and got the same behavior you did: an extra Excel shell became visible after Application.Visible = True.

I'm not sure why you sometimes get two extra shells, but maybe you have another addin (.xlam) loaded? You could try adding some code to unload all addins first, but I have an alternative solution: why not just launch a new instance of Excel, load your macro workbook in it and run the macro? For example, if your workbook is called C:\Book1.xlsb and the macro in it is "MyMacro" then create a second workbook with code that will launch Book1. Like this:

Sub LaunchIt()

    Dim xlApp As Excel.Application
    Dim wb As Workbook

    Set xlApp = New Excel.Application

    With xlApp
        Set wb = .Workbooks.Open("C:\Book1.xlsb")
        .Run "'" & wb.Name & "'!MyMacro"
        wb.Close SaveChanges:=False
        .Quit
    End With

End Sub

The new instance of Excel is not visible by default, so no need to set visibility. I tested it and it worked for me.

Answer 2

I can reproduce this behavior also, but setting the Visible-property of the window to false will solve it.

Private Sub Workbook_Open()
Dim x As Workbook
With Application
    .Visible = False
    .Wait (Now + TimeValue("0:00:05"))
    .Visible = True
        For Each x In .Workbooks
            If x.Name = "PERSONAL.XLSB" Then 'maybe also other addins(?)
                x.Windows(1).Visible = True
                x.Windows(1).Visible = False 'toggle
                x.Saved = True 'for not getting save-alerts
            End If
        Next
End With
End Sub

Edit:

or changing the for each loop to:

    For Each myAdd In AddIns
        myAdd.Installed = False
    Next myAdd

works on my side (But: on closing you've to set your previous installed addins back to True)

Answer 3

If I have understood you correctly, you wish to go invisible to the user during a particular operation then reappear once that operation has completed?

If so, then take a look at the code I used to make my application actually start with Excel invisible with only a particular form on show:

When the user has finished and wishes to quit the application, he presses cmdQuit command button. The code behind this would make Excel re-appear: Code pasted below. I hope this helps to inspire you to find the solution for your situation.

Notes:
1. Ideally we would like to make the entire excel invisible! So, if there is only one instance of Excel open on the machine, then use Application.visible = true. However, it may be that other spreadsheets are open. If this is the case, then only hide the current workbook. Hence ThisWorkbook.Windows(1).Visible = False is used after in the Else block.

2. Once Excel has been made invisible, we load up our forms. These are windows which provide a front end GUI for interaction with the user. I am thus able to create a proper application from Excel almost without any indication that Excel is running behind the scenes.

3. Once the user has finished, he presses an exit button which I have called cmdExit. You can see the code bend this button below. It unloads the GUI forms and makes Excel visible again for the user. This is similar to what you want to achieve.

Private Sub Workbook_Open()

    Stop

    ' Hide Excel...
'    ActiveWorkbook.Windows(1).Visible = False
    If Workbooks.Count < 2 Then
        Application.Visible = False
    Else
        ThisWorkbook.Windows(1).Visible = False
    End If

    Load frmMain
    frmMain.Show vbModal

End Sub


Private Sub cmdQuit_Click()

    'ThisWorkbook.Windows(1).Visible = True
    Application.Visible = True
    Unload frmMoreOccupationLines
    Unload Me

End Sub

I think the reason you might be seeing more than one workbook may be because you have inadvertantly left previous instances of Excel still open but hidden (invisible). They have not been unloaded and still remain in memory. Does that make sense? So when you do Application.visible=true, you are seeing all the previous instances which you did not properly quit. So a solution for m you may be to ensure you implement a proper unload and exit strategy in your code.

So I guess you might be looking for a solution like as shown below:

Sub Test()
    If Workbooks.Count < 2 Then
        Application.Visible = False
    Else
        ThisWorkbook.Windows(1).Visible = False
    End If

    Application.Wait (Now + TimeValue("0:00:05"))

    If Workbooks.Count < 2 Then
        Application.Visible = True
    Else
        ThisWorkbook.Windows(1).Visible = True
    End If
End Sub


Private Sub cmdQuit_Click()
    Application.Visible = True
    'Unload Me
    Application.Quit
End Sub