Hi,
I want to update data in an Excel sheet with an Access query. I have the code below. The deletion of the sheet works fine. The transfer of the query data not because the Excel file is open. How can I solve this? Thank you in advance.
Kind regards,
Code:
Dim wb As Excel.Workbook
Dim xlApp As Excel.Application
' Delete old data in Excel
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wb = xlApp.Workbooks.Open("C:\Excel1.xlsx", True, False)
wb.Sheets("Query1").Delete
' Export data to Excel
DoCmd.TransferSpreadsheet acExport, 10, _
"Query1", "C:\Excel1.xlsx", True