Macro for extracting selected text and comment from Word to Excel on Mac

[aminchicago]

Hi! I am not a developer, but trying to use this video and its comments to install a macro (on a mac) that extracts selected text and its associated comment. The following macro works for comments, but does not include the selected text. My attempts to use other macros found on this site and elsewhere haven't worked at all. Would anyone be able to suggest a line of code that would include the selected text in the macro? I'd really appreciate it!

Sub CopyCommentsToExcel()
'Create in Word vba
Dim xlApp As Object
Dim xlWB As Object
Dim i As Integer
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
xlApp.Application.Visible = True
Set xlWB = xlApp.Application.Workbooks.Add ' create a new workbook
With xlWB.Worksheets(1)
For i = 1 To ActiveDocument.Comments.Count
.Cells(i, 1).Formula = ActiveDocument.Comments(i).Initial
.Cells(i, 2).Value = ActiveDocument.Comments(i).Range.Text 'Comment Text

Next i
End With
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

 

[Tetra201]

Try inserting the following line of code after Next i

If Selection.Type = wdSelectionNormal Then .Cells(i, 1).Value = Selection.Text

 

[aminchicago]

I greatly appreciate your help, @Tetra201. I tried that and it produces an excel spreadsheet where column A is my initials, and column b is the comment, with no selected text or other content. Here's how I inserted the code - do you see any errors that might be preventing the third column of selected test from forming?

Sub CopyCommentsToExcel()
'Create in Word vba
Dim xlApp As Object
Dim xlWB As Object
Dim i As Integer
On Error Resume Next
Set xlApp = GetObject(, "Excel.Application")
If Err Then
Set xlApp = CreateObject("Excel.Application")
End If
On Error GoTo 0
xlApp.Application.Visible = True
Set xlWB = xlApp.Application.Workbooks.Add ' create a new workbook
With xlWB.Worksheets(1)
For i = 1 To ActiveDocument.Comments.Count
.Cells(i, 1).Formula = ActiveDocument.Comments(i).Initial
.Cells(i, 2).Value = ActiveDocument.Comments(i).Range.Text 'Comment Text

Next i
If Selection.Type = wdSelectionNormal Then .Cells(i, 1).Value = Selection.Text
End With
Set xlWB = Nothing
Set xlApp = Nothing
End Sub

 

[Tetra201]

Yes, you inserted the line correctly. Before posting my suggestion, I have tested it to work in Word/Excel for Mac 2011 (14.7.7). It places the selected text from Word to column A in Excel after the last initial.

If you want the selected text from Word to appear in column C and if you have multiple selections, try the following line instead (also tested to work in Word/Excel for Mac 2011):

If Selection.Type = wdSelectionNormal Then Selection.Copy: .Paste .Cells(1, 3)

 

[aminchicago]

Hey @Tetra201, bless you for trying again. Unfortunately this didn't work. I continue to get a spreadsheet with Column A = my initials, Column B = comment text, and nothing in Column C. I am on Microsoft 365, so perhaps that's the difference. I've also tried creating an Apple Script, and I run into this same inability to extract the selected text. Thank you for trying!

 

[arts4health]

Hi, I'm trying to do the same thing. @Tetra201
I think the issue is something with "selected text" - when I had highlighted text in my word doc it exported in (albeit in column A after the initials).
I think both @aminchicago and I are trying to export the comments and the text the comment itself is linked to.
So the excel columns would be:
commenter initials - quoted text - comment - date