Vela Ventures : Custom Productivity Solutions

Our objective is to make you more productive.

How do I move all my Excel data to an Access database?



This demonstration is a an Excel file that has table data on three spreadsheets.   The Demo is that of an order for parts containing Name, Number Ordered, and Price per Item.  Each spreadsheet is a different Order Number.  The table will contain Name, Number Ordered, Price per Item and Order Number.  There is a control in the Menu bar "Make Access File".  It has 3 sub items "Check This Sheet", "Check All Sheets" and "Build Table".  Before building the table it is necessary to check that validity of the data.  "Check Data" verifies that the Number Ordered and Price per Item are numbers.  If they were not "Build Table" will fail.  "Build Table" runs "Check All Sheets" first and will stop if "Check All Sheets" fails.  The Table will be in a new Access Database in the same file location as this Excel file.

Recommend that you save (save as) the Excel file to a working folder on your hard drive before using any of the controls.

You will get a macro warning when you open the Excel file because of the existence of the VBA code. It is perfectly safe. Open with the macros or else the application will not work as advertised.

The VBA code is visible for your modification if desired.

Click here to download the Excel file.

If you are interested, I will gladly answer any questions - email John G Clark.

Web Site:   |    Page Master: