TransferSpreadsheet - Overwriting existing data

[mike1971]

Hi, I've built a funky command running a query and exporting to excel for daily reporting, the problem is I have graphs linked to a specific worksheet and access won't write over it will continue to creat new sheets.

very gratefully thanking in advance

 

[tledwards]

Have you tried linking the spreadsheet as a table in Access? If the format doesn't work, then link to a separate worksheet in the spreadsheet and link data point to the new worksheet.

 

[mike1971]

Not too sure whatr you mean, I've been using the TrSpreadshehet method as follows. Do you mean linking the xls as a updatable sheet and running the query into the linked sheet?

Function ExportDaily()

DoCmd.SetWarnings True
DoCmd.Hourglass True

On err GoTo err:

Dim stExportFileName As String

stExportFileName = "H:\Mike Temp\Outputfiles\AccessOutputs.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "QDailyExport", stExportFileName, True, "OutputWks"

MsgBox "Export Done!"

exitcommand_Click:
DoCmd.SetWarnings True
DoCmd.Hourglass False
Exit Function

err:
MsgBox err.Description, vbOKOnly
DoCmd.CancelEvent

 

[tledwards]

I ment for you to use the File, Get External Data, Link Tables command to link the spreadsheet to the database. Then use an update query to change data.