VBA Code to Pause Macros

Microsoft Excel 2003 (full product)
April 19, 2010 at 17:01:39
Specs: Windows XP
First, I'm NOT and advanced VBA user... and I greatly appreciate any help; just please understand my grasp of some terms is limited.

I have a macro that runs a series of sub routines to open, update and close workbooks which contain links and pivot tables (set to update on open).

Problem is the macro continues execution without waiting for each workbook to update. It goes something like this:

sub Main_Macro()

run ("Update_Macro")
'this is where workbooks are opened with UpdateLinks=3
'this macro then saves all of the workbooks and closes them

run ("Create_Reports")
'this macro opens some workbooks and prints them to PDF reports

end sub

The problem occurs when the Main Macro executes the line to run Create_Reports but the reports cannot be generated because the workbooks have not completed updating in the Update_Macro routine.

I've tried gimmicks like chaining a bunch of macros with Application.Ontime Now() + xx where I add in a delay before each successive macro is run, but I'm sure there is a more professional and efficient approach.



See More: VBA Code to Pause Macros

Report •

April 19, 2010 at 19:24:51
How about this for a 10 second pause...

Application.Wait(Now + TimeValue("0:00:10"))

Adjust the time as required.

Report •

April 19, 2010 at 20:13:33
Thanks for the response, but Application.Wait causes the entire application to stop, and no link updating or calculations occur; when the wait is terminated the original problem persists.

Does anyone know how to make the macro pause execution until the links are updated and calculated?

Report •

April 19, 2010 at 21:57:04
You said 'this macro then saves all of the workbooks and closes them.

Can you check to see how many (or which) workbooks are opened and don't do anything until all of the ones that are supposed to be closed are indeed closed?

 If Application.Workbooks.Count > 1 Then GoTo HowMany
 Run ("Create_Reports")

If you use Count, don't forget about your Personal.xls if you have one open/hidden.

Or you could check by open workbook Names and wait until certain ones no longer exist.

You could use a For-Next loop that does nothing but count to some value that you think is large enough to delay the next line from executing.

You could use a MsgBox or SendKeys, both of which require user intervention for the code to continue.

Since we don't know exactly what your update code is doing it's hard to more specific.

P.S. I'm not sure that there is anything wrong with OnTime if it works for you.

Report •

Related Solutions

April 20, 2010 at 00:14:34
If Application.Workbooks.Count > 1 Then GoTo HowMany
Run ("Create_Reports")

I tried your suggestion above, but the loop seems to eat up all the processor time for the Update_Macro routine.

I've seen this issue posted on many forums and yet to see a reply that works... I just can't believe there is no way to ensure all links are updated before a macro continues executing code...

oh well, I do appreciate your efforts. Thanks.

Report •

April 20, 2010 at 05:07:15

As you haven't posted the code you are using it is hard to know exactly what is happening.

I tried the following:
1. In a Master workbook I have a cell (B2) with a link to cell A1 in Update1.xls
1. The workbook named Update1.xls contains a link in cell A1 to the value in cell A1 of another workbook Update2.xls
2. Update1.xls is saved and closed (cell A1 shows the original value (999) of cell A1 in Update2.xls)
3. Update2.xls is opened and the value in cell A1 is changed (lets say from 999 to 911)
4. In my Master workbook I have a command button which will run a macro, which in turn runs a subroutine which opens Update2.xls, then Saves and Closes it.
5. By default Update1.xls updates its own linked cell from Update2.xls when it is opened.
6. The Update subroutine terminates after closing Update1.xls and returns control to the main macro.
7. The main macro now reads the value in cell B2 (this was the cell linked to Update1.xls)
8. The main macro creates my 'report' - in this example a message box, displaying the contents of cell B2
9. After clicking my command button, the main macro runs, opens, saves and closes Update1.xls and displays the value in B2
10. The value displayed was 911, i.e., Update1.xls had successfully opened, updated its link and closed, and the Master workbook's linked value had been updated, all before the main macro moved on to create the 'report'.

The above sequence appears to cover what you want to do.

Here is my macro, called from a command button:

Private Sub CommandButton1_Click()
Call Update
MsgBox Worksheets("Sheet1").Range("B2").Text
End Sub

Public Sub Update()
Workbooks.Open ("C:\temp\B_Files\Update1.xls")
End Sub


Report •

Ask Question