Linking Many Excel documents Spreadsheets to Access

[jmichael]

My company has hundreds of excel spreadsheets from separate projects within a folder on our network drive. The format of each spreadsheet is identical. Is there an easy way to create an Access database that holds all of this data? I know it can be done individually, but I was hoping there was an automated way.

Is there a way to easily add new spreadsheets to the database in Access when they arrive? Can this be done using an append query? I am not familiar with its usage so some guidance would be appreciated.

Thank you!

 

[LadyDi]

I would like to do this too and wondered what code you wound up using. All the Excel documents I want to load into Access are going to be stored on SharePoint.
Each spreadsheet only needs imported once.

If it could run overnight, that would be awesome. If it is easier for the user to click a button, that will work too.

There should not be any errors. The spreadsheets being compiled are actually generated by a machine. Every spreadsheet should be identical.

Once the data is collected, we are want to export it to a pareto chart in a master Excel file.

There will only be anywhere from two to five users.

We only need one table.

I don't know how many rows we will need in the Access tables. There will only be a handful of rows on each spreadsheet, but we want to combine all the spreadsheets in the database.

Yes, we will need web access, as the spreadsheets are going to be housed on SharePoint.

Also, if it's possible, we would like to import the spreadsheet name as one of the columns in the table (the unit type and date are in the document title instead of in the actual spreadsheet)

I know the basics of VB, but I don't even know where to start with this one. Any assistance you can provide would be terrific.

 

[LadyDi]

Unfortunately, our IT department will not allow this to run on a server, so it will need to be done by clicking a button.

I know I can create a table that will link to the SharePoint "list". In that table, there is a hyperlink to the spreadsheet. How would I create an append query that will open the hyperlink and append the data from the spreadsheet? Would it be possible to allow the user to select which spreadsheets he want's to append?