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 strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

DoCmdTransferSpreadsheet acimport question.

Status
Not open for further replies.

robojeff

Technical User
Dec 5, 2008
220
US
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
 
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
 
 http://files.engineering.com/getfile.aspx?folder=9a16ceed-d8d8-4d56-b82c-fc53fe05a964&file=created_table.jpg
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top