Append Table from Excel Import

[lauriphi]

Curious to know if anyone had some suggestions on how to create a form that would import an excel spreadsheet (that was in the same format of columns as the main table in the database) and append/attach it to the existing main table.

i have attached an abbreviated version of my database for reference. many thanks-

 

[Jibbadiah]

Look for help on "TransferSpreadsheet"... you will find everything that you need there.

 

[lauriphi]

but with TransferSpreadsheet is there a way to allow the user to specify the table that they want to import to the main table each time the command is run as opposed to specifying it in the code? with TransferSpreadsheet the file name of the spreadsheet to import is a required argument i thought... thank you

 

[ghudson]

You are asking for trouble if you give the user the option as to which table they want to append. Your code in the OnClick event should be coded to append one table and the user should know which button to click to update a specifc table.

 

[lauriphi]

all apologies... but elaborate on this portion of your statement please:

"Your code in the OnClick event should be coded to append one table and the user should know which button to click to update a specifc table."

i would like the user to be able to upload an excel spreadsheet that they have created on their own and then have the ability to append that specific spreadsheet they created to the main table in the access db

thanks

 

[lauriphi]

and as another question relating to this, is there a way for the user to select the range for the data in the spreadsheet to be imported in to the access table? otherwise you get the standard 60,000 or so cells imported.

 

[ghudson]

You are asking for trouble if you allow the user to "create" their own file to be imported into your db.

You [the user] will have to create a named range. Again, you are asling for trouble because you can not control what they are doing with the spreadsheet.

You can set the criteria of you append query [sql] to not allow a field(s) that are null. I suggest that you do that to atleast four fields that you know should not be null for those fields. That will prevent the blank rows from being imported. Access will try to bring in a row that has some type for formatting and Access will try to bring in a row if the user had data then deleted the data.