{"id":67033,"date":"2006-06-26T12:56:00","date_gmt":"2006-06-26T12:56:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2006\/06\/26\/how-can-i-copy-all-the-comments-from-an-excel-worksheet-to-a-word-document\/"},"modified":"2006-06-26T12:56:00","modified_gmt":"2006-06-26T12:56:00","slug":"how-can-i-copy-all-the-comments-from-an-excel-worksheet-to-a-word-document","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/how-can-i-copy-all-the-comments-from-an-excel-worksheet-to-a-word-document\/","title":{"rendered":"How Can I Copy All the Comments From an Excel Worksheet to a Word Document?"},"content":{"rendered":"

\"Hey, \n

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

— ED<\/P>\"Spacer\"\"Hey,\"TechNet<\/A> \n

Hey, ED. You know, one of the drawbacks to calling yourself a \u201cScripting Guy\u201d is that people assume you\u2019re really more of a Scripting Fanatic. Every now and then we get emails that say, \u201cHey, Scripting Guy! I read your column on X<\/I>. Well, I do X<\/I> 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<\/I>?\u201d<\/P>\n

Well, to tell you the truth, this is what have to say about that: \u201cGood for you.\u201d Believe it or not, we don\u2019t think you should script everything<\/I> you do; in fact, depending on what it is you do, maybe you shouldn\u2019t 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\u2019s probably the approach you should take. Do whatever works best for you.<\/P>\n

Of course, there will be times when there is<\/I> no easy way to carry out a task using the GUI or a command-line tool. (We\u2019re not sure about magic; that falls into kind of a gray area.) Those<\/I> 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\u2019t even ask about the command-line; needless to say, Microsoft Office doesn\u2019t exactly ship with a boatload of command-line tools. <\/P>\n\n<\/THEAD>\n\n\n
\n

Note<\/B>. In all fairness, we should point out that magic did <\/I>work in this case; however, the spell we cast had the unfortunate effect of turning Peter into a chicken. To put a new spin on an old joke, we\u2019d turn him back, except we need the eggs.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n

<\/DIV>\n

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

Set objExcel = CreateObject(“Excel.Application”)\nobjExcel.Visible = True\nSet objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate<\/p>\n
Set colComments = objWorksheet.Comments<\/p>\n
For Each objComment in colComments\n    strComments = strComments & objComment.Text & vbCrlf & vbCrLf\nNext<\/p>\n
objExcel.Quit<\/p>\n
Set objWord = CreateObject(“Word.Application”)\nobjWord.Visible = True\nSet objDoc = objWord.Documents.Add()\nSet objSelection = objWord.Selection<\/p>\n
objSelection.TypeText strComments\n<\/PRE>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. In case you\u2019re wondering, it took us just two minutes to create this script. Granted, we had an advantage because we copied some boilerplate code; in addition we also had a pretty good idea of how to carry out the task before we started. Still, you can see that scripting doesn\u2019t have to be a vast, time-consuming enterprise. After all, we were able to toss this script together in no time, even though Peter ate our first draft.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
So how does the script work? Well, we begin by creating an instance of the Excel.Application<\/B> object and then set the Visible<\/B> 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\u2019s 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.)<\/P>\n
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:<\/P>
Set objWorkbook = objExcel.Workbooks.Open(“C:\\Scripts\\Test.xls”)\nSet objWorksheet = objWorkbook.Worksheets(1)\nobjWorksheet.Activate\n<\/PRE>\n
All of that is a prelude to this line of code:<\/P>
Set colComments = objWorksheet.Comments\n<\/PRE>\n
As it turns out, all the comments for a worksheet are stored in the Comments<\/B> collection. To retrieve this collection all we have to do is create an object reference (here named colComments) to the worksheet\u2019s Comments<\/B> property. It\u2019s that easy.<\/P>\n
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:<\/P>
For Each objComment in colComments\n    strComments = strComments & objComment.Text & vbCrlf & vbCrLf\nNext\n<\/PRE>\n
As you can see, we don\u2019t 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\u2019t assigned a value to it yet) plus the Text<\/B> of the first comment in the collection plus a pair of carriage return-linefeeds (vbCrLf). That\u2019s easy enough, right?<\/P>\n
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<\/I> of the comments will be stashed in the variable strComments.<\/P>\n
In other words, without resorting to copying, pasting, or magic spells we can easily grab all the comments from the worksheet.<\/P>\n
After we\u2019ve grabbed the comments we have no further use for Excel, so we simply call the Quit<\/B> method to dismiss the application.<\/P>\n
Now we turn our attention to Microsoft Word. To begin with, we create an instance of the Word.Application<\/B> object and set the Visible <\/B>property to True. We then use the Add<\/B> method to create a new, blank document to work with.<\/P>\n\n<\/THEAD>\n\n\n
\n
Note<\/B>. We know: if you\u2019ve never written scripts that interact with Microsoft Office applications much of this column likely sounds like gibberish. (Sadly, even if you have<\/I> written scripts that interact with Microsoft Office applications this column occasionally sounds like gibberish. But that\u2019s a different story.) If you\u2019re new to Microsoft Office scripting you might find it useful to take a peek at the articles in our Office Space archive<\/B><\/A>.<\/P><\/TD><\/TR><\/TBODY><\/TABLE>\n
<\/DIV>\n
As soon as we have a blank document we create an instance of Word\u2019s Selection<\/B> object, then use this one line of code to write the extracted comments into the Word document:<\/P>
objSelection.TypeText strComments\n<\/PRE>\n
Is this really that<\/I> easy? Give the script a try and see for yourself.<\/P>\n
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\u2019s one heckuva chicken!<\/P><\/p>\n","protected":false},"excerpt":{"rendered":"
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 \u201cScripting Guy\u201d is that people assume you\u2019re really more of a Scripting Fanatic. Every now and then we get emails that say, \u201cHey, Scripting […]<\/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":[48,84,49,3,5],"class_list":["post-67033","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-microsoft-excel","tag-microsoft-word","tag-office","tag-scripting-guy","tag-vbscript"],"acf":[],"blog_post_summary":"
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 \u201cScripting Guy\u201d is that people assume you\u2019re really more of a Scripting Fanatic. Every now and then we get emails that say, \u201cHey, Scripting […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67033","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=67033"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/67033\/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=67033"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=67033"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=67033"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}