TransferSpreadsheet Issue

  • Thread starter Thread starter Shell
  • Start date Start date
S

Shell

In MS Access 2000 and MS Excel 2000 I am executing the following command

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tbl_x_export_vel_xxxx", "C:\OnProcess\Comcast\Output\export report-" &
Format(Date, "mm-dd-yyyy") & ".xls", False, "XXXX RawData!K1:P64000"

NOTE specifically the tab I am exporting the data to: "XXXX RawData!K1:P64000"
I need to place the data inot columns K thru P as previously I ran a
Transferspreadsheet command to place data in columns A thru I.


On my computer, I get the data transferred properly. After I place the code
onto someone elses computer, also running Access 2000 and Excel 2000, we get
the error message "Too many fields defined" . The data does take 6 columns.

Does anybody know what is happenning here and where I can look to solve this
problem.

Thanks
 
In MS Access 2000 and MS Excel 2000 I am executing the following command

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"tbl_x_export_vel_xxxx", "C:\OnProcess\Comcast\Output\export report-" &
Format(Date, "mm-dd-yyyy") & ".xls", False, "XXXX RawData!K1:P64000"

NOTE specifically the tab I am exporting the data to: "XXXX RawData!K1:P64000"
I need to place the data inot columns K thru P as previously I ran a
Transferspreadsheet command to place data in columns A thru I.

On my computer, I get the data transferred properly. After I place the code
onto someone elses computer, also running Access 2000 and Excel 2000, we get
the error message "Too many fields defined" . The data does take 6 columns.

Does anybody know what is happenning here and where I can look to solve this
problem.

Thanks
Click to expand...

I'm surprised it works on any of the computers.
The following is from Help on the TransferSpreadsheet method.
I've marked the relevant portion with ***.

Range Optional Variant. A string expression that's a valid range of
cells or the name of a range in the spreadsheet. ***This argument
applies only to importing. Leave this argument blank to import the
entire spreadsheet. When you export to a spreadsheet, you must leave
this argument blank. If you enter a range, the export will fail. ***

I would suggest you omit the Range argument, export the data to the
workbook. It will be placed in a sheet named the same as the table
exported, i.e. "tbl_x_export_vel_xxxx".

Then use code within the spreadsheet to transfer the data to the
appropriate columns in the worksheet.
Simplest method is to use Excel's Record Macro.
Select the worksheet, copy and paste the data into the other
worksheet, then delete the no longer needed worksheet.
Save the macro.
Then, simply run the macro each time afterwards to move the data where
wanted.
 
Back
Top