Data update Access to Excel

[b82726272]

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,

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

[ranman256]

Open the workbook AFTER the transfer.
If you want it in a specific sheet, put the sheet name AFTER the true param.
DoCmd.TransferSpreadsheet acExport, 10, "Query1", "C:\Excel1.xlsx", True, "Sheetname"

[b82726272]

Hi ranman256,

Thank you for the reaction. How can I open the workbook after the transfer? I get an error when I place the open statement after the transfer.

Kind regards,

[ranman256]

Its already in your code...
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set wb = xlApp.Workbooks.Open("C:\Excel1.xlsx", True, False)