DoCmdTransferSpreadsheet acimport question.

[robojeff]

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

 

[robojeff]

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

 

[Cretin]

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