Fails: ActiveWorkbook.RefreshAll BackgroundQuery:=False

[Raysoc]

Hi there,

I have a few Pivottables in my workbook that are linked to a DB. I am creating a macro to refresh, break the links and delete the pivot tables.

My code works but wont let the Refresh all finish, I read I need to add backgroundquery:= false but when i tried that I get an error:

ActiveWorkbook.RefreshAll BackgroundQuery:=False

Compile Error: Wrong number of arguments or invalid property assignment.

Any ideas as to what the issue is?

 

[p45cal]

refreshall takes no arguments.
Either
have all the pivotcaches' backgroundquery properties set to False, or
loop through all the workbook's pivotcaches:

For Each pc In ActiveWorkbook.PivotCaches
 pc.BackgroundQuery = False
  pc.Refresh
Next

this will leave all pivotcaches backgroundquery properties as false. You could retain each one's settings with:

For Each pc In ActiveWorkbook.PivotCaches
originalBGStatus = pc.BackgroundQuery
  pc.BackgroundQuery = False
  pc.Refresh
  pc.BackgroundQuery = originalBGStatus
Next

(All untested)

 

[Raysoc]

this code will ensure data is refreshed before proceeding with the next line of code right?

The reason I tried to enable refreshall with the background clause was because my code was not letting the frefresh finish before proceeding.

 

[p45cal]

Yes, I'm sure it will, but I haven't tested it - you can do that.

 

[Raysoc]

Works a treat thanks!