Re: Export Query to Excel 97 using TransferSpread***

From: Mo (anonymous_at_discussions.microsoft.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 09:49:56 -0800

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.TransferSpread*** acExport, _
> acSpreadsheetTypeExcel9, "tempq2", _
> "C:\temp\tempq2.xls",True
>
>works fine for me.
>
>
>On Tue, 23 Mar 2004 07:00:33 -0800, "Mo"
><anonymous@discussions.microsoft.com> wrote:
>
>>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
>>work*** 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
>>TransferSpread*** 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.TransferSpread***
>>acExport,acSpreadsheetTypeExcel9,"qryname","pathname.xls"
>>
>>I've also tried several variations of True/False
>>DoCmd.TransferSpread***
>>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.
>.
>