Read Data from Multiple Excel Workbooks

I have shown you, in my first couple of posts, how to burst data stored in one place to multiple workbooks. In this post I will show you the opposite - how to read data from multiple workbooks and place the data into one file.

Let's take the example of bursting physician call panel data into 99 territory-level workbooks. Let's assume we now have 99 territory-level workbooks populated and saved on our hard drive (under C:\Sales\). Now we would like to do some QC before we send out the files. Specifically, we would like to know if in each workbook the following is true:

1), Each file contains a reasonable number of records (average is ~150). If one territory only shows total of 5 physicians, we know something is wrong.

2), No physician who belongs to territory A should appear in workbook for territory B. In another word, all territory # within each workbook should be identical.

3), To balance the selling opportunity in each territory, the call panel has similar numbers of "RxDx" physicians for all the territories. This should be reflected in our QC.

Further, let's assume in each of the 99 workbooks we have the following formulas in certain cells:

1), In Cell A2: =COUNTA($A$4:$A$10000)

This will give us the total number of physician records.

2), In Cell B2: =($A$2 = COUNTIF($B$4:$B$10000, B4))

This will tell us if all the non-blank cells in Column B, where territory # is stored, are identical, and will return TRUE or FALSE accordingly.

3), In Cell J2: =COUNTIF($J$4:$J$10000, "RxDx")

This will give us the number for "RxDx" physicians.

To add the above formulas to all workbooks, you can either manually add the formulas to the template file, then save the template into 99 workbooks; or you can directly modify existing files by using the following VBA example:

.Range("A2").FormulaR1C1 = "=COUNTA(R4C1:R10000C1)"

Now that we have these three "metrics" in each workbook, we just need to retrieve them across all workbooks and store them in one table. It turns out you don't even need to open an Excel file to read data from it. If the file is stored on your local machine or network drive, you only need to provide the file name and path. For example, we know all our territory-level workbooks are stored under C:\Sales\, and the files names can be accessed through:

"Call Panel 2011Q1 - " & ter_list.Cells(i, 5) & " " & ter_list.Cells(i, 6) & ".xls"

Let's assign the above value (as a string) to a variable called FileName. Therefore, to read data stored in Cell A2 of the tab named ter_list.Cells(i, 6) of each workbook, we can use:

="='C:\Sales\[" & FileName & "]" & ter_list.Cells(i, 6) & "'!A2"

Once again, the workbook with the name FileName does not need to be open. This code can sprawl into the said file and read data from cell A2 of the given tab. Do a loop between 2 and 100, and you will get Cell A2 data from all 99 workbooks.