Syntax for TransferSpreadsheet method

[dhstein]

I'm using intellisense to try to get the syntax for the transferspreadsheet
statement, but I get compile errors. Below are 3 attempts. Any help is
appreciated. Thanks.

DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12,
"qryVendorPhone", ExportFileName) = acExport

DoCmd.TransferSpreadsheet(AcDataTransferType =
acExport,acSpreadsheetTypeExcel12, "qryVendorPhone", ExportFileName)

DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12,
"qryVendorPhone", ExportFileName) = acExport

 

[Cory Dove]

I'm using intellisense to try to get the syntax for the
transferspreadsheet
statement, but I get compile errors. Below are 3 attempts. Any help is
appreciated. Thanks.

DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12,
"qryVendorPhone", ExportFileName) = acExport

DoCmd.TransferSpreadsheet(AcDataTransferType =
acExport,acSpreadsheetTypeExcel12, "qryVendorPhone", ExportFileName)

DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12,
"qryVendorPhone", ExportFileName) = acExport

 

[fredg]

I'm using intellisense to try to get the syntax for the transferspreadsheet
statement, but I get compile errors. Below are 3 attempts. Any help is
appreciated. Thanks.

DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12,
"qryVendorPhone", ExportFileName) = acExport

DoCmd.TransferSpreadsheet(AcDataTransferType =
acExport,acSpreadsheetTypeExcel12, "qryVendorPhone", ExportFileName)

DoCmd.TransferSpreadsheet(acExport, acSpreadsheetTypeExcel12,
"qryVendorPhone", ExportFileName) = acExport

1 The parenthesis are not needed. This is a Method, not a function.
The VBA editor will not compile.

2) Access won't recognize
DoCmd.TransferSpreadsheet(acExport

Note there is no space between the "t" in Spreadsheet and the (
so it is looking for a method named, literally,
TransferSpreadsheet(acExport

So, leave off the parenthesis and make sure there is a space after
Spreadsheet, and that ought to fix it.

Try:
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12,
"qryVendorPhone", ExportFileName

Note there is no = anything at the end.

 

[dhstein]

Fred,

Thanks for your response.

Good News / Bad
Good News is your answer is correct - the statement compiled and in fact it
even executed to create the file.

Bad News is the format of the file is bad - Excel 2007 can't open it so I'm
not sure what that means. Could this parameter be wrong -
acSpreadsheetTypeExcel12

 

[dhstein]

Thanks again Fred. I found the solution:

acSpreadsheetTypeExcel12Xml