Exporting table to Excel workbook

[ejc00]

Hey everyone,

Any help on the following problem would be excellent because I have a deadline coming up...

PROBLEM: I'm exporting an Access query to an Excel workbook as a worksheet. I'm using the following code:

strDocName = "qry_Power_Positions_Crosstab_ELE"
strFile = "C:\Data\BO Automation\Power Positions\East Power Positions-062802.xls"

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strDocName, strFile, True, ""


I've done this for several queries/workbooks it always works fine. However, a couple of export attempts produce errors.

THE ERROR: After the export I try to open the spreadsheet (manually--the open function is not automated). I get this dialog box:

"The instruction at "0x3004c309" referenced memory at "0xfffffffc". The memory could not be "read" Click OK to terminate Click CANCEL to debug the program"

Both options result in excel not being able to open the document.

Any help would be greatly appreciated.

 

[ejc00]

Someone please help...!

 

[ETID]

Maybe this is an option for you?

You would not need to export the data to excel, (too many steps)...

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properies to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current access data comes in (if you set the refresh on open option)

 

[EvilCabal]

Hi, does the error occurs everytime or only sometimes? Is it always on the same file or not? What version or access and excel are you using?