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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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,830
Office Version
  1. 365
Platform
  1. Windows
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.
 

Mateusr1

New Member
Joined
May 4, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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

Hi Cojac. Iam having the same issue. Can you post your full code if u still have it? I know its been a long time...
I have a vba code that goes to SAP and it get that export.xlsx automatically. But I cant even do what i need to that workbook export.xlsx. Its just open when the macro ends.
Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,122,962
Messages
5,599,065
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top