VBA Refresh of Linked Tables

Badrul

New Member
Joined
Sep 25, 2013
Messages
28
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
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
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
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top