0

I've got an issue with my macro not properly refreshing the connection and/or pivot tables when running it and saving the workbook.

Been reading about it, tried setting backgroundquery = false (which I didn't manage to make work even though I have Microsoft Scripting Runtime added to the references) and different types of refreshing however the problem still remains. The connection is just a table linked to a sheet in a different workbook.

Here's my code for the refresh, which is followed later by a workbook save (both are part of a larger routine, thus the private subs):

Private Sub RefreshPivotTables()

Dim WkC As PivotTable, WkAC As PivotTable

Set WkC = ThisWorkbook.Worksheets("Contracts").PivotTables("Contracts")
Set WkAC = ThisWorkbook.Worksheets("All Contracts").PivotTables("All Contracts")

ThisWorkbook.Connections("Contracting Expiry Report Master File").Refresh
'Reading about it, I was using both Refresh Table and Cache Refresh together with Update thinking it might help
With WkC
    .RefreshTable
    .PivotCache.Refresh
    .Update
End With
With WkAC
    .RefreshTable
    .PivotCache.Refresh
    .Update
End With

End Sub

Private Sub SaveWorkbookNewMonth()

Dim dt As String

dt = Format(CStr(Now), "mmmm yyyy")
ThisWorkbook.SaveAs Filename:="C:\Users\" & Environ$("username") & "\Desktop\Expiry Report Aviation, Asphalt, BS and International Marine - " & dt & ".xlsm", _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

End Sub

Any ideas on how to ensure the refresh is completed without the code moving forward would be appreciated.

EDIT:

In the end the background query was the solution, didn't realize it wasn't working due to the workbook being protected.

New working code:

Private Sub RefreshConnectionAndPivotTables()

Dim WkC As PivotTable, WkAC As PivotTable

Set WkC = ThisWorkbook.Worksheets("Contracts").PivotTables("Contracts")
Set WkAC = ThisWorkbook.Worksheets("All Contracts").PivotTables("All Contracts")

ThisWorkbook.Protect Password:="pass", Structure:=False, Windows:=False
ThisWorkbook.Connections("Contracting Expiry Report Master File").OLEDBConnection.BackgroundQuery = False
ThisWorkbook.Connections("Contracting Expiry Report Master File").Refresh
ThisWorkbook.Connections("Contracting Expiry Report Master File").OLEDBConnection.BackgroundQuery = True
ThisWorkbook.Protect Password:="pass", Structure:=True, Windows:=True
DoEvents
WkC.RefreshTable
WkAC.RefreshTable

End Sub
Mazura_
  • 5
  • 7

2 Answers2

1

You can use DoEvents (documentation here) after calling the query refresh. That will wait until the refresh is complete before progressing to the next line of VBA code.

Check out this question.

teylyn
  • 34,374
  • 4
  • 53
  • 73
  • Just tried it, added `DoEvents` after the connection refresh and also after each pivot table refresh but to no avail. I also read the other post which you've linked but since they've also said `DoEvents` wasn't working I didn't try it myself until now. – Mazura_ Aug 04 '17 at 06:54
  • Thanks for the reminder on the post, tried some more and finally found out the reason why my background query wasn't working at first. `DoEvents` by itself didn't seem to work however, combined with disabling background query, it seems to do the trick. – Mazura_ Aug 04 '17 at 07:14
0

I suggest you to add a count of data colum in values of your pivot and match the total count records with the pivot count. If it matches then fine or refresh the pivot again.

Shrikant
  • 523
  • 4
  • 15
  • I'm sorry, not exactly sure what you mean. Haven't really worked with pivot tables in VBA so I'm unsure on how to achieve what you've suggested. – Mazura_ Aug 04 '17 at 06:39