VBA Inserting Comments from Excel to Word

Question

I'm new to VBA and I'm having difficulty trying to insert comments from data that I have in Excel onto a Word document. I am trying to write the VBA in Word and want it to extract data from a separate spreadsheet

Sub ConvertCelltoWordComment()

Dim Rng As Range
Dim wApp As Object
Dim strValue As String
Dim xlapp As Object
Dim xlsheet As Object
Dim xlbook As Object

'Opens Excel'

    Set xlapp = GetObject("C:\Users\eugenechang\Desktop\...xlsx")

If Err Then
     Set xlapp = CreateObject("Excel.Application")
End If

On Error GoTo 0

Dim i As Integer

For i = 1 To 5
    With xlsheet
        strValue = ActiveSheet.Cells(i, 1).Offset(1, 0)
    End With
    'Insert comment into document'

    ActiveDocument.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="15"
    ActiveDocument.Selection.GoTo What:=wdGoToLine, Which:=wdGoToRelative, Count:=5
    ActiveDocument.Comments.Add Range:=Selection.Range, Text:=strValue
Next i

End Sub

I'm trying to get it to work, but it is giving me an error "Object not defined". I've tried setting up an object within the strValue line below "With xlsheet", but am hitting a wall. Any help??

Answer 1

You have not assigned anything to xlsheet - so this (by default) equates to Nothing.

Try setting xlSheet to something meaningful. The following is only an example:

For i = 1 To 5
    Set xlsheet = xlbook.Worksheets(i) ' <--- example here
    With xlsheet
        strValue = .Cells(i, 1).Offset(1, 0) '<-- don't use ActiveSheet
    End With
    'Insert comment into document'

    ActiveDocument.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, Name:="15"
    ActiveDocument.Selection.GoTo What:=wdGoToLine, Which:=wdGoToRelative, Count:=5
    ActiveDocument.Comments.Add Range:=Selection.Range, Text:=strValue
Next I

An important note here is that you also have not set xlbook - you must also assign something meaningful to xlbook.

Answer 2

Add a couple DocVariables to your Word file and run the script below, from Excel.

Sub PushToWord()

Dim objWord As New Word.Application
Dim doc As Word.Document
Dim bkmk As Word.Bookmark
sWdFileName = Application.GetOpenFilename(, , , , False)
Set doc = objWord.Documents.Open(sWdFileName)
'On Error Resume Next

objWord.ActiveDocument.variables("FirstName").Value = Range("FirstName").Value
objWord.ActiveDocument.variables("LastName").Value = Range("LastName").Value
' etc., etc., etc.

objWord.ActiveDocument.Fields.Update

'On Error Resume Next
objWord.Visible = True

End Sub

Answer 3

This ended up writing comments from an Excel file. Obviously the names have been changed for privacy reasons. Please let me know if I can simplify this better.

Sub ConvertExceltoWordComment()

Dim wApp As Word.Application
Dim xlApp As Excel.Application
Dim PgNum As Integer
Dim LineNum As Integer
Dim objSelection As Word.Document
Dim strpgSearch As Long
Dim strlinSearch As Long
Dim myRange As Range
Dim XlLog As Excel.Worksheet
Dim RowCount As Long

'Opens Copied Word document'

    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
If Err Then
    Set xlApp = CreateObject("Excel.Application")
End If

On Error GoTo 0

Dim SaveDoc As Excel.Workbook
Set SaveDoc = xlApp.Workbooks.Open("FilePath.xlsm") 'Type filepath of document here'

Set XlLog = SaveDoc.Sheets("Worksheet_Name") 'Type Sheetname here'
RowCount = XlLog.Range("A1048576").End(xlUp).Row
If RowCount > 0 Then

    Dim iTotalRows As Long
    iTotalRows = XlLog.Rows.Count      'Get total rows in the table'

    Dim txt As Variant
    Dim iRows As Long
End If
Dim i As Integer

'Insert comment into Word document'
    Set wApp = CreateObject("Word.Application")
    wApp.Visible = True
If Err Then
    Set wApp = CreateObject("Word.Application")
End If
Set objSelection = ActiveDocument

For iRows = 3 To iTotalRows
        txt = XlLog.Cells(iRows, 8).Text 'Grabs appropriate comment text'
        objSelection.Activate
        objSelection.SelectAllEditableRanges
        strpgSearch = XlLog.Cells(iRows, 2) 'Grabs appropriate Page number'
        strlinSearch = XlLog.Cells(iRows, 3) 'Grabs appropriate Line number'
        objSelection.ActiveWindow.Selection.GoTo What:=wdGoToPage, Which:=wdGoToNext, 
Name:=strpgSearch
        objSelection.ActiveWindow.Selection.GoTo What:=wdGoToLine, Which:=wdGoToRelative, 
Count:=strlinSearch
        Set myRange = ActiveWindow.Selection.Range
        ActiveDocument.Comments.Add Range:=myRange, Text:=txt
Next iRows

Set xlApp = Nothing
Set SaveDoc = Nothing
Set XlLog = Nothing
Set objSelection = Nothing
Set myRange = Nothing
Set wApp = Nothing
SaveDoc.Close

End Sub