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.

Print Reports into PDF Files

Let's do something fun: print all our territory-level call panel worksheets into PDF files. It has become more and more popular among biotech managers to distribute their reports in PDF format to avoid any formatting discrepancies as well as potential data tempering. This is suitable for any reports that do not require user-interaction, such as IC Payout Scorecard, Goal Attainment Scorecard, etc.

Before we can programatically do this, we need to install a free software called "PDFCreator" on the report creator's machine (not necessary on end-users' computers). The download link is here. After proper installation, go to "VBA Editor" in Excel, choose "Tools" -> "References", and select "PDFCreator", click "OK". This allows VBA access to all the properties and methods of PDFCreator library.

The code to print all 99 territory-level workbooks into PDF files is shown below. Note that for demonstration purpose we will only print out the first two pages for each file. You can download the example file here.

Change the "mkdPDFName" and "mkdPDFPath" variables to your specifications. If you would like to print out all pages (not just the first two), remove "From" and "To" parameters in the above "ActiveSheet.PrintOut" line.

Combining what we have done in this post and the past one, we can easily print out Excel worksheets as PDF files, then immediately email them to various recipients via Outlook.

Email Reports to Multiple Recipients using VBA

In this post I will show you how to email reports (as attachments) to various recipients via Outlook using VBA. It is a less commonly-used function of VBA to control other Windows processes beyond Excel, but no less powerful. Once we have the Macro in place, we will be able to:

1),Send territory-level reports to corresponding territories. Each territory will only receive one email with one report attached.

2), Send each email with customized Subject line and body message showing, for example, territory name.

Before I show the code, we need email addresses for all territories as shown in the following snapshot:

Add the following Macro to your workbook. Download the example here.

The code above calls an Outlook object and then create a new mail item. Once that's done, we can change the properties of that item (such as .To, .Subject, .Body) to send an email to a specific address with specific messages. Finally, .Attachments.Add method is used to attach the currently opened workbook with the outgoing email.

Several things to keep in mind:

1), Outlook must be open and running when you execute the Macro.

2), Outlook may give a security warning every time an email is sent. Readjust the security settings in your Outlook to disable that message.

3), To add a line break in the body of the message, use vbCrLf as part of the string.

Finalize Call Panel Report - Add Filtering, Remove Opt-out MD Info, Protect Sheets, and Some More

Let's add some final features to our national level call panel file and call it a day.

One is to add the function for the end-user to filter records by specialty. For example if the user chooses "Spec3", then only those MDs with a specialty of "Spec3" would show up. All others will be hidden. Not the most fancy, but many times it would prove to be useful for people not familiar with using Auto-Filter.

To do this, we create another drop-down list which allows the user to choose one of the six specialties, and add the following code. You can download the workbook here.

In the above code we use "RowHeight" property to display/hide rows. Here we set "RowHeight" to 13 (default row height on my computer) to display the rows; and to 0 to hide them. If necessary, this property can also be read first to determine what is the default row height on the end-user's machine.

Let's move on to another topic: Opt-out physicians - doctors who do not wish to share their information (can be sales data, can be demographic info) with any sales reps. Biotech managers are legally required to remove such information in any reports sent to the sales force. In our example, let's assume that we have a list of 400 physicians who are opt-out doctors. The only information we are allowed to show is their MD#, Region and Territory, Specialty and Name. All other fields need to be blocked. The VBA code is shown below:

We can see from the above code that this 400 Opt-Out physician list is on sheet "DropDownList" and occupies the range "G1:G400". If you do not wish to show this list to the sales force, you can choose to hide the sheet (see below), or clear the list by using .Range().ClearContents, or have the list resided on a different Excel file.

Finally, before we send out the file to everybody, we would like to hide the sheets "Ter List" and "DropDownList" by using:

.Sheets(1).Visible = False

If you also prefer to hide column and row headings ("A", "B", "C", etc.) you can use:

ActiveWindow.DisplayHeadings = False

Some people prefer to protect all sheets in the workbook with a password so that no (unintentional) changes can be made to any contents within the sheets. To do this, use:

