Transfer Spreadsheet coding problem

J

John134

I am using Access 2003 and Excel 97. I have successfully coded a subroutine
to transfer an Access file to a pivot file in an Excel 97 spreadsheet. The
first line of code in this routine is:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Activity
Log", _
"E:\LA Snr Ctr\Activity Log.xls", True

When I try to run this code using an Excel 2003 version it fails at this
line. I have tried changing the spreadshee type to TypeExcel8 and TypeExcel9
but it still fails.

Is it even possible to do this using the Transfer Spreadsheet command with
Excel 2003?

Any suggestions from anyone?

John134
 
J

Jeanette Cunningham

John,
yes it usually works well with A2003 and Excel 2003.
the default spreadsheet type is 9, try the code like this:

Is Activity Log 2 words or one word?

DoCmd.TransferSpreadsheet acExport, , "[Activity Log]", _
"E:\LA Snr Ctr\Activity Log.xls", True

Jeanette Cunningham
 
J

John134

Hi Jeanette,

Thanks for your reply. I tried the blank default argument and that did not
work either. I suspect that I have some other error in my code. I am
currently trying to debug it to see if I can isolate the error. Thanks for
confiming that the Transfer Spreadsheet code should work in Excel 2003.

John134


Jeanette Cunningham said:
John,
yes it usually works well with A2003 and Excel 2003.
the default spreadsheet type is 9, try the code like this:

Is Activity Log 2 words or one word?

DoCmd.TransferSpreadsheet acExport, , "[Activity Log]", _
"E:\LA Snr Ctr\Activity Log.xls", True

Jeanette Cunningham

John134 said:
I am using Access 2003 and Excel 97. I have successfully coded a
subroutine
to transfer an Access file to a pivot file in an Excel 97 spreadsheet.
The
first line of code in this routine is:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel97, "Activity
Log", _
"E:\LA Snr Ctr\Activity Log.xls", True

When I try to run this code using an Excel 2003 version it fails at this
line. I have tried changing the spreadshee type to TypeExcel8 and
TypeExcel9
but it still fails.

Is it even possible to do this using the Transfer Spreadsheet command with
Excel 2003?

Any suggestions from anyone?

John134
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How do I transfer an Excel spreadsheet to an Access table 2
Please read this message if you use docmd.TransferSpreadsheet 5
Transfer Spreadsheet Problem 2
Exporting multiple Excel spreadhsheets using Excel templates 7
DoCmd.TransferSpreadsheet acExport, problem 4
forms!Formname!control/ docmd transfer spreadsheet 11
Importing Excel Spreadsheet Problem 7
Problem outputting rpt to spreadsheet - dates are wrong format 1

Top