Export to Excel

[paulcraigdainty]

I have the following statement which exports data based on a query to a .xls:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryreportsbydate", "C:\Documents and Settings\pdainty\Desktop\RawQualityData_Weekly.xls "


This works fine it exports the data and names the sheet rawqualitydata.

The problem i'm having is when i come to run the export again Excel tells me the file already exists. What I want it to do is overwrite the data in the rawqualitydata sheet in the same file.

Is this possible?

 

[ecawilkinson]

This code will delete the file if it exists, then export the query, which is functionally almost the same as overwriting the sheet each time:

    Const sFile = "C:\Documents and Settings\pdainty\Desktop\RawQualityData_Weekly.xls"
    If Dir(sFile) <> "" Then
        Kill sFile
    End If
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryreportsbydate", sFile

HTH,
Chris

 

[paulcraigdainty]

Thanks for the response. I missed what now seems a vital piece of information. I have other sheets in the file so if the workbook is deleted the other sheets will be lost. Is it possible to do without deleting the file?

 

[MStef]

Try with OutputTo method.

 

[boblarson]

And here's another version:

Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset

Set objXL=CreateObject("Excel.Application")

Set xlWB = objXL.Workbooks.Open("[color=red]C:\Documents and Settings\pdainty\Desktop\RawQualityData_Weekly.xls[/color]")

Set xlWS = xlWB.Worksheets("[color=red]YourWorksheetNameHere[/color]")

Set rst = CurrentDb.OpenRecordset("[color=red]qryreportsbydate[/color]")

xlWS.Range("[color=red]A1[/color]").CopyFromRecordset rst

xlWB.Save

rst.Close
Set rst=Nothing

replace the red text with your actual names (if they are different).