How to automate export from Access 2007 to Excel 2003

I want to automate exporting the results of an Access query to Excel. My environment is:

• Access 2007
• A database in Access 2003 format
• Export should be in Excel 2003 format.

The query includes a Memo column that can contain up to 512 characters.

So far I've tried the following:

• Run the query in Access, then copy/paste the result grid into Excel. This works fine but requires a manual step.

• Run some VBA code that exports the query using DoCmd.TransferSpreadsheet as follows:

  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "MyQuery", "MyFile.xls"

The problem I have is that the VBA code truncates the Memo column to 255 characters.

What's the easiest way to programatically export to Excel without this truncation?

Ideally I want to "push" the data from Access to Excel rather than the other way around, i.e. I have not attempted to use "Import External Data" from within Excel.

Edit

In response to the comments and response so far:

Can you export a table that contains a memo field?

I haven't tried exporting a table, since being able to do so wouldn't help me anyway.

... one of the things that truncates memos, like sorting on it

The query does contain an ORDER BY clause, so results are sorted (and have to be sorted). But it's not sorted on the memo column.

That can be avoided by processing the memo with something like Left(MyMemo, 4096).

The memo column in the query is already processed and truncated to 512 characters using something like "Left(Replace(MemoColumn, "...", "..."), 512)".开发者_Python百科 So using Left(...) doesn't seem to help.

Try automating using the copyfromrecordset function

I've tried using Excel Automation with Range.CopyFromRecordSet. In this case the longer Memo fields are not truncated, but instead are exported with some garbage characters at the end.

Try automating using the copyfromrecordset function. While I don't recall any problems with 255 characters that restriction might still exist. Modules: Transferring Records to Excel with Automation

Once you have the code running then I'd suggest removing the Excel reference by using late binding. Late Binding in Microsoft Access