Hi all
I am looking to utilise this code:
Code:
Private Sub Form_AfterUpdate()Dim strFile As String
strFile = "C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx"
If FileExists(strFile) Then
Kill "C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Risk Register summary_createtable", _
"C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx", True
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "Risk Register summary_createtable", _
"C:\Users\x\OneDrive - x\Approvalapp\Risk Register link.xlsx", True
End If
Dim appexcel As Object
Set appexcel = CreateObject("Excel.Application")
appexcel.Workbooks.Open "C:\Users\x\OneDrive - x\Riskregisterquery.xlsx"
appexcel.ActiveWorkbook.refreshall
Set appexcel = Nothing
End Sub
The first part of the code exports a query from access. the second part then opens an excel that has a data connection to that exported excel file from access. I wish to refresh this data connection.
However, it seems to leave excel running in the background causing the file to fail to synch with Onedrive unless I manually close this background excel manually via task manager. I've tried putting a .wait in to see if it needs time to complete but that doesn't seem to work. I've also unticked 'enable background refresh' as I read somewhere this can cause issues. But still, Excel doesn't close and therefore the VBA does not complete correctly as when I try to run the VBA again it states the file is open.
I look forward to hearing your advice.
all the best,