Close Spreadsheets after macro runs

[CoJac]

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]

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

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]

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]

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?

[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]

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]

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