Bulk upload multiple Excel files as tables at once

[Stoolen]

I'm new to Access so any help would be appreciated. I have multiple Excel spreadsheets (seperate files)that I want to upload into Access as separate tables. Is there a way I can bulk upload them all at once? If not, if I'm able to make them all 1 file but seperate tabs, is it possible to upload the file into access and easily make each tab its own table?

 

[Micron]

First, xl sheets pretty much NEVER make for good database tables. Sheets are wide (columnar) while tables should be tall (rows/records) where related data is held in other tables.

You can use transferspreadsheet function to copy sheets into Access as tables, but only 1 at a time. To do a bulk copy would require code that loops thru wb sheets. Or you can link sheets as tables (ribbon>external data>new data source...

Either way, it is highly likely that you'd need to move data into properly related tables from the transfers/linked sheets. If you have not learned db normalization, I strongly suggest you do so, otherwise you will continually encounter roadblocks to getting at data.

 

[Stoolen]

First, xl sheets pretty much NEVER make for good database tables. Sheets are wide (columnar) while tables should be tall (rows/records) where related data is held in other tables.

You can use transferspreadsheet function to copy sheets into Access as tables, but only 1 at a time. To do a bulk copy would require code that loops thru wb sheets. Or you can link sheets as tables (ribbon>external data>new data source...

Either way, it is highly likely that you'd need to move data into properly related tables from the transfers/linked sheets. If you have not learned db normalization, I strongly suggest you do so, otherwise you will continually encounter roadblocks to getting at data.

Thank you. would it be better if i just created the tables in Access then copy and paste the data from Excel into the Access tables instead of uploading the excels as tables?

 

[Micron]

I'll let you decide on creating them in Access (except that copying/pasting into Access tables is pretty much a non-starter).
Do yourself a big favour if you have not already learned this:

Normalization Parts I, II, III, IV, and V

What Is Normalization, Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com

and/or

Database Normalization – Holowczak.com Tutorials

holowczak.com

Entity-Relationship Diagramming: Part I, II, III and IV

Entity-Relationship Diagramming: Part I

Thoughts, opinions, samples, tips, and tricks about Microsoft Access

rogersaccessblog.blogspot.com