Refresh Data Connections in VBA then Message Box

[ACross]

Hello,

I have been scouring forums for the last day or so.

Basically I have the following code.

Private Sub Cmd1_Click()

FirstMacro
AllWorkbookPivots
ThirdMacro

End Sub

Sub AllWorkbookPivots()

Dim pt As PivotTable

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets


For Each pt In ws.PivotTables

pt.RefreshTable
Next pt

Next ws

End Sub

Sub FirstMacro()

ActiveWorkbook.RefreshAll

End Sub
Sub ThirdMacro()

MsgBox "Macro finished."

End Sub


However the message box and the pivot refresh occur before all of my ODBC data connections have been refreshed. Is there a way to ensure all have run prior to this?

I am a VB novice, this is some of my first code.

Many Thanks

Andrew

[SamT]

Try this
Private Sub Cmd1_Click() FirstMacro
Do Events()
AllWorkbookPivots
Do Events() ThirdMacro End Sub You might need a DoEvents() just before the "Next pt."

[ACross]

Thanks for the reply - So Like:

Private Sub Cmd1_Click()
FirstMacro
Do Events()
AllWorkbookPivots
Do Events() ThirdMacro

End Sub

Sub AllWorkbookPivots()

Dim pt As PivotTable

Dim ws As Worksheet


For Each ws In ActiveWorkbook.Worksheets


For Each pt In ws.PivotTables

pt.RefreshTable
Next pt

Next ws

End Sub

Sub FirstMacro()

ActiveWorkbook.RefreshAll

End Sub
Sub ThirdMacro()

MsgBox "Macro finished."

End Sub


The Do Events () goes red. Sure i am missing something simple?

[ACross]

Seems I figured it out...

I had to disable background refresh for each ODBC Link. The above worked.

Alternatively... I could have specified.

ActiveWorkbook.Connections("YOUR CONNECTION NAME INCLUDING """).Refresh

Many Thanks

Andrew