Excel isn't refreshing power query when using Access VBA. Below is the VBA function I am using within a macro.

Code:
Function PrepXL()
    Dim xlFile As String
    Dim xlFolder As String
    Dim NewxlFile As String
    Dim xlapp As Excel.Application
    Dim xlbk As Excel.Workbook
    Dim cn As Object
    Dim qry As Object
 
    xlFile = "ValRpt.xlsx"
    xlFolder = CurrentProject.Path & "\"
    xlFile = xlFolder & xlFile
    NewxlFile = InputBox("Validate the File Name", "Validate", Forms!frm_MonthlyReports.cmboMacom)
    If Nz(NewxlFile, "") = "" Then
        Exit Function
    End If
    NewxlFile = xlFolder & NewxlFile & ".xlsx"
   If Dir(xlFile) = "" Then
      MsgBox (" The file " & xlFile & " does not exist! ")
      Exit Function
   End If
   If Dir(NewxlFile) <> "" Then
      Kill (NewxlFile)
   End If
      
   FileCopy xlFile, NewxlFile
   Set xlapp = New Excel.Application
   Set xlbk = xlapp.Workbooks.Open(NewxlFile)
   
   xlbk.RefreshAll
   
   DoEvents
   On Error Resume Next
   For Each cn In xlbk.Connections
      cn.Delete
   Next cn
   For Each qry In xlbk.Queries
      qry.Delete
   Next qry
   DoEvents
   xlbk.Save
   xlbk.Close
   Set xlapp = Nothing
   MsgBox (" The Excel File is Ready! ")
End Function
The code does all steps except the Excel workbook doesn't refresh the data connection from Access table.

My macro has two steps - openquery and runcode PrepXL. I run the macro from a button on a form with two combo boxes - I select the unit name from one box and the month from the second box. The function copies my base Excel file with connections and gives new file name from Input Box, then opens workbook, refreshes all connections (should, but doesn't), deletes all queries and connections, saves, and closes.

I can manually refresh from the ribbon in the base file with no issues. The connection updates the data from the Access table.

The database and base Excel file are in the same folder and the new file saves to the folder as well.

I changed the security settings to the lowest (accept all) in the Trust Center, and turned-off background refresh through query properties in Excel.



I am at a loss as to what prevents Excel from updating the connection from Access VBA.