Refreshing Macro

[sanantonio]

Hi All,

Me again.

I have a remote dataset linked to the functional workbook that I thought I could remotely refresh using a VBA I found somewhere on the internet (Forgive me not naming the author here I've lost it).

Public Sub refreshXLS()
    Path = "K:\Users\xxx\xxxx\Dataset.xlsx"  'the workbook path you want to refresh

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
        .EnableEvents = False
        .AskToUpdateLinks = False
    End With

    Workbooks.Open Path
    ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
    ActiveWorkbook.Close True

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
        .EnableEvents = True
        .AskToUpdateLinks = True
    End With
End Sub

It works fine for things like refreshing the pivot tables etc. but it isn't refresh all the power queries. I have 4 power query data links inside the file pulling in from a variety of places. If I hit "Refresh All" in the dataset itself they pull in fine so the issue isn't with the links, it's the VBA.

Is it because this code opens, hits refresh, then closes and saves too quickly and the power queries can't pull the data quick enough? Is there a solution? I remember seeing a VBA code once that was essential to make it "Wait", not sure if that's appropriate here or if there's an alternate solution? Maybe this code is all wrong and isn't refreshing all?

 

[JEC]

Disable the background refresh for the power queries, that should do. (search at properties)

Then:

Thisworkbook.RefreshAll

 

[sanantonio]

Hi @JEC

Thanks for coming back to me. At first I was like: "Why would disabling the background refresh work?" but I've done some reading and that makes total sense now as it won't allow other actions to take place whilst the refresh is occurring! Genius!

As for disabling background refreshing I've found the following code

Sub Change_Background_Refresh()
'Description: Enable or disable background refresh on all Power Query connections
'Author: Jon Acampora, Excel Campus
'Source:  https://www.excelcampus.com/vba/enable-background-refresh-on-all-power-query-connections/
   
Dim lCnt As Long

'The following code loops through all connections
'in the active workbook.  Change the property to
'True to Enable, False to Disable background refresh.
   
With ActiveWorkbook
For lCnt = 1 To .Connections.Count
'Excludes PowerPivot and other connections
If .Connections(lCnt).Type = xlConnectionTypeOLEDB Then
.Connections(lCnt).OLEDBConnection.BackgroundQuery = False
End If
Next lCnt
End With
   
End Sub

From a trustworthy source. But that article goes on to say:

Does this mean the code needs to go into the sheet on the workbook of the where the data is refreshing? And not in the code of the macro in my other workbook where the buttons is pressed to refresh?

The same source also mentions a different way to disable background refresh...

Is this a better option than the macro? Does it save with the workbook? rather than the user? As the intended end user will change regularly?

Thanks!