Export Query to Excel 97 using TransferSpreadsheet

[Mo]

I support an Access 97 application that currently uses
Macros to output query results to Excel 97. We've
recently run into a problem where we cannot export more
than 16,384 rows. Excel 97 should accept up to 65,536.
It appears that when using the macro Excel 97 creates the
worksheet as Excel 95 vs 97. I can manually export as
Excel 97 but I need to automate it for our users.

I've tried to replace the macro with VBA code using
TransferSpreadsheet but I get the msg "Operation is not
supported for this type of object".

Here's what I'm using in my code:

DoCmd.OpenQuery "qryname",AcViewNormal,AcEdit
DoCmd.TransferSpreadsheet
acExport,acSpreadsheetTypeExcel9,"qryname","pathname.xls"

I've also tried several variations of True/False
DoCmd.TransferSpreadsheet
acExport,acSpreadsheetTypeExcel9,"qryname","pathname.xls",
True,,False

Using True,,False I get msg "Method or Data Member not
found"

Any suggestions would be greatly appreciated. Thanks.

 

[John Nurick]

Hi Mo,

You don't need to open the query before exporting it. This

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "tempq2", _
"C:\temp\tempq2.xls",True

works fine for me.

 

[Mo]

Thanks for the reply John, I think I've figured it out
now. I was using an SQL passthrough query and when I
copy/pasted your code below and replaced the query name
with my query name I still got the error message, "An
error occurred - Operation not supported for this type of
object". I tried a QBE query of a local Access table and
the code ran fine. Looks like I'll have to consider
dumping my query results into local tables before
exporting to Excel. Thanks for your help.

-----Original Message-----
Hi Mo,

You don't need to open the query before exporting it. This

DoCmd.TransferSpreadsheet acExport, _
acSpreadsheetTypeExcel9, "tempq2", _
"C:\temp\tempq2.xls",True

works fine for me.

I support an Access 97 application that currently uses
Macros to output query results to Excel 97. We've
recently run into a problem where we cannot export more
than 16,384 rows. Excel 97 should accept up to 65,536.
It appears that when using the macro Excel 97 creates the
worksheet as Excel 95 vs 97. I can manually export as
Excel 97 but I need to automate it for our users.

I've tried to replace the macro with VBA code using
TransferSpreadsheet but I get the msg "Operation is not
supported for this type of object".

Here's what I'm using in my code:

DoCmd.OpenQuery "qryname",AcViewNormal,AcEdit
DoCmd.TransferSpreadsheet
acExport,acSpreadsheetTypeExcel9,"qryname","pathname.xls"

I've also tried several variations of True/False
DoCmd.TransferSpreadsheet
acExport,acSpreadsheetTypeExcel9,"qryname","pathname.xls" ,
True,,False

Using True,,False I get msg "Method or Data Member not
found"

Any suggestions would be greatly appreciated. Thanks.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.