Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Question about TransferSpreadsheet

Status
Not open for further replies.

robojeff

Technical User
Dec 5, 2008
220
US
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
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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...
 
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]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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...
 
Check my modified response just above.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top