Question about TransferSpreadsheet

[robojeff]

I have a table that I would like to append to an existing excel spreadsheet and I am using the
TransferSpreadsheet method with the following syntax:

DoCmd.TransferSpreadsheet acExport, 8, "Table-5", "C:\Stuff\MySpreadSheet.xlsx", True

This exports table-5 into C:\Stuff\MySpreadSheet.xlsx but places the data into a new tab named
"Table-5."

I would prefer to have this data appended to an existing tab in this spreadsheet
named "Work".

Can this be modified to export Table-5 into the "Work" tab of MySpreadsheet or do I need to
do this in another way?

Thank you

 

[SkipVought]

HI,

If I were doing it, I'd code this in Excel to run the query to get external data from Access in a tab named Import, for instance, and then copy the resultset and paste/append to the table in your Work tab.

You can set up, one time, the query via Data > Get External Data > From Access...and the drill down to your database. This process uses Microsoft Query and is very similar to the Access process using a graphic method to specify tables, joins, fields etc.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[robojeff]

Thanks for the reply Skip

I am not the owner of the excel spreadsheet and prefer not to modify it except for appending records to it from access.

I am attempting to automate a manual and time consuming process that the spreadsheet's owner currently performs in order
to add these records...

 

[SkipVought]

Seems to me that you already modify it by the Transfer Spreadsheet method. It's your choice.

I was just trying to make it easier for you. This VBA would be pretty simple.

But there is a more complex approch whereby you could code in Access and query via DAO or ADO objects and then can use CopyFromRecordset (assuming you have set a reference to the Excel Object Library) to put the resultset fight into the Work sheet table.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[robojeff]

Thanks Skip.

My choice is not to modify the actual excel spreadsheet form and what I need
to figure out is how to export my access table into the correct excel tabs
whether it is by using the DoCmd.TransferSpreadsheet method or by some other means...

 

[SkipVought]

Check my modified response just above.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[robojeff]

Thanks Skip

I have coded in Access with queries via DAO objects in the past and it was several years ago
so I will have to look at my old databases.

Because I have been away from Access for some time now, I was just checking to see if there was another means
of accomplishing this or if there was any good examples out there to check out.