Import Excel into Access database

[qootea]

Hi. I would like to import an excel spreadsheet into access table firstly by allowing the user to press a browse button which will open up a file dialog box, I would also like to have the filepath displayed on a text field. Then when the user clicks the import button, it will import the excel sheet into an exisiting table. The excel sheet will be imported to the same table everytime.

I am able to get the file dialog to show up when the browse button is clicked and also the file path to show up on the text, but not too sure how to do the importing function. Is it possible to read the file path from the text field for the import function?
I'm don't know VBA very well, so any help would be good!

Thanks for you help

 

[ZanyJanie]

From the code associated with your first command button, display the browse dialog box and store the full pathname of the selected file in a variable.

Next, create a second command button to do your Excel spreadsheet import. Below is a sample of the VBA command:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel4, "YourTableName", [varSelectedFile], True

In the above code ...

"YourTableName" represents the database table you want to imported the Excel data into. If the table already exists, the new records will be appended to it. If the table doesn't exist, Access will create it.

[varSelectedFile] is the name of the variable where you stored the full path name of your Excel spreadsheet.

True or False (e.g. at the end of the command) indicated whether or not your the Excel spreadsheet contains column names.

Hopefully this will help. I'm no VB expert either; but I've had a little experience and success in this area.

 

[qootea]

Hi. Thanks for your help ZanyJanie. I tried the code, but access gives me this message:
"The contents of fields in 0 record(s) were deleted and 13 records were lost due to key violations.
* if data was deleted the data you pasted or imported doesn't match the field data types or the fieldsize property in the destination table.
*If records were lost either the records you pasted contain primary key values that already exist in the destination table, or they violated referential integrity rules for the relationship defined between tables. Do you want to proceed anyways?"

If I choose yes, it doesn't actually import the table because the changes I made are not in the access table where the excel sheet should append to. I do have fields in my table that are not in the excel sheet, does this cause a problem?

Thanks

 

[ZanyJanie]

The additional fields could make a difference. Also, Access doesn't like something about the Excel data (e.g. text trying to go into a numeric field, duplicate values in a primary key field, etc.)

Try importing your Excel spreadsheet into a new table and then see if you can figure out what Access is squaking about and correct it. Next, you can create an append query to copy the records to your existing table.