.Sheets(1).Protect(Password)

Word of caution. After you protect a sheet, if a Macro needs to write to/change the sheet, an error will be generated. To avoid this, use:

.Sheets(1).UnProtect(Password)

to temporarily unprotect the sheet, do your thing, then protect it again.

Finally, to protect the VBA code itself with a password (after all, any person with some VBA knowledge can easily go through your code and retrieve the password contained in the above Protect and UnProtect methods), go to VBA Editor, right click on the VBA Project, and choose "VBAProject Properties...".

Add Sorting and Filtering Capabilities to Reports - Part II

In the last post all sortings are done on either an Ascending or Descending order. For example, when we choose "Specialty" in the drop-down list, the entire physician record is sorted based on an ascending order of "Spec1 -> Spec2 -> Spec3 -> Spec4 -> Spec5 -> Spec6". No surprise here. However, how do we customize the above sorting order? What if "Spec3" is the most important specialty for our sales force and we want those physicians to always appear at the top when sorted? Let's further assume that we would like to sort "Specialty" from top to bottom in an order of "Spec3 -> Spec2 -> Spec6 -> Spec4 -> Spec1 -> Spec5". Can we do this?

It turns out we can, by using something called "Custom Lists". To see your current "Custom Lists", assuming that you are using Excel 2007-2010, go to Excel Options -> Popular -> Edit Custom Lists. Our task is to programatically add a new "Custom List" so that Excel VBA may use that list to perform a customized sorting.

Here is the code. Download the example here.

The key section in the above code utilizes a method called Application.GetCustomListNum(), which returns the position of a particular custom list within all the custom lists on end-user's machine. The method will return 0 if the custom list has not been found. Then we can use Application.AddCustomList to add our custom list. Once we know for sure that there is such custom list on the user's machine, we can use .Range().Sort OrderCustom=CustomListNum+1. Note that we always need to make sure to add 1 to the CustonListNum. Don't ask me why.

For those people who do not want to leave their custom lists on end-user's machine when the user closes the workbook, you can add the following code to your excel file's "ThisWorkBook" section.

Add Sorting and Filtering Capabilities to Reports - Part I

One feature that always helps the usability of reports is the addition of various sorting (i.e. re-arranging the order of records) and filtering (i.e. show/hide records based on certain criteria) capabilities for the end-user. The easiest way to include such functionality is to add "Auto-Filter" to the header row using the following code:

.Range("A3:L3").AutoFilter

The Range() object above would contain all the headers of the list. Run the code again, you would remove the Auto-Filter.

Problem solved, right? Except things rarely get that easy. In reality people who are not Excel geeks like you and me may not feel comfortable using Auto-Filter and many of its functions. In oder to create a more user-friendly report, I always recommend using drop-down lists to let the user choose which metric to sort or filter. See a snapshot below:

You can see that once again we are using our physician record file "Mikoudai Inc Sales Hierarchy.xls" as the example. The above drop-down list (also called Combo Box) resides in the worksheet "Nat Data", and obtains its listed items from the range "A1:A12" in another worksheet named "DropDownList". The listed items correspond with all the headers we have for the physician record list, such as "MD #", "Region #", "Address", etc. The "Cell Link" property refers to range "B1" in "DropDownList". Further assume that we would like to have an ascending order for all the columns except "Level" (we want to show "RxDx" first) when sorting. Here is the macro:

Don't forget to associate the macro with the drop-down list. Download the Excel file here.

Application.Calculation = xlCalculationManual stops the automatic re-calculation of all the cell formulas in the entire workbook, until it is reset back to Automatic Calculation. This helps improve the speed of the macro execution.

On Error Resume Next prevents the macro execution from being stopped by any (potential) errors generated. The macro will just ignore the error and skip the line that causes the error and goes to the next line. Of course any error may render the entire macro unusable, however at least your user will not be alerted by the sudden appearance of an error message.

