VBA to refresh power query then resume macro once the refresh is done

[jasonfish11]

Hi All,
I've searched the internet and found a few threads on this topic. I've tried some of the concepts and have been having issues. I hope this is a simple fix that I'm just missing.

I'm receiving a Run-time error '1004': Application-defined or object-defined error. But I feel like everything is defined. That said I did grab this coding from another workbook where this function works, so I'm likely to be missing something.

Thanks in advance.

Here is my coding it's failing on the highlighted row...

Sub BulkUpdate()Dim TempWB As Workbook
Dim LoaderWB As Workbook
Dim LoaderName As String
Dim LR As Long
Dim FundInit As String
Dim rCell As Range
Dim rCellRow As String
Dim rRng As Range
Dim FilePath As String
Dim FileName As String
Dim Period As String
Dim Question As Integer
Dim Connection As Variant




ScreenUpdating = False
Set TempWB = ActiveWorkbook
FilePath = Sheets("Settings & Refresh").Range("F24").Value
Period = Format(Excel.Application.WorksheetFunction.EoMonth(Sheets("Settings & Refresh").Range("C43").Value, Sheets("Settings & Refresh").Range("C45").Value), "mmddyyyy")
LoaderName = Sheets("Settings & Refresh").Range("F31").Value
Workbooks.Open (LoaderName)
Set LoaderWB = ActiveWorkbook
LR = Sheets("Fund List").Range("A36000").End(xlUp).row
Set rRng = Sheets("Fund List").Range("A2:A" & LR)




    LoaderWB.Sheets("Fund List").Select
    
      If LR = 1 Then
        Exit Sub
      End If
      
    LoaderWB.Sheets("Fund List").Range("A1:E" & LR).AutoFilter field:=4, Criteria1:="YES"
        For Each rCell In rRng.SpecialCells(xlCellTypeVisible)
            rCellRow = rCell.row
            FundInit = LoaderWB.Sheets("Fund List").Range("B" & rCellRow).Value
            FileName = FilePath & "Tax - Tax022T.R - Excise Tax Return " & FundInit & " " & Period & ".xlsm"
            TempWB.Activate
            Range("C42").Value = FundInit
                For Each Connection In ActiveWorkbook.Connections
[COLOR=#ff0000]                    Connection.OLEDBConnection.BackgroundQuery = False[/COLOR]
                Next Connection
            
                ActiveWorkbook.RefreshAll


                For Each Connection In ActiveWorkbook.Connections
                    Connection.OLEDBConnection.BackgroundQuery = True
                Next Connection
            ActiveWorkbook.SaveAs FileName:=FileName
        Next rCell
        ActiveWorkbook.Close True
        LoaderWB.Sheets("Fund List").ShowAllData
        LoaderWB.Sheets("Fund List").AutoFilter Mode = False
End If
   ScreenUpdating = True
End Sub

 

[John_w]

The Connection.OLEDBConnection object is defined only if the connection type for that connection is OLEDB. Therefore change the loop to:

                For Each Connection In ActiveWorkbook.Connections
                    If Connection.Type = xlConnectionTypeOLEDB Then
                        Connection.OLEDBConnection.BackgroundQuery = False
                    End If
                Next Connection

Or you can do this to set the BackgroundQuery to False for all connections, regardless of connection type:

    Dim ws As Worksheet
    Dim listObj As ListObject
    
    For Each ws In ActiveWorkbook.Worksheets
        For Each listObj In ws.ListObjects
            listObj.QueryTable.BackgroundQuery = False
        Next
    Next

 

[jasonfish11]

Thanks so much John, this seems to be working.