Hard to do in plain English when dealing with technical stuff - but here goes!
Firstly, some assumptions:
1. Each worksheet of all workbooks has an identical layout (given)
2. Each worksheet has a headings row which is in row 1 only (i.e. not multiple heading rows)
3. You want to do this manually, rather than automate the process
4. You have a database set up to contain the table to be created
Based on the above assumptions, the outline steps are:
1. Import your first worksheet (from any workbook) to create your Access table
2. Import all subsequent worksheets from each workbook in turn to the table create in step 1
The detail for these steps is:
Step 1:
1. In Access, open your database
2. On the Ribbon, go the the
External Data tab,
Import group and select
Excel
3. In the dialog box which opens, a) browse to your first workbook and b) select
Import the source data into a new table in the current database. Press OK
4. On the next dialog box which opens, make sure the radio button
Show Worksheets is selected. Select the first worksheet to be imported.
5. Follow the process by selecting the
Next button until you get to the last dialog which allows you to define the import table name. Enter the table name you want, or leave it at the default setting given. Do NOT check
I would like a wizard ... (you can do this later when all data have been imported - if you want/need to).
6. Press
Finish. Close the next dialog box, but do NOT save the import steps (because each subsequent import will be different)
Step 2.
1. On the Ribbon, go the the
External Data tab,
Import group and select
Excel
2. In the dialog box which opens, a) browse to the source workbook and b) select
Append a copy of the records to the table, then
select the table name created in step 1. Press OK
3. In the first dialog box, select the next worksheet to be imported
3. Follow the process through the series of dialogs, or just press
Finish (note - you must make sure all of the headings match between the table and the worksheet at this stage)
Repeat step 2 for all worksheets in all workbooks.
You may encounter errors along the way, which will probably be data-related. I can't predict what these might be, but if you get stuck with anything, then post details and we'll see if we can help.
Good luck!
