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

DoCmdTransferSpreadsheet acimport question.

DoCmdTransferSpreadsheet acimport question.

(OP)
Not sure if I am asking this correctly or not but here goes....

There are a lot of examples of DoCmdTransferSpreadsheet acimport out there
but haven't found one that answers my question.

What I would like to do is have the user click a button on a form and then have
this command import a spreadsheet into a table.

After the table is created, I would like to open another form that uses a query that accesses
the table that was just created for populating the fields on the form.

So this is something of what comes first, the chicken or the egg...

So if my command looks something like this:

DoCmd.TransferSpreadsheet acImport, 8, "NewData_tbl", "C\Staging\SalesOrderReport.xlsx", True

Will this command over write an existing table named NewData_tbl?

Otherwise, how would I set up the form and query for a table that does not exist yet,
or do I create a table with the query and form?

Thanks

RE: DoCmdTransferSpreadsheet acimport question.

(OP)
I tried the DoCmdTransferSpreadsheet command and it actually appends the table
instead of replacing the table.

Is there a way to have the DoCmdTransferSpreadsheet command replace the table.

Also, it places the first Row of the excel spreadsheet in the 2nd row of the table as it
places F1, F2, F3, etc. as the field names in Row 1 of the table.

See attached

In order to not append the table every time, do I need to delete the table before subsequent
calls to this command?

IN order to get rid of that first row of the table, do I need to do some command to delete the
first row before I run my query?

thanks

RE: DoCmdTransferSpreadsheet acimport question.

When I run the docmd I do clear the table out prior to running it. Is what I do is run a macro and the first step in the macro clears the table with a query then the 2nd step runs the import command.
To get rid of the first row you can run a query. In mine I defined the fields in my table just like they are in excel so I do not have the F1, F2, etc issue.

Cretin

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