My sequence of Macros stops running after one of the macros closes another workbook

[dougmarkham]

Hi everyone,

Hope you're having a great Friday!

I am running a sequence of macros as follows:

Sub RunMacroSequence_Click()
    Call ClearContentsBelowRowTwo 'Macro1
    Call copyDataFromMultipleWorkbooksIntoMaster 'Macro2
    Call ExportOpsManPOReconExport 'Macro3
[B][COLOR=#b22222]    Call CloseWorkbookCCSWithoutSaving 'Macro4[/COLOR][/B]
    Call CloseWorkbookAPSWithoutSaving 'Macro5
    Call CloseWorkbookCognosLMSImporterWithoutSaving 'Macro6
    Call openwbPOReconLive 'Macro 5
End Sub

The sequence is interrupted when Macro 4 runs to close another workbook via...

Sub CloseWorkbookCCSWithoutSaving()
Workbooks("CCS Template2").Close SaveChanges:=False
End Sub

The file it's closing is a *.xltm file---if that makes any difference.

Is it possible to resume the sequence of macros after the VBA in one of the macros closes another workbook?

Also, I am interested to know why this happens: is this happening because the workbook I'm closing becomes active prior to closing, thus stopping the macro sequence from completing?

If you would help me with this, I'd be very grateful

Kind regards,

Doug.

 

[nemmi69]

Is Workbooks "CCS Template2" the one running the code?

 

[dougmarkham]

Is Workbooks "CCS Template2" the one running the code?

Hi Nemmi69,

Thanks for replying.
The VBA code is running from a workbook called ReportImporter (which runs 7 macros in sequence below).

Sub RunMacroSequence_Click()
    [COLOR=#0000cd]Call ClearContentsBelowRowTwo 'Macro1
    Call copyDataFromMultipleWorkbooksIntoMaster 'Macro2
    Call ExportOpsManPOReconExport 'Macro3[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookCCSWithoutSaving 'Macro4[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookAPSWithoutSaving 'Macro5
    Call CloseWorkbookCognosLMSImporterWithoutSaving 'Macro6[/COLOR]
   [COLOR=#006400] Call openwbPOReconLive 'Macro 7[/COLOR]
End Sub

Macros 4 to 6 in ReportImporter close workbooks that ran earlier on i.e., in order to generate reports that ReportImporter process and export to file via Macros 2 to 3 in the above list i.e.,

• Macro 4: closes CCS Template2.xltm
• Macro 5: closes APS Template1.xltm
• Macro 6: closes Cognos LMS importer1.xltm

Then the last macro ('Macro7 in the above list) opens a master workbook which parses and processes many streams of data.

To clarify:
The sequence of macros running within ReportImporter stops running at Macro 4. Macros 4 to 6 close external workbooks that are open within the same excel window as ReportImporter.

I would like to know if it is possible to close other workbooks but continue running the subsequent macros (5 to 7) within ReportImporter?

Kind regards,

Doug.

 

[nemmi69]

Just wondering why you are using "Call" as should not be needed if all the macros are in the same workbook.

 

[Norie]

Doug

Why do you have separate subs to close workbooks?

Shouldn't the code to close workbooks be in the subs that open those workbooks?

 

[dougmarkham]

Doug

Why do you have separate subs to close workbooks?

Shouldn't the code to close workbooks be in the subs that open those workbooks?

Hi Norie,

Thanks for replying also!

I have created a series of workbooks in which one runs then opens another, which then runs etc.. i.e., the first opens and automatically runs a sequence of macros culminating in a macro to open the next workbook.

E.g., workbook 1 opens workbook 2; then, workbook 2 has a Private sub to run a sequence of macros when it is opened.
The last macro in workbook 2 then opens workbook 3 etc.

As I was initially creating these workbooks, I encountered the same problem: if I called a macro to close a workbook, the next macro in the sequence wouldn't run. Therefore, I left these workbooks open.

If possible---for this last workbook (ReportImporter)---I wish to close off all the remaining open workbooks. It would be a nice to have to be able to do this, but my initial priority was to get the excel model running.

Kind regards,

Doug

 

[Norie]

Doug

Is the code to run the sequence of macros in a separate workbook to the others?

When the code in one workbook finishes how is the next workbook opened and the code in it initiated?

 

[dougmarkham]

Just wondering why you are using "Call" as should not be needed if all the macros are in the same workbook.

Hi Nemmi69,

I'm attempting to automate the running of macros in sequence within my workbooks so no button pressing is required. I'm also automating across a series of workbooks that do different data processing steps. Are you suggesting I create one sub in ThisWorkbook to run at workbook opening for all my macros within a workbook including the one to open the next workbook?

Anyhow, I did try combining the VBA code for closing all three workbooks within one sub...

Sub CloseWorkbookCognosLMSImporterWithoutSaving()
[COLOR=#0000cd][B]Workbooks("Cognos LMS Importer1").Close SaveChanges:=False[/B][/COLOR]
Workbooks("APS Template1").Close SaveChanges:=False
Workbooks("CCS Template2").Close SaveChanges:=False
End Sub

The result was an error message with the debug highlighting the first line of code (in blue) which was the only line which ran and successfully closed Cognos LMS Importer1.xltm

So the problem seems to be the same whether I run within one macro or as 3 macros called consecutively.

Kind regards,

Doug.

 

[dougmarkham]

Doug

Is the code to run the sequence of macros in a separate workbook to the others?

When the code in one workbook finishes how is the next workbook opened and the code in it initiated?

Hi Norie,

Each separate workbook (e.g., ReportImporter.xlsm) contains the following vba within the microsoft excel object - ThisWorkbook:

Private Sub Workbook_open
     Call [COLOR=#333333]RunMacroSequence_Click 'Macro1
[/COLOR]End Sub

When the ReportImporter is opened, this private sub runs the below sequence of macros stored within modules in ReportImporter

[COLOR=#333333]Sub RunMacroSequence_Click()
[/COLOR][COLOR=#0000cd]    Call ClearContentsBelowRowTwo 'Macro1
    Call copyDataFromMultipleWorkbooksIntoMaster 'Macro2
    Call ExportOpsManPOReconExport 'Macro3[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookCCSWithoutSaving 'Macro4[/COLOR]
[COLOR=#b22222]    Call CloseWorkbookAPSWithoutSaving 'Macro5
    Call CloseWorkbookCognosLMSImporterWithoutSaving 'Macro6[/COLOR]
    [B][COLOR=#006400]Call openwbPOReconLive 'Macro 7[/COLOR] 
[/B][COLOR=#333333]End Sub[/COLOR]

To clarify, there is no central workbook where my macros are stored: they all run from modules within the active workbook. The close macros aren't stored within the workbook I'm trying to close; rather, they are in ReportImporter.xlsm. That is why I wonder if the close macros within ReportImporter are activating the workbooks they are closing, and closing that workbook without then reactivating the ReportImporter.xlsm workbook?

To answer your last query: the next workbook is opened via the last macro in the sequence e.g., Call openwbPOReconLive 'Macro 7. The next workbook "POReconLive.xlsm" contains the same private sub within the excel object "ThisWorkbook" setting off its own macro sequence.

 

[Norie]

Have you tried using a separate workbook to start the sequence by simply opening the first book in the sequence?