Exporting to Excel (w2k)

Topic

New Reply

Hello,

Is it possible to export to a specific worksheet in Excel. Every time I export, I create a new Excel file. I would like to have a workbook that includes calculations on one sheet and the data on the other. Currently, when I export it pastes a new file over the old and I lose all of the calculations.

Thanks.

Reply | Quote

Replies

If you use File/Export…, and select Excel97-2000 as file type, you can specify an existing file name. The table or query will be saved as a worksheet named after the table or query; if that worksheet already existed, it will be overwritten, but other worksheets will be left alone.

Reply | Quote

Additional remark: the equivalent in VBA code of File/Export…, file type Excel, is DoCmd.TransferSpreadsheet.

An alternative is to import the data into Excel using Data/Get External Data/New Database Query. You can refresh data imported this way using Data/Refresh Data.

Reply | Quote

I may not be using the correct terms to explain. I’ll try again.

I would like to have multiple sheets within the same workbook. One of the sheets (within the workbook) will have calculations that will not change. Another sheet will have the data from Access. When the data comes in from Access, I need it to go to a particular sheet.

Reply | Quote

AFAIK, you have two possibilities:
1. Name the sheet with the data from Acces after its source; if you want to export tblData, name the sheet tblData, etc. When you select tblData in the database window, then File/Export…, and specify your workbook as destination, the data will be exported to the worksheet named tblData. The existing version of this worksheet will be overwritten. All other worksheets are unaffected by this. This will only work if you name the worksheet after the table or query that you want to export. If that is unacceptable, you can’t use this method.
2. Don’t export from Access, but import into Excel. Use Data/GetExternal Data/New Database Query for this (in Excel). By default, Excel will keep a link to the original data. This link is not live, it hase to be updated manually. This way, you can give the sheet with the data any name you like.

Reply | Quote

Hans,

Thank you very much. The importing to Excel is working like a charm.

Reply | Quote