DoCmd.TransferSpreadsheet: Export from a particular row in a sheet

  • Thread starter Thread starter jayakrishna
  • Start date Start date
J

jayakrishna

Please look into follwowing export statement

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "xyz
query", Me.txt_file_path, False, "Sheet1"


First 5 rows already has some data in "sheet1".
I need to retain those rows those and should not be deleted.

So, I want to export data from row 6.

Please let me if there is any way to do that through VBA

Regards,
Jay
 
Please look into follwowing export statement

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "xyz
query", Me.txt_file_path, False, "Sheet1"

First 5 rows already has some data in "sheet1".
I need to retain those rows those and should not be deleted.

So, I want to export data from row 6.

Please let me if there is any way to do that through VBA

Regards,
Jay
Click to expand...

You have posted this question in an Access newsgroup.
I'm afraid I don't understand your logic.
If you are using Microsoft Access and wish to bring data from a
Spreadsheet to Access, you would use acImport to import data from the
spreadsheet.

If, using Access, you wish to send data from Access to a Spreadsheet,
you would use acExport.

The TransferSpreadsheet method does NOT delete the data in the
spreadsheet when it exports data to Access.
Simply include the range of cells in the Range argument of the
TransferSpreadsheet method, i.e.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Access
table into which you are placing the data", Me.txt_file_path, False,
"Sheet1!A6:D15"

to import into Access the range A6 to D15.

Your original code would export data from an Access query, "xyz
query", to an Excel worksheet of the same name. When exporting data,
do not include a Range argument, as that will cause the export to
fail. See Access help.
 
You have posted this question in an Access newsgroup.
I'm afraid I don't understand your logic.
If you are using Microsoft Access and wish to bring data from a
Spreadsheet to Access, you would use acImport to import data from the
spreadsheet.

If, using Access, you wish to send data from Access to a Spreadsheet,
you would use acExport.

The TransferSpreadsheet method does NOT delete the data in the
spreadsheet when it exports data to Access.
Simply include the range of cells in the Range argument of the
TransferSpreadsheet method, i.e.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Access
table into which you are placing the data", Me.txt_file_path, False,
"Sheet1!A6:D15"

 to import into Access the range A6 to D15.

Your original code would export data from an Access query, "xyz
 query", to an Excel worksheet of the same name. When exporting data,
do not include a Range argument, as that will cause the export to
fail. See Access help.
Click to expand...


Hi Fred,
Thanks for the reply.
Actually my requirement is

I have data in table.
I want to export the data from table to excel sheet.
While exporting, Export should be started from a particulr row say 6th
row in the excel sheet.



Regards,
Jay
 
Hi Fred,
Thanks for the reply.
Actually my requirement is

I have data in table.
I want to export the data from table to excel sheet.
While exporting, Export should be started from a particulr row say 6th
row in the excel sheet.

Regards,
Jay
Click to expand...

If I understand you correctly now, you wish to export data from Access
to Excel and place the data into a worksheet starting at row 6. Is
that correct?

According to VBA help files on the TransferSpreadsheet method, you
cannot specify the range in an Excel workbook into which to place the
exported data.
What I do is export the data to the Excel workbook. It will go into a
worksheet of the same name as the table or query you are exporting.
Then, from within Excel, you can either copy/cut and paste the data
wherever you wish, or use a macro code to do so.
 
Back
Top