Refresh PowerQueries for ALL excel files in a folder

[WojtekP]

Hello,

I am trying to refresh all connections/queries in each excel in a folder - open the file, refresh connection, save the file, close it and go to next - the code below doesn't seem to quite work.
Could you please help?

Sub LoopAllExcelFilesInFolder()
'To loop through all Excel files in a user specified folder and refresh power query connections

Dim wb As Workbook
Dim myPath As String
Dim MyFile As String
Dim myExtension As String
Dim FldrPicker As FileDialog

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  Application.Calculation = xlCalculationManual

'Retrieve Target Folder Path From User
  Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)

    With FldrPicker
      .Title = "Select A Target Folder"
      .AllowMultiSelect = False
        If .Show <> -1 Then GoTo NextCode
        myPath = .SelectedItems(1) & "\"
    End With

'In Case of Cancel
NextCode:
  myPath = myPath
  If myPath = "" Then GoTo ResetSettings

'Target File Extension (must include wildcard "*")
  myExtension = "*.xls*"

'Target Path with Ending Extention
  MyFile = Dir(myPath & myExtension)

'Loop through each Excel file in folder
  Do While MyFile <> ""
    'Set variable equal to opened workbook
      Set wb = Workbooks.Open(filename:=myPath & MyFile)
    
    'Ensure Workbook has opened before moving on to next line of code
      DoEvents
    
    'Change First Worksheet's Background Fill Blue
      Dim lTest As Long, cn As WorkbookConnection
    On Error Resume Next
    For Each cn In ThisWorkbook.Connections
    lTest = InStr(1, cn.OLEDBConnection.Connection, "Provider=Microsoft.Mashup.OleDb.1", vbTextCompare)
    If Err.Number <> 0 Then
    Err.Clear
    Exit For
    End If
    If lTest > 0 Then cn.Refresh
    Next cn

    
    'Save and Close Workbook
      wb.Close savechanges:=True
      
    'Ensure Workbook has closed before moving on to next line of code
      DoEvents

    'Get next file name
      MyFile = Dir
  Loop

'Message Box when tasks are completed
  MsgBox "Task Complete!"

ResetSettings:
  'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[John_w]

ThisWorkbook refers to the workbook in which the running macro resides, however you want to refer to the opened workbook, therefore change
For Each cn In ThisWorkbook.Connections to For Each cn In wb.Connections

 

[SBM]

ThisWorkbook refers to the workbook in which the running macro resides, however you want to refer to the opened workbook, therefore change
For Each cn In ThisWorkbook.Connections to For Each cn In wb.Connections

Hi John_w,

I know that this thread is an old one, but I would like to perform a similar action and corrected the code as explained above.

The main idea of my process is as follows:
- budget owners should complete/review their budgets through excel templates. They have to fill tables manually.
- once done, we save those files in our directory and consolidate them using powerquery (kind of big append of all files)
- In order to allow them to do correct estimations, I would like to add the last available actual data. To do so, their templates include queries enabling us to retrieve the last actual data stored on a location with restricted access. Therefore, my idea was to use that macro to open every single template; refresh, save and close it before sending them back to the budgetowners.

Nevertheless, this macro seems to be the one I need.
The only problem is that the macro only work in debug mode. Out of the debug mode, the files are opened and saved but not refreshed.
Can it be that the queries need more time to complete? I already tried to add DoEvent and Application.wait for 20 seconds (more than real time needed on normal manual refresh using Data refresh all).
Any idea on the cause?

thanks

 

[RoryA]

Try adding:

application.calculateuntilasyncqueriesdone

before the wb.close line.

 

[SBM]

Try adding:

application.calculateuntilasyncqueriesdone

before the wb.close line.

Hi RoryA, it works fine! I tested it on a small amount of files, hope it will not crash on a bigger amount of files ... something to be tested later on.
Anyway, thanks for the quick answer!

 

[SBM]

Try adding:

application.calculateuntilasyncqueriesdone

before the wb.close line.

Hi again,
As explained a few weeks ago, the code works fine on my local drive. Unfortunately, the folder from which I have to loop through all files is now on a sharepoint folder. I am able to select the folder and retrieve the correct path.
For your information:
Original sharepoint link: https://CompanyX.sharepoint.com/sites/Controlling/Shared Documents/Year
Corrected sharepoint link: https://CompanyX.sharepoint.com/sites/Controlling/Shared Documents/Year --> replaced %20 with spaces

But I have an error in following line:

MyFile = Dir(myPath & myExtension)

Error is: Runtime error 52 - Bad file name or number. I set a msgbox to check the outcome before the error and the path is correct ending with *.xls*
Msgbox: https://CompanyX.sharepoint.com/sites/Controlling/Shared Documents/Year*.xls*
--> so, according to my understanding of the code, it should be able to show me all excel files in this folder.

Any idea how to solve this? The sharepoint site has restricted access but only autorized employees (including myself) should use the code.

Thanks in advance.

 

[SBM]

Hi again,
As explained a few weeks ago, the code works fine on my local drive. Unfortunately, the folder from which I have to loop through all files is now on a sharepoint folder. I am able to select the folder and retrieve the correct path.
For your information:
Original sharepoint link: https://CompanyX.sharepoint.com/sites/Controlling/Shared Documents/Year
Corrected sharepoint link: https://CompanyX.sharepoint.com/sites/Controlling/Shared Documents/Year --> replaced %20 with spaces

But I have an error in following line:

MyFile = Dir(myPath & myExtension)

Error is: Runtime error 52 - Bad file name or number. I set a msgbox to check the outcome before the error and the path is correct ending with *.xls*
Msgbox: https://CompanyX.sharepoint.com/sites/Controlling/Shared Documents/Year*.xls*
--> so, according to my understanding of the code, it should be able to show me all excel files in this folder.

Any idea how to solve this? The sharepoint site has restricted access but only autorized employees (including myself) should use the code.

Thanks in advance.

Just to correct a typo: Msgbox: https://CompanyX.sharepoint.com/sites/Controlling/Shared Documents/Year/*.xls*