Export to Excel Overwrite

[Plumes]

Hi All,

I'm exporting a query from my db to "sheet1" in spreadsheet "test1.xls". This works fine except that I need it to overwrite the data in "sheet1" if it exists, what actually happens is it creates a new sheet ie "sheet11". According to other post this code should overwrite?! I need it to this because I have calculated controls on other sheets referencing "sheet1".

Can anyone help?

Function ExportToExcel(Path As String)

Path ="\\10.1.1.244\Folder\Folder\test1.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Qry_Name", Path, False, "Sheet1"

End Function

Thanks for the help in advance.
Matt

 

[Frink]

Hallo,

What about deleting Sheet1 before you TransferSpreadsheet?

- Frink

 

[Plumes]

Frink,

Thanks for responding. I have cells on other sheets referenced to sheet1 so if I delete sheet1 they loose thier reference and don't pick the new sheet1. I have resolved my problem through.

What I have found is that if I run the command when the spreadsheet doesn't exist it'll create one using the names provided. Then if I run it again it'll overwrite existing data in the designated workbook. However if I try to use this command to export data to an existing spreadsheet workbook it won't replace the data in the workbook instead it create a new workbook called "WorkbookName"1. The first case is what I needed to achieve so I haven't had time to look in to why this happens.

Thanks again for you help Frink

Matt

 

[SkipVought]

Matt,

I prefer to PULL data rather than PUSH.

I'd recommend doing the query from Excel and refershing the query in the Workbook_Open event.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884