INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Question about TransferSpreadsheet

Question about TransferSpreadsheet

(OP)
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

RE: Question about TransferSpreadsheet

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question about TransferSpreadsheet

(OP)
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...

RE: Question about TransferSpreadsheet

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question about TransferSpreadsheet

(OP)
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...

RE: Question about TransferSpreadsheet

Check my modified response just above.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Question about TransferSpreadsheet

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close