Hey, Scripting Guy! How can I copy all the comments from an Excel worksheet to a Word document?

— ED

Hey, ED. You know, one of the drawbacks to calling yourself a “Scripting Guy” is that people assume you’re really more of a Scripting Fanatic. Every now and then we get emails that say, “Hey, Scripting Guy! I read your column on X. Well, I do X all the time, and I can do it faster and easier by using the GUI/a command line tool/magic. What do you have to say about that?”

Well, to tell you the truth, this is what have to say about that: “Good for you.” Believe it or not, we don’t think you should script everything you do; in fact, depending on what it is you do, maybe you shouldn’t even use scripts at all. If you find that the GUI or a command-line tool (or, yes, magic) works better for you, well, then that’s probably the approach you should take. Do whatever works best for you.

Of course, there will be times when there is no easy way to carry out a task using the GUI or a command-line tool. (We’re not sure about magic; that falls into kind of a gray area.) Those are the times when scripting can be a real life-saver. And, by amazing coincidence, the chore of copying all the comments from an Excel worksheet to a Word document falls into that very category. Short of methodically copying and pasting each individual comment is there any way to accomplish this task using the GUI? Not as far as we know. And don’t even ask about the command-line; needless to say, Microsoft Office doesn’t exactly ship with a boatload of command-line tools.

With a script, however, copying all the comments from an Excel worksheet to a Word document is no more difficult than this:

Set objExcel = CreateObject(“Excel.Application”)
objExcel.Visible = True
Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

Set colComments = objWorksheet.Comments
For Each objComment in colComments
    strComments = strComments & objComment.Text & vbCrlf & vbCrLf
Next
objExcel.Quit
Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
Set objDoc = objWord.Documents.Add()
Set objSelection = objWord.Selection
objSelection.TypeText strComments

So how does the script work? Well, we begin by creating an instance of the Excel.Application object and then set the Visible property to True. The latter is actually optional: the script works just fine if Excel is never visible on screen. We like to make the application visible simply so you can see what’s going on. (Of course, if you have only a handful of comments in the spreadsheet then Excel will flash onto and off of the screen so quickly you might miss it anyway.)

After creating an instance of Excel we then use these three lines of code to open the spreadsheet C:\Scripts\Test.xls and make Sheet1 (the worksheet where our comments reside) the active worksheet:

Set objWorkbook = objExcel.Workbooks.Open(“C:\Scripts\Test.xls”)
Set objWorksheet = objWorkbook.Worksheets(1)
objWorksheet.Activate

All of that is a prelude to this line of code:

Set colComments = objWorksheet.Comments

As it turns out, all the comments for a worksheet are stored in the Comments collection. To retrieve this collection all we have to do is create an object reference (here named colComments) to the worksheet’s Comments property. It’s that easy.

Of course, that poses a new problem: what do we do with those comments once we have them? Turns out that this is no problem at all: because the comments are stored in a collection, we can set up a For Each loop to walk through all the items in that collection:

For Each objComment in colComments
    strComments = strComments & objComment.Text & vbCrlf & vbCrLf
Next

As you can see, we don’t do anything particularly complicated within that For Each loop. In fact, all we do is assign a value to a variable named strComments. The first time we run through the loop strComments gets assigned the existing value of strComments (which is nothing, seeing as how we haven’t assigned a value to it yet) plus the Text of the first comment in the collection plus a pair of carriage return-linefeeds (vbCrLf). That’s easy enough, right?

Now, what happens the second time through the loop? Pretty much the same thing: strComments gets assigned the existing value of strComments (which, the second time around, will be the Text of comment 1) plus the Text of the second comment in the collection plus a pair of carriage return-linefeeds (vbCrLf). By the time we finish with the loop the Text of all of the comments will be stashed in the variable strComments.

In other words, without resorting to copying, pasting, or magic spells we can easily grab all the comments from the worksheet.

After we’ve grabbed the comments we have no further use for Excel, so we simply call the Quit method to dismiss the application.

Now we turn our attention to Microsoft Word. To begin with, we create an instance of the Word.Application object and set the Visible property to True. We then use the Add method to create a new, blank document to work with.

As soon as we have a blank document we create an instance of Word’s Selection object, then use this one line of code to write the extracted comments into the Word document:

objSelection.TypeText strComments

Is this really that easy? Give the script a try and see for yourself.

Oh: and if anyone needs eggs, please let us know. We always considered Peter a so-so Scripting Guy, but we have to admit, he’s one heckuva chicken!