.Range().Sort is the main method that does the sorting job. Key1 denotes the first "field" - under most circumstances being the column - to be sorted. If necessary, Key2 (and Key3) may be added to perform up to three sorts. "Order1" decides if the sorting for Key1 is A->Z (Ascending) or Z->A (Descending). "Header" tells the macro whether the header row has been included in the Range() object and this is needed if we don't want the header row to be sorted as well (and we don't).

In the next post I will show you how to do a sorting based on customized order.

Modify the Contents/Formats of Existing Workbooks

Many times we face the need to modify our data (in this case, physician records) in some way AFTER we have already bursted the data into territory-level workbooks. Instead of modifying the national data and re-run data bursting, we can also choose to modify each territory file with physician data already populated.

Assume that we would like to make the following changes to our already-populated 99 workbooks:

1), Make all last names upper-case

2), Insert a new column between Columns A and B, name it "Region #", and fill in the regional # of each physician

3), If the physician level is "RxDx" AND the physician segment is "None", change the segment from "None" to "Low".

Without further delay, here is the code:

Some tricks that are worth mentioning:

1), Application.ScreenUpdating = False stops your computer from refreshing the display during the Macro run. There are two advantages of doing this. One is to increase the speed of Macro execution. Two is to avoid all the flickering on the computer monitor (can be very annoying). I highly recommend adding this line to all your VBA codes which cause repetitive display refreshing.

2), .Range("B:B").Insert method inserts a new column to the left (shift:=xlToRight) or to the right (shift:=xlToLeft) of the existing column B. Note that shift parameter determines the shift direction of the existing column B, not the new column to be inserted. To insert a new row, use .Range("2:2").Insert shift:=xlDown or .Range("2:2").Insert shift:=xlUp.

3), When comparing two text strings, first convert both into uppercase strings. This would eliminate any unintentional errors caused by different upper- or lower-case combinations. Note that VBA will treat two text strings as different as long as there is upper- or lower-case difference, even when all the letters are the same. For example, "rxdx" is different from "RxDx" or "RXDX".

How to Burst Physician Data into 99 Territory-level Workbooks

Following the last post, we now have 99 (empty) territory-level workbooks for our physician call panel lists. The next step is to write a Macro to populate these workbooks. Before we can do this, we need a sheet containing the physician list for the entire nation. We will add a new tab to our now familiar "Mikoudai Inc Sales Hierarchy.xls", and name the tab "Nat Data". Within this tab is our physician data for the entire nation. You can download the file here. It may take a while to download since it contains 15,707 physician records. All physician data are fictional.

With the physician data ready, add the following code into the same "Mikoudai Inc Sales Hierarchy.xls" file that you just downloaded (see link above).

The code is slightly more complicated than what we've seen in the last post, but the underlying thinking is simple: each time we open a territory-level workbook, we loop through the entire national physician list line by line. If the territory # matches (If data_sheet.Cells(m, 2) = ter_list.Cells(i, 5) Then), the single-line physician record is copy-pasted into the corresponding territory-level file. All 15,707 lines of records will be "scanned" in this way before we save the territory-level workbook, close it, and make ready for the next territory file. Such process will repeat 99 times all together. Although this may sound a lot of data processing, on my laptop running Excel 2007 the entire Macro takes less than 5 minutes to run.

Some explanations for certain methods/functions we used in the above code will help the understanding:

.Range(cell).End(xlUp).Row: returns the row number (in Integer) of the last non-blank cell in the same column above the cell defined in "Range()" object. In reality, if you use a cell that is way down the bottom of the sheet as the reference (in our case, we used Cell "A60000" as we knew there was no way we would have close to 60,000 physician records), .Range(cell).End(xlUp).Row returns the row number of the last physician record, period. The advantage of using such code is that if we need to add more physician records later on, we do not have to worry about updating our codes.

Selection.PasteSpecial xlPasteValues: paste values only to the destination cells. Other commonly used PasteSpecial methods include paste formats only (xlPasteFormats), paste formulas only (xlPasteFormulas), and paste all (xlPasteAll), which could also be achieved by simply using ActiveSheet.Paste.

How To Create, Open, Save and Close 99 Files For 99 Territories From One Template File

