Import Excel sheet to Access

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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.

excel sheet to import.png

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

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

I think that's it for now. Thank you
 

Krayna

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
Sorry actually used different spreadsheet for import - only had necessary data in columns A, B and C - effectively manipulating in Excel before import.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,059
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 2, 2021
Messages
33
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
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).
 

Watch MrExcel Video

Forum statistics

Threads
1,127,822
Messages
5,627,096
Members
416,220
Latest member
Sykotik7S

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top