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!

 

[Pat Hartman]

Welcome Aboard
You can do anything you want once you learn how to use VBA. It takes only a single line of code to import or link to a spreadsheet. There are lots of details that need to be defined before you can get going. Including but not limited to:
1. Is this a one-time import for each workbook or will you have to go back at regular intervals for updates?
2. How automatic do you want the import to be? Will a user push a button or do you want to sechedule something to run overnight and gather new files?
3. How much error checking has to happen for each import? How will errors be handled?
4. Once the data is collected, what are you going to do with it?
5. How many users?
6. How many tables?
7. How many rows in the largest table?
8. Do you need web access?
etc.

 

[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.

 

[Pat Hartman]

I don't know that Access can directly import spreadsheets stored in Sharepoint. It may be able to if you have a hyperlink. If it can't, you'll need a procedure to extract them and save them locally.

If you want the process to run unattended, it will need to run on a server. Check with your IT folks to see if they support this. Most servers don't have Office installed. They will have to OK this.

The import will need to be a two step process. Link to the spreadsheet and then run an append query to copy the data from the linked spreadsheet to the permanent table. The append query will be able to take an argument that is the name of the spreadsheet.

You can make many charts in Access (it uses the same charting engine as Excel - it's just that the wizard is stupid) so you might not even need to export the data.

If you export the data, create a template that has the chart on page one and the data table on page two. Export the data from Access onto page two and overlay the previous values.

 

[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?