Custom Import from Excel

[StuartSmith]

I am looking for some help with the following. I want to have the user select an excel file to import, once selected, the column headings in the excel sheet are displayed with ability to map each column to the correct column in an existing table in the access database. Then clicking ok would import the selected excel sheet into the appropriate columns in the access table.

My thought would be to import the excel sheet using transferspreadsheet and use the tabledef to get all the field names from the excel sheet and the destination table (destination fields in drop down) then run insert statement using the mapping.

Any advice on the best approach would be appreciated.

[weekend00]

1 change you table field name to match the title in Excel sheet; or
2 import into a temporary table using fieldname as Excel sheet then append to the target table by a query.

Coding is the most complicated way and i think it should be the last choice.

[StuartSmith]

Unfortunately, this is a process the a user with not much access experience is going to be doing so having them import then open query designer to append the data is not going to work. Also the format of the excel is not static, fields are in different columns which is why I wanted to have a mapping function.

[StuartSmith]

The current way I do it is to have them copy the excel sheet into a template excel sheet with the proper column names and order then I import that one using transfer spreadsheet. We are looking to eliminate that step with a simple mapping process.

[weekend00]

My thought would be to import the excel sheet using transferspreadsheet and use the tabledef to get all the field names from the excel sheet and the destination table (destination fields in drop down) then run insert statement using the mapping.

You thought is good to go.