Export 65k+ file

[Salemgrad]

Does anyone have code to export a query which returns over 65k rows of data?
I keep getting an error message that I cannot copy more that 65k onto the
clipboard. I have tried exporting in all formats but cannot get it to take.

 

[Klatuu]

That is because Excel, prior to 2007, only allows 65K rows in one worksheet.
So your options are to upgrade to 2007 which allows up to 1 million rows or
to create queries that will break the data you want to import into pieces
that are < 65K rows and import them into multiple sheets in the workbook.
You do that by using the Range argument of the TransferSpreadsheet method and
give it a worksheet name.

 

[Salemgrad]

I am running 2007. That is another reason I am confused...

 

[Salemgrad]

I am trying to output to a specific location with a date tag on the end of
the file. It works as long as the query does not return more than 65k rows.
I am at a loss as to why I am getting the error. Is there another option
besides outputto to run in a macro or VBA code that will allow this transfer
of data?

 

[Klatuu]

Are both Excel and Access 2007?

If so, I don't know why.

 

[Salemgrad]

Yes.

I am running 2007. That is another reason I am confused...

 

[Klatuu]

Then I am sorry I can't help. I don't have 2007 here and haven't used it
that much yet.
I would suggest you repost your question and include the version of your
products when posting. That will save you some time and hopefully get you a
knowledgeable answer.

I would suggest a header something like:
Exporting > 65K -Acc to Excel both 2007

Sorry I can't help.

 

[Jeanette Cunningham]

Salemgrad,
the usual thing with OutputTo is that it uses an older version of excel as
its default output.
My understanding is that this is done to maintain compatibility with older
versions of access and office.

If you want the higher rows limit, you need to use Transfer Spreadsheet
instead of OutputTo.


Jeanette Cunningham

 

[Rick Brandt]

Are both Excel and Access 2007?

If so, I don't know why.

OutputTo assumes Excel 97 format no matter what version of Access you are
using. You can use TransferSpreadsheet instead. That lets you specify the
Excel version.

 

[Jaime]

I did export very large files > 65K in prior version 2003 < can some body
help in order to export > 65k I just did the DoCmd.TransferSpreadSheetType
and still exporting upto < 65K.