VBA Embedded SQL and Pivot Refresh

[anniemalec]

Hello

I'm using the following VBA script to refresh the 2 embedded queries and also a pivot table in the report I am creating however I'm experiencing the report refreshing the pivot before the SQL refreshes have executed.

The script I'm using is:

Option Explicit
Public Sub refreshSheets()
Dim wbk As Workbook, wks As Worksheet, qry As QueryTable, pvt As PivotTable
Application.Calculation = xlManual
Set wbk = Application.ThisWorkbook
For Each wks In wbk.Worksheets
wks.Visible = xlSheetVisible

wks.Activate
If (wks.Name = "Raw Data") Then
For Each qry In wks.QueryTables
qry.Refresh
Next qry

wks.Visible = xlSheetHidden
End If

Next wks
For Each wks In wbk.Worksheets
wks.Activate
If (wks.Name = "Report") Then
For Each pvt In wks.QueryTables
pvt.RefreshTable
Next pvt
End If
Next wks
Sheets("Report").Select
Application.Calculation = xlAutomatic
MsgBox "Refresh complete", vbInformation, "Wrap & No Answer"
End Sub

Any suggestions would be appreciated.

Cheers.

 

[Ruddles]

I don't know the answer to this one for definite but if you don't get any other suggestions then maybe you could try this.

Before refreshing the pivot table, wait for Excel to finish all its calculations. This may do the job:-

do until xlcalculationstate=xldone
  doevents
loop

No guarantees!

 

[Fazza]

'instead of
qry.Refresh

'use
qry.Refresh backgroundquery:=false

HTH