Power Query - Data Refresh Error in Macro

[jaihawk8]

I have created a file in PowerQuery that will pull in the Earnings Statement for an Employee. I have the file pulling in the Sales Rep name and the Payroll Date from another file and that part is working just fine. I am now trying to get the file to Refresh the PowerQuery. When I do it manually, it works just fine, but when I try to do it as part of this macro, I get a message saying "This will cancel a pending data refresh. Continue?".

What am I doing wrong?

Sub UpdateFilesInPath()
    Dim folderPath As String
    Dim fileName As String
    Dim originalValue As Variant
    Dim newWB As Workbook


    ' Get the folder path from cell B19
    folderPath = ThisWorkbook.Sheets("Main").Range("B19").Value
    
    'Retrieve the Payroll Date from cell B3 of the original workbook
        originalValue = ThisWorkbook.Sheets("Main").Range("B3").Value

    ' Check if the folder path exists
    If Dir(folderPath, vbDirectory) = "" Then
        MsgBox "Folder path does not exist.", vbExclamation
        Exit Sub
    End If

    ' Disable screen updating to speed up the process
    ' Application.ScreenUpdating = False

    ' Loop through all files in the folder
    fileName = Dir(folderPath & "\*.xls*")
    Do While fileName <> ""
        ' Open the current file
        Workbooks.Open folderPath & fileName, IgnoreReadOnlyRecommended:=True, UpdateLinks:=0
        
        ' Set active workbook
        Set newWB = ActiveWorkbook
        
        'Set the value of cell C2 in opened workbook
        newWB.Sheets("Hidden").Range("C2").Value = originalValue

        ' Update the workbook (replace this line with your update code)
        ActiveWorkbook.RefreshAll

        ' Save and close the current file
        newWB.Save
        newWB.Close

        ' Move to the next file
        fileName = Dir()
    Loop

    ' Re-enable screen updating
    ' Application.ScreenUpdating = True

    MsgBox "Files updated successfully.", vbInformation
End Sub

 

[bobsan42]

At which code line do you get this message?
I assume the queries are refreshed in the background so the code doesn't wait for them to finish.
Disable the queries background refresh, so the code will wait for them before trying to save and close the file.
Check this link for code to automate this.

 

[Kerryx]

Have you checked background refresh
- Go to the data tab, click connections in the connections group, click on ALL the connections and click properties, un-check the box that says "Enable background refresh"

 

[bobsan42]

Go to the data tab, click connections in the connections group, click on ALL the connections and click properties, un-check the box that says "Enable background refresh"

The problem is that the code suggests that it opens all files in a folder and does something ...

fileName = Dir(folderPath & "\*.xls*")
Do While fileName <> ""

... so with many potentially changing files the menu is quite a tedious option