Access and Excel 2007 - 65000 rows limitation trouble

[Lucson]

I had an Access 2003 query. When I ran it and exported into Excel, the total
number of rows got truncated to 65000 (a limitation well known with MS Excel
2003). I moved to Excel 2007, the same problem persisted. Last week, I got MS
Access installed but using the same query (previously built in Access 2003)
the problem is not going away.
Any idea on how to export my query output into Excel 2007 without having my
output truncated in Excel 2007?
Do I have to build a new query or replicate the query?

Appreciate any pointers.

 

[Jerry Whittle]

It isn't the query. Rather it's how you are exporting it.

Try TransferSpreadsheet either in code or a macro. Use the Constant of
acSpreadsheetTypeExcel12 or the Value of 9. That should make it an Excel
2007 spreadsheet.

 

[Lucson]

Jerry, thanks for your reply, but I have no clue of what you are refering to.
I do not have a code or let alone a macro.
All I do is to run the query, then go to "External Data" tab on Access, on
the sub-tab "Export" click on Excel.
Could you please be more specific?

 

[Jerry Whittle]

Unfortunately that won't allow you to export all the records. I don't have
A07 here at work, so bear with me. I have to work from memory.

Go to the Create tab and look for Macros. I believe that it's towards the
right.

Create a new macro. The action should be TransferSpreadsheet.

Down lowere there should be dropdowns in the Action Arguments section that
ask for things like Transfer Type (Export); Spreadsheet Type (12 possibly);
Table name (the query will work here too); Field Name (what you want to call
the spreadsheet and its path); Has Field Names (field names at the top of the
columns. Probably leave Range blank.

Save the macro and name it.

Now just run the macro to export the data to Excel. You don't even need to
run the query.

 

[Lucson]

I am trying to build a macro that performs the action "TransferSpreadsheet"
and exports data from a query to a spreadsheet. The command is hidden i.e.
not showing in macro command drop down list in Access 2007. I have set my
Trust Center settings to enable all macros but still, I cannot see the
TransferSpreadsheet command. Any idea