Why no 'Export' functionality?

[Jay]

Can anyone explain why some of my queries don't allow me to 'Export' - with
the actual 'Export' item on the File menu greyed out.

This is proving very problematic as I also can't 'Analyse with Excel' via
the 'Tools' menu as I receive the following prompt: 'There are too many rows
to output, based on the limitation specified by the output format or by
Microsoft Access'.

My query has 16,000 rows.

Any help greatlt appreciated.....Jason

 

[Guest]

Older versions of Excel can only handle a little over 16K records in a
worksheet. The default export format for Analyze with Excel is this older
format.

If you upgrage to Office Pro 2007, Excel can handle a million rows. But that
info doesn't help you today.

In a macro you can TransferSpreadsheet and select Microsoft Excel 8-10 as
the Spreadsheet Type. Then you can export up to 64K records into Excel.

In code it would look something like:

DoCmd.OutputTo acQuery, "qry_All", "MicrosoftExcelBiff9(*.xls)",
"N:\All.xls", True

Watch out for word wrapping.

 

[Jay]

Hi Jerry,

Thankls for explaining it. I'm a bit of a VBA novice. Can you help me out
with how to get the TransferSpreadsheet macro working?

Many thanks......Regards, Jason

 

[Guest]

Sure. At the database window go to Macros.

Click the New icon in the database window.

In the first row of the Action column select TransferSpreadsheet from the
dropdown.

Down below some change the Transfer Type to Export.

The Spreadsheet Type to Microsoft Excel 8-10.

Table Name to the name of the table or query.

The File Name to what you want the spreasheet to be named AND its location.
In other words, fully path it out.

Has Field Names to Yes.

Save the macro with something like macExportSpreadsheet.

Now you can just double click on this macro to make it run. You could also
assign it to a button on a form. Putting a macro on the standard Access
Switchboard is a little more difficult.

 

[Jay]

Many thanks Jerry, I appreciate your help.

Rgds....Jason

 

[Jay]

Many thanks for your time Jerry, I've got it working fine now.
Regards....Jason