Hey, Scripting Guy! How can I create a table and automatically fill the first column in that table with a range of dates?

— DG

Hey, DG. You know, back in the old days we would have been a bit nervous about answering this question. Why? Well, it seems like Word should have a built-in command for doing something like this; however, we couldn’t find one. (Not that we looked all that hard.) However, we did come up with a script that, while maybe not the most elegant solution in the world, will do the trick.

So why does that make us nervous? Well, every now and then we’ve answered a question like this one only to receive a barrage of email from people saying, “You know, instead of using 100 lines of crazy workaround code you could have done this in just a couple lines by calling the AutoCreateCalendar method.” That was a problem, not only because it hurt our feelings (no, not really; Scripting Guys don’t have feelings) but because everyone was right: we could havedone things better/faster/easier. Unfortunately – and for reasons we won’t bother discussing – there wasn’t much we could do with these better/faster/easier solutions we received.

Now, of course, we have the Community-Submitted Scripts Center. As you read today’s column, if you find yourself thinking, “Man, I have a much better way to solve this problem,” then by all means submit your solution to the Community Center. Trust us, the Scripting Guys do not believe that we have the answers to everyone’s problems.

Well, at least not to everyone’s scripting problems.

So what kind of solution did we come up with? This kind of solution:

Const NUMBER_OF_ROWS = 1
Const NUMBER_OF_COLUMNS = 2

Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objRange = objDoc.Range()
objDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS
Set objTable = objDoc.Tables(1)
objTable.Cell(1, 1).Range.Text = “Date”
objTable.Cell(1, 2).Range.Text = “Notes”
dtmDate = #4/1/2006#
dtmMonth = Month(dtmDate)
i = 2
Do While True
    objTable.Rows.Add()
    objTable.Cell(i, 1).Range.Text = dtmDate
    dtmDate = dtmDate + 1
    If Month(dtmDate) <> dtmMonth Then
        Exit Do
    End If
    i = i +1
Loop

Before we start we should mention that very little of this code has to do with adding dates to a table; the vast majority of it involves creating the table in the first place. We won’t delve into the whys and wherefores of table creation today. But don’t despair: after all, we have an Office Space article that will fill you in on how to create a table in Microsoft Word.

Without going into any detail, the script starts out by creating a new, visible instance of the Word.Application object, then uses the Add method to open a blank Word document. We then add a new table (one row, two columns) to this document, putting the heading Date in column 1 and the heading Notes in column 2. (Why? Because DG needs a table that looks like this: the date in one column and space to type notes in the second column. We should also add that DG needs to do this for a month at a time, e.g., he needs a row in the column for each and every day in the month of April.)

That brings us to these three lines of code:

dtmDate = #4/1/2006#
dtmMonth = Month(dtmDate)
i = 2

In line one we simply assign the first day of the month (4/1/2006) to a variable named dtmDate. We then use the Month function to assign the month (April) of the starting date to a variable named dtmMonth. As you’ll soon see, we’ll use this variable to help determine when we’ve reached the end of the month.

Oh, yeah: what about line 3? In line 3 we assign the value 2 to the counter variable i. We’ll use this variable to keep track of which row number in the table we’re working with. We assign the variable the value 2 because that’s the row where we want to put the first day of the month; remember, we’ve already put headings in the first row of our table.

And now we run headlong into this block of code:

Do While True
    objTable.Rows.Add()
    objTable.Cell(i, 1).Range.Text = dtmDate
    dtmDate = dtmDate + 1
    If Month(dtmDate) <> dtmMonth Then
        Exit Do
    End If
    i = i +1
Loop

What we’re doing here is setting up a Do loop that will run until we reach the end of the month. Inside that loop we call the Add method to add a new row to the table; we then use this line of code to write the value of our starting date to column 1 of this new row:

objTable.Cell(i, 1).Range.Text = dtmDate

That’s pretty easy; now it gets just a tiny bit trickier. Having written April 1, 2006 to the table we now need to write April 2, 2006 to the next row in the table. To do that we start off by adding one day to the variable dtmDate; that makes the value of dtmDate equal to April 2, 2006:

dtmDate = dtmDate + 1

That’s good. But suppose dtmDate was equal to April 30, 2006. In that case, adding a day would make dtmDate equal to May 1, 2006, and we don’t want any May dates in our table. Instead, if we’ve added rows for each day in April we’re done. How do we handle that situation? Here’s how

If Month(dtmDate) <> dtmMonth Then
    Exit Do
End If

Here we’re checking to see if the month of our current date (dtmDate) is still equal to our starting month of April (which, as you recall, we stashed in the variable dtmMonth). If it is, then we’re going to increment the counter variable i by 1, loop around, and add a new row to the table. If the two months are different, however, that means we’re run out of days in April. In that case, we use the Exit Do command to exit the Do loop and the script comes to and end.

Will this really give us a table that includes a row for each day in the month of April? Give it a try and see for yourself. And if you can figure out a better way to accomplish this task, then please send your solution to the Community-Submitted Scripts Center. By our calculations that would make you one of just 950 million people who’ve figured out how to do something better/faster/easier than the Scripting Guys. Select company indeed!