{"id":67553,"date":"2006-04-12T09:09:00","date_gmt":"2006-04-12T09:09:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/04\/12\/how-can-i-create-a-table-and-fill-the-first-column-with-a-range-of-dates\/"},"modified":"2006-04-12T09:09:00","modified_gmt":"2006-04-12T09:09:00","slug":"how-can-i-create-a-table-and-fill-the-first-column-with-a-range-of-dates","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-create-a-table-and-fill-the-first-column-with-a-range-of-dates\/","title":{"rendered":"How Can I Create a Table and Fill the First Column With a Range of Dates?"},"content":{"rendered":"

\"Hey, \n

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

— DG<\/P>\"Spacer\"\"Hey,\"Script<\/A> \n

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\u2019t find one. (Not that we looked all that<\/I> hard.) However, we did come up with a script that, while maybe not the most elegant solution in the world, will do the trick.<\/P>\n

So why does that make us nervous? Well, every now and then we\u2019ve answered a question like this one only to receive a barrage of email from people saying, \u201cYou 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.\u201d That was a problem, not only because it hurt our feelings (no, not really; Scripting Guys don\u2019t have<\/I> feelings) but because everyone was right: we could <\/I>havedone things better\/faster\/easier. Unfortunately – and for reasons we won\u2019t bother discussing – there wasn\u2019t much we could do with these better\/faster\/easier solutions we received.<\/P>\n

Now, of course, we have the Community-Submitted Scripts Center<\/B><\/A>. As you read today\u2019s column, if you find yourself thinking, \u201cMan, I have a much<\/I> better way to solve this problem,\u201d then by all means submit your solution to the Community Center. Trust us, the Scripting Guys do not<\/I> believe that we have the answers to everyone\u2019s problems.<\/P>\n

Well, at least not to everyone\u2019s scripting<\/I> problems. <\/P>\n

So what kind of solution did we<\/I> come up with? This kind of solution:<\/P>

Const NUMBER_OF_ROWS = 1\nConst NUMBER_OF_COLUMNS = 2<\/p>\n
Set objWord = CreateObject(“Word.Application”)\nobjWord.Visible = True\nSet objDoc = objWord.Documents.Add()<\/p>\n
Set objRange = objDoc.Range()\nobjDoc.Tables.Add objRange, NUMBER_OF_ROWS, NUMBER_OF_COLUMNS\nSet objTable = objDoc.Tables(1)<\/p>\n
objTable.Cell(1, 1).Range.Text = “Date”\nobjTable.Cell(1, 2).Range.Text = “Notes”<\/p>\n
dtmDate = #4\/1\/2006#\ndtmMonth = Month(dtmDate)\ni = 2<\/p>\n
Do While True\n    objTable.Rows.Add()\n    objTable.Cell(i, 1).Range.Text = dtmDate\n    dtmDate = dtmDate + 1\n    If Month(dtmDate) <> dtmMonth Then\n        Exit Do\n    End If\n    i = i +1\nLoop\n<\/PRE>\n
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\u2019t delve into the whys and wherefores of table creation today. But don\u2019t despair: after all, we have an Office Space article<\/B><\/A> that will fill you in on how to create a table in Microsoft Word.<\/P>\n
Without going into any detail, the script starts out by creating a new, visible instance of the Word.Application<\/B> object, then uses the Add<\/B> method to open a blank Word document. We then add a new table (one row, two columns) to this document, putting the heading Date<\/B> in column 1 and the heading Notes<\/B> 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.)<\/P>\n
That brings us to these three lines of code:<\/P>
dtmDate = #4\/1\/2006#\ndtmMonth = Month(dtmDate)\ni = 2\n<\/PRE>\n
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<\/B> function to assign the month (April) of the starting date to a variable named dtmMonth. As you\u2019ll soon see, we\u2019ll use this variable to help determine when we\u2019ve reached the end of the month. <\/P>\n
Oh, yeah: what about<\/I> line 3? In line 3 we assign the value 2 to the counter variable i. We\u2019ll use this variable to keep track of which row number in the table we\u2019re working with. We assign the variable the value 2 because that\u2019s the row where we want to put the first day of the month; remember, we\u2019ve already put headings in the first row of our table.<\/P>\n
And now we run headlong into this block of code:<\/P>
Do While True\n    objTable.Rows.Add()\n    objTable.Cell(i, 1).Range.Text = dtmDate\n    dtmDate = dtmDate + 1\n    If Month(dtmDate) <> dtmMonth Then\n        Exit Do\n    End If\n    i = i +1\nLoop\n<\/PRE>\n
What we\u2019re 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<\/B> 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:<\/P>
objTable.Cell(i, 1).Range.Text = dtmDate\n<\/PRE>\n
That\u2019s 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<\/I> we start off by adding one day to the variable dtmDate; that makes the value of dtmDate equal to April 2, 2006:<\/P>
dtmDate = dtmDate + 1\n<\/PRE>\n
That\u2019s 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\u2019t want any May dates in our table. Instead, if we\u2019ve added rows for each day in April we\u2019re done. How do we handle that situation? Here\u2019s how<\/P>
If Month(dtmDate) <> dtmMonth Then\n    Exit Do\nEnd If\n<\/PRE>\n
Here we\u2019re 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\u2019re 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\u2019re run out of days in April. In that case, we use the Exit Do<\/B> command to exit the Do loop and the script comes to and end.<\/P>\n
Will this really<\/I> 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<\/B><\/A>. By our calculations that would make you one of just 950 million people who\u2019ve figured out how to do something better\/faster\/easier than the Scripting Guys. Select company indeed!<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":595,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[84,49,3,5],"class_list":["post-67553","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-word","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67553","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/595"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=67553"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67553\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=67553"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67553"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67553"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}