Am trying to find a way to export from Access to Excel (which is simple), but how do you specify which worksheet within a workbook, you wish the data to go to. i.e I have eight seperate tables I wish to export to excel into one file but each table onto its own sheet.

In summary how do you include the Sheet Name or number within the export path???


Thanks

You can't. The TransferSpreadSheet Method/Action does not support this. To export to a specific sheet/location, you will need to automate Excel with VBA and feed the data to it in a code loop.

OK Many Thanks Pat.

Hi Jon,

Just for info, have found a way to do it (which will save me a load of work).

The TransferSpreadsheet does support it, by specifying the sheet ref in the range box without adding any cell references.

Many Thanks Again

Jack

This is what Help says:

Range --- The range of cells to import or link. Leave this argument blank to import or link the entire spreadsheet. You can type the name of a range in the spreadsheet or specify the range of cells to import or link, such as A1:E25 (note that the A1..E25 syntax no longer works in Microsoft Access 97). If you are importing from or linking to a Microsoft Excel version 5.0, 7.0, or Excel 97 worksheet, you can prefix the range with the name of the worksheet and an exclamation point; for example, Budget!A1:C7.

Note --- When you export to a spreadsheet, you must leave this argument blank. If you enter a range, the export will fail.

[This message has been edited by Pat Hartman (edited 01-14-2002).]

Hi Pat,

Yes, I read help file and tried it on the off chance. When you put in a cell range i.e A1:F19 or something, then yes the export fails, but for some reason with just the Sheet name it works, must be a bug. Upshot is it does the job for me. Thanks


Jack