Exporting to Spreadsheet problem

[WSF]

Access 97

I am able to create a spreadsheet using

DoCmd.OutputTo acTable, "tblPartsUsed4Period", "MicrosoftExcel(*.xls)",
FilePath & FileName, False, ""
where Filepath and FileName are dynamic references created each time the
data is pulled.
This works fine.

I then try to export into that spreadsheet a subset of data as a separate
worksheet, using

DoCmd.TransferSpreadsheet acExport,5, "tblPartsRequired", FilePath &
FileName

I get the error message "3274 - External table isn't the expected format."

The original spreadsheet, when opened, reports as SaveAs "Excel5.0/95".

Ant help gratefully appreciated.

WSF

 

[Guest]

Hi WSF
I think you should be using DoCmd.TransferSpreadsheet command for both
exports
e.g.
DoCmd.TransferSpreadsheet acExport,5, "tblPartsUsed4Period", FilePath &
FileName
which will create a spreadsheet FilePath & FileName with a worksheet
tblPartsUsed4Period
then

DoCmd.TransferSpreadsheet acExport,5, "tblPartsRequired", FilePath &
FileName
which will add a worksheet tblPartsRequired to the spreadsheet created above.
Regards John

 

[WSF]

John,
You are a gentleman and a scholar.
Spot on, thank you.

WSF