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.

Create Progress Bar in Your Report - Part 2

In this post I will show you how to create an actual "bar" to measure the progress of Macro running. You can see an example in the last post (Prog. Bar 2).

To add such a bar to our userform, first make sure the Toolbox is visible (if not, use View -> Toolbox). For those of you who have never used the Microsoft ProgressBar Control before, you need to add that control to your Toolbox. To do this, right-click anywhere in the Toolbox, then choose "Additional Controls". Select "Microsoft ProgressBar Control 6.0", and click "OK". A new control will appear in your Toolbox, which can be dragged to your userform.

Once you have done that and resized/repositioned your progress bar, you can use the code below to set its values. You can download the example file in the last post.

As shown above, it is very easy to control the progress bar - by setting the value of the bar at a certain number between 0 and 100; and the control will automatically set the bar progress at that level.

Bar2.mkdBar.Value = percentage

Once again, the label (Bar2.Label1) will also be refreshed with % numbers, but only at every 5% interval. DoEvents allows the rest of the Macro to be executed normally. Finally, when the loop is finished, we hide the bar with Bar2.Hide.

---

Finally, what if we want to avoid using the Progress Bar control (since it is possible this control may not exist on end-user's computer), and use only those controls that come as default in the Toolbox? There is one walk-around, namely to use a label control with dark background whose width increases over time.

You can write something like this, with Label1 positioned to the right of Label2:

Bar2.Label2.Width = (Bar2.Label1.Left - Bar2.Label2.Left) * percentage / 100

Create Progress Bar in Your Report - Part 1

A progress bar - showing the % completion of a Macro run - can be added to a workbook. It serves to remind the end-user that a Macro is running (hence Excel is locked), and give some idea of how much longer he/she has to wait. It is suitable for a Macro that processes a large amount of data and thus takes some time to run. See the screenshots below for two examples of progress bar.

Prog. Bar 1:

Prog. Bar 2

The Prog. Bar 1 only shows the progress in % numbers, while the second one also shows an actual bar together with the % numbers. In this post I will show you how to create the first progress "bar" and manipulate its value with Macro. You can download the example file here. Please note that there are two buttons in the example file. Clicking on the left button will activate the first progress bar. See below:

The actual Macro, when the button is clicked, does one task: to loop through the entire list of physicians and hide those lines whose specialty is not "Spec3". The progress bar effectively shows the % of lines being processed at any given moment.

Let's first work on creating a progress bar. Go to Visual Basic Editor, make sure the VBA project representing our example file is the current VBA Project, and then choose Insert -> UserForm. A blank userform and a Toolbox will appear. If you don't see the Toolbox, choose View -> Toolbox. First we set the ShowModal property of the userform to False. This is very important as it allows the Macro to keep running when the userform shows. Then we drag from the Toolbox two Label controls, and land them on the form side by side. Change the Caption and other font-related properties of the labels to what's in the example file. Rename the form to Bar1.

Here is the code:

To show the progress bar, use Bar1.Show. The Macro will keep running. Each time the loop cycles through, % of progress is calculated, and the form will be refreshed if necessary. DoEvents yields to other processes so the Macro can run normally. See the section circled by red pen. When the loop cycle is done, use Bar1.Hide to hide the form. You can also use Unload Bar1 to unload it from the memory.