Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the new column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Create a Calendar Using Excel’s AutoFill Feature

We don’t actually have any evidence to back this up (not that a little thing like that will stop us, of course) but the Scripting Guys are willing to bet that most of the Excel spreadsheets currently in existence have nothing to do with budgets, bookkeeping, or any of the other things Excel was originally designed for. Based on our experiences here at Microsoft we know that people find all sorts of crazy uses for Excel, simply because they find it easy to work in Excel’s grid format. Granted sometimes Excel isn’t the tool they really should be using, but, hey, who are we to judge, right? After all, at least one of the Scripting Guys has never used anything other than Notepad when writing a script.

One thing people really like about Excel is its ability to generate calendars (or, at least, a running list of days). For example, need a list consisting of each and every day in the year 2005? Well, there’s no reason to type in all 365 days. Instead, just type 1/1/2005 in cell A1 then use Excel’s AutoFill feature to generate the remaining 364 days. You end up with a spreadsheet that looks like this, and the whole thing takes maybe 2 seconds to complete:

Pretty cool, huh? But how does that help script writers? Can we take advantage of the AutoFill feature in our scripts? Can we generate calendars and lists of days and all the other things you can do with AutoFill? Funny you should ask ….

In answer to your questions, here’s a script that lists the days of the year – one day per cell – in column A of a spreadsheet (exactly like the spreadsheet shown in the preceding screenshot). The script enters only one date: it writes the value 1/1/2005 in cell A1. After that it uses the AutoFill method to automatically generate the remaining days of the year.

Here’s the script; we’ll explain how it all works in a moment:

Const xlFillDays = 5

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = #1/1/2005#

Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")

objRange1.AutoFill objRange2, xlFillDays
objRange1.EntireColumn.Autofit

We begin by creating a constant named xlFillDays; we’ll use this to tell the AutoFill method to fill our spreadsheet with dates. Next we do the usual: create an instance of the Excel.Application object, set the Visible property to True (so we can watch the excitement unfold), then create a new workbook and a blank worksheet to play with. Finally, we use this line of code to write the value 1/1/2005 to cell A1:

objExcel.Cells(1, 1).Value = #1/1/2005#

Note that we used pound signs (#) to surround our date value. That’s a VBScript convention that helps ensure the value is treated as a date and not as something else.

So far so good, right? With the initial preparation out of the way we now use these lines of code to create a pair of Range objects:

Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")

Our first range object (objRange1) represents the starting point for our series (cell A1); because we entered 1/1/2005 in this range our calendar is going to start with January 1, 2005. What if we entered 4/15/2006 in cell A1? Well, then – oh, you figured it out yourself, did you? You’re right: our calendar would start with April 15, 2006.

The second range object (objRange2) represents the group of cells we want to fill with dates. Note that this range runs from cell A1 to cell A365; not coincidentally, the year 2005 also runs from day 1 to day 365. If we wanted to create a calendar that covered only the first 100 days of 2005 then our second range would run from A1 to A100. Nothing too tricky about any of that.

After that the AutoFill itself takes just one line of code:

objRange1.AutoFill objRange2, xlFillDays

As you can see we call AutoFill on objRange1, the starting point for the series. We then pass AutoFill two parameters: the range for the series to be generated by AutoFill (objRange2) and the type of series we want to generate (xlFillDays). That’s it; we tack on a final line of code to autosize column A, but that’s just for aesthetic purposes. Like we said, the autofill itself requires just one line of code.

As you might expect there are lots of other interesting things we can do with AutoFill. For example, in this script we define a different constant (xlFillWeekdays) and set the value of that constant to 6. Other than that – and other than using this new constant when we call the AutoFill method – the script is identical to the one we just showed you:

Const xlFillWeekdays = 6

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = #1/1/2005#

Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")

objRange1.AutoFill objRange2, xlFillWeekdays
objRange1.EntireColumn.Autofit

Ah, but the resulting spreadsheet is very different. Take a look at the output: the weekends are missing. That’s because xlFillWeekdays shows only the days Monday through Friday, and purposely excludes the weekends:

And, yes, we’d prefer a calendar that has only weekends and excludes all weekdays ourselves. We’re still working on that.

Here’s another variation. This time we set two values: in cell A1 we enter 1/1/2005 and in cell A2 we enter 1/8/2005 (in other words, dates exactly one week apart). We then set the value of objRange1 to encompass both these cells: cells A1 and A2. Why in the world would we do something like that? Well, Excel is an extremely smart application; when it sees that we have a series of dates a week apart it will continue that pattern for us. In other words, it’ll give us a spreadsheet that looks like this:

Cool, huh? Here’s the script that performs that wizardry:

Const xlFillSeries = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = #1/1/2005#
objExcel.Cells(2, 1).Value = #1/8/2005#

Set objRange1 = objExcel.Range("A1:A2")
Set objRange2 = objExcel.Range("A1:A365")

objRange1.AutoFill objRange2, xlFillSeries
objRange1.EntireColumn.Autofit

Here’s one more, just for the heck of it. You’re not limited to autofilling just dates; among other things you can also autofill plain old numbers. (For more information on the AutoFill method and what you can do with it see the Microsoft Excel VBA Language Reference.) For example, this script uses the constant xlFillSeries (which has a value of 2) to fill a range with a series of numbers. If you take a look at the code where we specify the two range objects, you’ll see that we’ll start with 1 in cell A1 and, ideally, end up with 365 in cell A365. Here’s the code:

Const xlFillSeries = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = 1

Set objRange1 = objExcel.Range("A1")
Set objRange2 = objExcel.Range("A1:A365")

objRange1.AutoFill objRange2, xlFillSeries
objRange1.EntireColumn.Autofit

And a slight variation: this one starts with the value of 5 in cell A1 and the value 10 in cell A2. As you might expect, it will fill objRange2 with values that increment by 5 each time (5, 10, 15, 20, etc.):

Const xlFillSeries = 2

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)

objExcel.Cells(1, 1).Value = 5
objExcel.Cells(2, 1).Value = 10

Set objRange1 = objExcel.Range("A1:A2")
Set objRange2 = objExcel.Range("A1:A365")

objRange1.AutoFill objRange2, xlFillSeries
objRange1.EntireColumn.Autofit

Try it and see what happens.