Close Spreadsheets after macro runs

CoJac

New Member
Joined
Feb 14, 2019
Messages
5
I am running a macro that goes into another application (SAP). In SAP it downloads data and exports it to an Excel file. by default when you export a file from SAP it automatically opens up (I don't want to change this default). But the spreadsheets don't open up until after the macro is done running. This results in one or multiple spreadsheet (that are no longer needed) opening up after the macro runs.

Is there a way to have a second macro run automatically after one macro has completed? (But can't be called by the first macro since the spreadsheets don't open until the first macro is completely done running.)

Or is there another options that I am not thinking of?

I have added code, to the BeforeClose option for the workbook, that closes the extra spreadsheets when you close the spreadsheet with the macro. But this approach isn't very convenient. I'm hoping there is a better option.

I have also added the Wait application to see if the spreadsheets would open while the macro was "waiting". But no luck.

Any help is appreciated.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,613
I have done exactly what you are trying to do. Look at this

A call to Ontime releases the current macro completely. The system now monitors the time to run a new macro
Code:
Application.OnTime Now() + TimeValue("00:00:01"), procedure:="IsSAPOpen", schedule:=True
The IsSAPOpen macro can reinvoke the OnTime; in other words, you can test if the file from SAP is open or not. In that test, if the file isn't open yet, you can call the IsSAPOpen macro until it is. Once the SAP file is open, you can call the other macro you wanted to run.

Jeff
 

CoJac

New Member
Joined
Feb 14, 2019
Messages
5
Thank you very much. That worked perfectly. Below is the code that I used starting with the last line of my original sub and including the new CloseSpreadsheets sub.

Application.OnTime Now + TimeValue("00:00:01"), "CloseSpreadsheets"


End Sub


Sub CloseSpreadsheets()


On Error Resume Next


'if the workbook "Spreadsheet1.xlsx" is open this code will close it when the macro is done.
If Not Workbooks("Spreadsheet1.xlsx") Is Nothing Then
Workbooks("Spreadsheet1.xlsx").Activate
Workbooks("Spreadsheet1.xlsx").Close savechanges:=False
End If


End Sub
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,613
If you added a little bit more... This would continue to test if the workbook has been opened. I don't understand why you are closing the SAP workbook right away?

Code:
[COLOR=#333333]If Not Workbooks("Spreadsheet1.xlsx") Is Nothing Then[/COLOR]
[COLOR=#333333]  Workbooks("Spreadsheet1.xlsx").Activate[/COLOR]
[COLOR=#333333]  'Workbooks("Spreadsheet1.xlsx").Close savechanges:=False
[/COLOR]else
[COLOR=#333333]  Application.OnTime Now + TimeValue("00:00:02"), "CloseSpreadsheets"[/COLOR]
[COLOR=#333333]End If[/COLOR]
 

CoJac

New Member
Joined
Feb 14, 2019
Messages
5
For the Macros that I have been writing I have been exporting information from SAP in an excel spreadsheet. I will then take that information and combine it to other spreadsheets, copy it, move it, filter it, etc... Then I do not need the original export from SAP anymore. But since the exported spreadsheets do not open until after the macro has fully ran I have open and close the spreadsheets within the macro. So by the time the macro is done running I don't need the exported spreadsheet anymore. But they still pup up after the macro has run. So now I used your code to call another macro that closes any exported spreadsheets so that the person running the macro doesn't have to go find them and close them all manually.

It is a little funky though because after the macro has run I have 3 spreadsheets that open up one at a time and then instantly start closing one at a time. So it looks a little weird but it does the trick. Thanks again for you help.
 

Forum statistics

Threads
1,085,542
Messages
5,384,327
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top