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
3 Answers
Answers 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 xlApp.AddIns 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.
Answers 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)
Answers 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 
0 comments:
Post a Comment