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.

Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 4

In this last post of the series, I will show you how to run Macro automatically when the end-user opens our workbook, and when he/she closes it.

Why do we need to run events when the workbook is being opened? In the current example, we want to make sure that all the main elements of the worksheet - i.e. an embedded chart, a "Drill-Down" button, and a shape serving as the chart title - are properly positioned relative to each other on the end-user's computer.

Why do we need to run events when the workbook is being closed? In the current example, we want to make sure that if a drill-down menu still exists and shows in the environment when the user clicks the "x" button, the menu is deleted and removed from the system.

Here is the code. DO NOT put the code in Modules as we normally do. It should be put in the "ThisWorkBook" section under "Microsoft Excel Objects"."Workbook_Open()" event fires up when the workbook is opened AND the Macro is enabled. "Workbook_BeforeClose()" event fires up when the user clicks on the "x" button (the "Close" button).

As we see, we can position shapes (Left, Top) relative to given cells in the worksheet, giving us the ability to do some precise positioning. For example,
.Shapes("Chart1").Left = .Range("B6").left
positions the left edge of the chart to the left edge of the cell B6. Here we are treating the chart as a shape object rather than a chart object as our only concern here is to position the chart, not to manipulate any chart elements.

Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 3

Excel VBA has provided some very useful and powerful features to manipulate data sources as well as the appearance of embedded charts. As a matter of fact almost EVERY single aspect of a chart can be modified by Macros. The example I show here should give you a taste of such functionalities.

First things first, we need a name for the chart we intend to control so that we may effectively reference the chart in our codes. There are two ways to locate and change the name of an embedded chart. Click on the chart, and look in one of the following two places:

Enter a customized name (if you choose) into one of the two textboxes outlined above and press enter. Make sure that each chart has its own unique name.

Now let's take a look at the following codes we used to chart "Monthly Regional Sales" in the given example. Once again, you can download the example file here.

Key grammars of the codes include:

1), Sheets("Dashboard").ChartObjects("Chart1"), is used to reference an embedded chart named "Chart1" in worksheet named "Dashboard". Please note that to reference chart elements such as chart titles, legends and data series, we should use:

Sheets("Dashboard").ChartObjects("Chart1").Chart.ChartTitle

Sheets("Dashboard").ChartObjects("Chart1").Chart.Legend

Sheets("Dashboard").ChartObjects("Chart1").Chart.SeriesCollection

2), Before we redraw the chart we need to delete the current content in the chart - i.e. all data series (circled by red pen) and (potentially) trendlines (circled by blue pen). Now that if there is currently no trendline in the chart, the attempt to delete it would trigger an error message. Use "On Error Resume Next" to ignore all such errors.

3), The line to determine the chart type (i.e. if it's a line chart or a column chart) is underlined by black pen: .Chart.ChartType = xlLine. A good source for all chart types can be found here.

4), Codes circled by Magenta pen detail how to add a data series as part of a line chart, as well as how to customize the look of the data series. Most properties are self-explanatory (for example xValue determines the x-Axis data range, .Border.Color determines the line color, etc). One property, .Points(.Points.Count), references to the last (right-most) data point of the data series. To reference the first data point, use .Points(1). To reference all data points, set up a loop between 1 and .Points.Count.

5), Codes detailing how the axes and legend would appear are pretty easy to understand too. One thing I would like to point out is .Chart.Axes(xlValue) refers to the y-Axis while .Chart.Axes(xlCategory) refers to the x-Axis.

Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 2

Continuing from the last post, here is the code that creates a "Pop-up" menu each time the user clicks on the "Drill-Down" button:

The "Drill-Down" button is associated with the Macro show_mkdMenu1(). This Macro does three things:

1), Delete the menu already created (blue underline). This ensures that at any given time no more than one menu exists in the environment. This menu always has the name "mkdMenu1". Note that when the user first opens the workbook there is no menu ever created and hence any attempt to delete a non-existent object will result in an error and break. To avoid this we used "On Error Resume Next" to ignore any such potential errors.

2), Call Macro create_mkdMenu1() to create, but not show, a new menu. See below for the details on how to create a multi-layer menu as I did with the example.

3), Show the menu we just created (red underline).

Now let's dig a little deeper into Macro create_mkdMenu1().

To create a multi-layer menu as shown in the example (there are three items in the first layer labeled with numbers 1, 2 and 3. One more item "MKD1 Monthly District Sales" appears as a sub-menu upon which a second-layer with two more items will open), the key is to add new controls as either:

1), Type:=msoControlButton, which adds a normal menu item. When clicked, another procedure named in "OnAction" property will be executed. As shown in our example, values can be passed to such procedure when called. The "FaceId" property determines what icons will appear in front of the item, i.e. FaceId = 71 will show number "1", etc. There are thousands of FaceIds you can apply to individualize your menu. A good link is here.

2), Type:=msoControlPopup, which adds a sub-menu group. As we can see from our example, there is no "OnAction" or "FaceId" properties associated with this kind of control as it is only used to open a second-layer menu items. To add sub-menu items, repeat the procedures we discussed in step 1) above.

In the next post I will show you how to manipulate charts using VBA.

Spruce Up Your Dashboard Using Charts and Drill-Down Menus - Part 1

In the next 3 posts I will show you a neat way to display your data with charts. By using drill-down menus we can show multiple data sets - all related in some way - in the same chart, with one data set showing at a time. Download the example file here and try it out yourself.

What you can see in "Dashboard" tab is one chart, with a drill-down button as shown below.

Clink on the button, and a drill-down menu will appear.

This drill-down menu provides the user with multiple options, from showing the sales data on the national level to showing same data on the district level. The chart will redraw automatically each time a different option item is clicked. Furthermore, headers in Range C1 as well as the textbox right above the chart will also change accordingly.

I see at least two big advantages to utilize such a drill-down menu:

1), The data is being presented in a way that is highly organized. By designing your own drill-down menus you can group related data sets together, making your data logically structured and easy to follow.

2), By using one chart area for multiple data sets you also save space, and your report will look so much more clean and usable.

I will only show you the key sections of Macro in my posts and you can always refer to the Macro in its entirety in the example file you just downloaded.