Transfer Spreadsheet coding problem

  • Thread starter Thread starter John134
  • Start date Start date
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
 
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
 
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...
 
Back
Top