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.