Import Excel sheet to Access

[Krayna]

Hi,

I am attempting to create a convertor tool in Access which will take values in a given spreadsheet, perform the conversion calculation and then export back to excel.
So far I have managed to perform the calculation effectively as well as export in a limited way. Basically I am just looking to brush up my tool so it can be used independently by other users.

I would like to be able to import excel files of variable structures and immediately append to table in DB so calculation can work.
To explain in more detail. Function should:
1. Select excel file (possibly preview to user), - see below how a sheet MAY look however it is quite erratic. User should be able to select which sheet in file required.

2. User selects columns required for each column in DB table (e.g. FirstName = column 2 in spreadsheet...) - see table structure below.

3. Data is appended to table (possibly preview to user).

I think that's it for now. Thank you

 

[Krayna]

Sorry actually used different spreadsheet for import - only had necessary data in columns A, B and C - effectively manipulating in Excel before import.

 

[Micron]

Well, all my comments were based on your original requirements. What you have now is far, far simpler than what you originally asked for.

 

[Krayna]

It is not ideal but better than no solution at all...
Would definitely prefer more automation but would take much longer to code all that...

 

[xenou]

So based on what you said and your current code, if you are just importing the first three columns you could probably just use transferspreadsheet with the option to ignore column headers. Then you would not have that row by row and cell by cell looping which is inefficient. Also you wouldn't need to open the workbook and make it visible. I don't see any reason to make it visible - there isn't anything for the user to do anymore. Not having to go through Excel automation would probably be a plus (its a little added complexity in the code that doesn't add much benefit).