Tables, figures and listings must be bundled together and sent out to Statisticians for review. They usually add some comments. Programmers need to extract those comments and put them in an excel file. To pull all the comments out is time consuming. Today this post will guide you through how develop a VBA macro to complete the task.
Show Developer tab and Open Visual Basic Editor
First of all, let me introduce some basic knowledge. Developer tab is not displayed in Excel by default. For Microsoft Excel 2007, we can enable it to show Developer tab by clicking on Microsoft Office Button -> Excel options -> Popular and then select the Show Developer tab in the Ribbon check box. For Microsoft Excel 2010/2013/2016, we can click on the File tab -> Options to trigger out Excel options dialog box and then click on Customize Ribbon. Then select Developer which is under Main Tabs (This Main Tabs is Under Customize the Ribbon). Here I only show you a screenshot for Microsoft Excel 2010/2013/2016.
After showing up Developer tab, you can press Alt+F11 to trigger out Visual Basic Editor. Another approach is to click on Developer ->Visual Basic.
Design Macro
Now let’s move on and see what we would like this macro to do. We need it to retrive information from Word file (Input) and put comments (Output) in Excel file. And by running VBA (Action), Excel can finish the task by itselt. From this point of view, we need three elements. The left part in below figure represents Input element. We have to put word file from which we ‘d like to extract comments into cell B1. The right part is for Output element. Here I have created four columns to contain retrieved information such as author of comments, content of comments and on which day the comments are created. Finally the last thing – Action element – is exactly the button (in green box) in the left panel. By clicking on this button, Excel can run VBA.
Here is to show you how to insert button and assign a macro to it. Click on Developer tab -> Insert and then select the first one – button. After a plus sign appears, you can move the plus sign and draw a box which is a button. After you press up your mouse, Assign Macro dialog box will be showed up. Give your macro a name like Retrieve_Comments and then click on New. So far, the Visual Basic Editor will be opened and is ready for you to enter code. You can also right click on your button to change the text into “Extract Comments” as shown in above figure.
Put VBA code to into Visual Basic Editor
We can use Comments(Index), where Index is the index number, to return a single Comment object and it’s related information. The index number represents the position of the comment in the specified selection, range, or document. I will not make detailed explanation here and will introduce VBA syntax in future. This post is just use an example to show you how to develop a macro. After you put following code into your Visual Basic Editor, please do not forget to save your excel file as XLSM file. This file extension is for VBA macro.
Click here to hide/show code
Sub Retrive_Comments_Click()
‘Create object
Dim objWord As Object
Set objWord = CreateObject(“Word.Application”)
objWord.Visible = True
‘Open word file with comments
Dim WordNam As String
WordNam = ThisWorkbook.Worksheets(1).Cells(1, 2)
objWord.Documents.Open WordNam
ThisWorkbook.Worksheets(2).Activate
‘Clear comments extracted previously
For i = 2 To ActiveSheet.UsedRange.Rows.Count
For j = 1 To ActiveSheet.UsedRange.Columns.Count
Cells(i, j) = “”
Next j
Next i
a = 2
For i = 1 To objWord.Documents(WordNam).Comments.Count
‘Sequence number
ActiveSheet.Cells(a, 1) = a – 1
ActiveSheet.Cells(a, 1).HorizontalAlignment = xlCenter
‘Create date of comment
ActiveSheet.Cells(a, 2) = Format(objWord.Documents(WordNam).Comments(i).Date, “Short Date”)
ActiveSheet.Cells(a, 2).HorizontalAlignment = xlLeft
‘Author of Comment
ActiveSheet.Cells(a, 3) = objWord.Documents(WordNam).Comments(i).Author
ActiveSheet.Cells(a, 3).WrapText = True
‘Comment
ActiveSheet.Cells(a, 4) = objWord.Documents(WordNam).Comments(i).Range.Text
ActiveSheet.Cells(a, 4).WrapText = True
a = a + 1
Next i
Columns(“A”).ColumnWidth = 5
Columns(“C”).ColumnWidth = 18
Columns(“D”).ColumnWidth = 70
‘Colse word file with comments
objWord.Documents.Close
objWord.Quit
End Sub
Test your macro
Put the full path of your Microsoft Word file which contains comments into cell B1 of the first worksheet. By clicking on the button, you will see the comments will be extracted successfully.
Like the post? Welcome to share or you can subscribe to get latest post. How to subscribe? Go to the top-right corner of this web page to submit your name and email address. If you cannot receive emails from us, please check your spam/junk folder.
