VBA Refresh of Linked Tables

[Badrul]

So to solve an earlier issue I was having I've had to create a VBA routine to refresh my linked tables. It seems to work fine, though it is not elegant. It uses send keys to open PowerPivot window, refrehs, close it down, then wait before refreshing all charts/tables.

That last step is peculiar though -- fails the first time, but the error handling runs it again and it works fine.

Anyway thought I'd post my code here in case others find it useful or can improve upon it. I tried to use Application.ScreenUpdating = False to hide the PpowerPivot window but that causes issues.

Sub RefreshLinkedTables()
Dim i As Long


Application.SendKeys "%"
DoEvents
Application.SendKeys "G"
DoEvents
DoEvents
Application.SendKeys "Y7"
DoEvents
For i = 1 To 20000
DoEvents
Next i
Application.SendKeys "%"
DoEvents
Application.SendKeys "FC"
DoEvents
For i = 1 To 100000
DoEvents
Next i
DoEvents
On Error GoTo errHandler
ActiveWorkbook.Connections("PowerPivot Data").Refresh
DoEvents

Exit Sub


errHandler:
ActiveWorkbook.Connections("PowerPivot Data").Refresh



End Sub

 

[BrianMH]

Does this work?

Thisworkbook.RefreshAll

 

[Badrul]

No sadly that doesn't work - I used it on its own and also after the Powerpivot window closes down on my original code but it didn't refresh. Also here is a minor correction to my code (Y8 rather than Y7)

Sub RefreshLinkedTables()
Dim i As Long


Application.SendKeys "%"
DoEvents
Application.SendKeys "G"
DoEvents
DoEvents
Application.SendKeys "Y8"
DoEvents
For i = 1 To 20000
DoEvents
Next i
Application.SendKeys "%"
DoEvents
Application.SendKeys "FC"
DoEvents
For i = 1 To 100000
DoEvents
Next i
DoEvents
On Error GoTo errHandler
ActiveWorkbook.Connections("PowerPivot Data").Refresh
DoEvents

Exit Sub


errHandler:
ActiveWorkbook.Connections("PowerPivot Data").Refresh



End Sub