One common task for Biotech managers is to create a "call panel" workbook for each territory, containing a list of physicians within that territory whom the rep is required to call on during the quarter. As that essentially means that we need 99 workbooks as our final output (one for each territory), it is smart to begin with one template file, having all the rows and columns and formatting that we desire. We call it "Call Panel Template 2011Q1.xls". It's just a template so no real physician data is added at this time. Download the template file here.

Next step for us is to use VBA method "SaveAs" to save the template file into 99 territory-level workbooks. Let's assume that for such territory-level workbooks we would like to have both territory # and name as part of the file name, for example, "Call Panel 2011Q1 - 10101 Seattle.xls".

Open our "Mikoudai Inc Sales Hierarchy.xls" workbook (remember? We use this file as our "Macro" file), and enter the following VBA code:

The above code does several things. First of all it opens the template file - Call Panel Template 2011Q1.xls. It then sets up a loop in which the template is saved as a new workbook with corresponding territory # (ter_list.Cells(i, 5)) as well as territory name (ter_list.Cells(i, 6)). Finally it closes the template after all the workbooks have been created. Please note that for the code to work properly in Excel 2007-2010, "FileFormat" property of "SaveAs" method must be given. See the table below for main file formats in Excel 2007-2010:

51: .xlsx

52: .xlsm

50: .xlsb

56: .xls

Make sure to use both the correct file type and FileFormat number in your own code.

Now that we have created all the workbooks. They are all identical at this point. Next we would like to modify each workbook so that:

1), the name of the single tab in the workbook is changed from "Template" to the corresponding territory name;

2), territory # is added to Cell F1.

The code to do this is the following:

This is just a sample of what you can do to modify each individual workbook. I hope you find the code straightforward and easy to understand. You can download the codes shown in this post here.

Let's Do Some House-Cleaning Part II: More parameters to throw around

We will assume that all the Excel files are saved or created under the folder:

C:\Sales

Now I know that I should be doing a better job organizing my files, but it's very convenient to assume a single folder structure for demonstration purpose. Wherever applicable, you should change the default path shown here to your own specific settings.

Furthermore, remember the sales org file that you could download in the last post - "Mikoudai Inc Sales Hierarchy.xls"? Throughout this blog we will use this file as our Macro file - meaning we write Macros within this file and use such Macros to control other files. Instead of directly writing VBA codes in the output files, this method offers several advantages:

1), No one can peek into your codes as they are not in the output files;

2), We have ready access to the sales org hierarchy (including all the territory #, etc) which is necessary for just about every Macro we are going to write.

3). Scalability. This "Macro file" is not task-specific so change some parameters and we are good to go for our next task.

Let's Do Some House-Cleaning Part I: Some common settings we will use throughout this blog

I want to call my company "Mikoudai Inc" (means "rice bag" in Chinese, nice), which just received its first drug approval from FDA (Hooray!). Now we want to sell the drug, obviously. After spending a fortune on a prestigious consulting firm, we come up with a map of the United States, divided into a nice hierarchy of regions, districts and territories. Every zipcode in this country falls within one single territory, one single district, and one single region.

The details of the sales organization hierarchy are as such:

We have two regions, one West and one East. We assign 10000 to the West and 20000 to the East. These numbers will be referred to as "Region #" throughout this blog.

Each region has six districts. For the West region, they are numbered 10100, 10200, 10300, 10400, 10500 and 10600. For the East region, they are numbered 20700, 20800, 20900, 21000, 21100 and 21200. They will be referred to as "District #".

Each district has at least seven territories, and at most nine. In total we have 99 territories. The "Territory #", as we shall call it, also contains five digits - the first three come from the District # to which the territory belongs, and the last two digits denote the order of the territory within the district. For example, Territory # 10101 is the first territory in District 10100, while 20906 is the sixth territory in District 20900. Except for their geographical proximity, territories are ordered randomly inside each district, just as districts inside each region.

Simple enough, right? You can download an Excel file containing the entire structure of our sales organization here.

You might have noticed that the entire numbering system is rather logical. As you will see, this is very important.