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.