Software

Ask something
Question Locked

How does one pull Excel data from 200 workbooks into 1 report?

0 helpful
5 replies

by jolson 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

So how would one go about pulling data from 200 Excel workbooks into one report? Either another Excel book, or a text file or something other than 200 Excel wookbooks.

The workbook is a "form" that was created for 200 students, so the cells I need to pull from are always the same. For Tax purposes the college I work for needs to pull this data into a report. In the past, this was all done by hand in about 3 days. I know there should be a way to do this, but cannot remember how to do it.

5 replies

by sue t

there may be a better way but when I need to do this I link the cells and that way the workbook will automatically update when I open it. This means you will always have to use the same cells and the workbooks will have to always have the same names and you will have to put them in the same location and that way you can delete the current workbook that they are sending you and replace it with the new one. This is assuming that those 200 workbooks each have a different name. I realize this isn't real clear but it may get you going. Hope this helps or someone has a better idea on how to do this with Excel.

by tallman_roy

Hi Jolson,

I have tried this before opening more than 100 files and read the data, calculating and save them in 1 excel file.

The steps are;
1) You must first create a worksheet ("File") with the filenames uniquely in a table format in you excel file ("Master-File"). Example John Scott as Data_John_Scott.xls

2) Your file must be of the same format. Only the filename are different only.

3) Have all the people to submit their files into a central location in a server.

4) Use Excel VBA to lookup the filename in this table in worksheets "File" and open these files one by one.

5) Read the data in this file and write into the "Master-File".

6) Closed the data file and open the next in the list.

7) Repeat the steps until end of the data list in worksheet "File".

for 200 files, it probably takes you not more than 15min (depends on your PC power).
Try it out.
If you want detail, let me know.

Roy

by techrepfollower

This would only work if the Excel data is in a table format.
Create a table in Access which is a link to the spreadsheet template.
Create a macro that appends the data in the Excel linked table to a master table (and then exits Access).
Create a DOS batch file which copies each of the student spreadsheets over the template table and then runs the macro to append the data to the master table.
I'd be happy to send a sample batch file which would accomplish this.

This discussion is closed to new replies.