CODEWITHSUNDEEP
vbams-word
LearningToCode
LearningToCode

Reputation: 11

Exporting Comments from Word to Excel

I am getting an error when I try to run the following code in word to extract comments. The yellow highlight is over Public Sub PrintFirstColumnOnActiveSheetToSheetName(), which then says I have a compile error: variable not defined and highlights "Activesheet" within the Activesheet.range("A1") at the bottom of the code

Option Explicit

Public Sub FindWordComments()


Dim objExcelApp As Object
Dim wb As Object
Set objExcelApp = CreateObject("Excel.Application")
Set wb = objExcelApp.Workbooks.Open("C:\Desktop\Book11")

Dim myWord              As Word.Application
Dim myDoc               As Word.Document
Dim thisComment         As Word.Comment

Dim fDialog             As Office.FileDialog
Dim varFile             As Variant

Dim destSheet           As Worksheet
Dim rowToUse            As Integer
Dim colToUse            As Long

Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
Set destSheet = wb.Sheets("Book11")
colToUse = 1

With fDialog
    .AllowMultiSelect = True
    .Title = "Import Files"
    .Filters.Clear
    .Filters.Add "Word Documents", "*.docx"
    .Filters.Add "Word Macro Documents", "*.docm"
    .Filters.Add "All Files", "*.*"
End With

If fDialog.Show Then

    For Each varFile In fDialog.SelectedItems

        rowToUse = 2

    Set myWord = New Word.Application
    Set myDoc = myWord.Documents.Open(varFile)

    For Each thisComment In myDoc.Comments

        With thisComment
            destSheet.Cells(rowToUse, colToUse).Value = .Range.Text
            destSheet.Cells(rowToUse, colToUse + 1).Value = .Scope.Text
            destSheet.Columns(2).AutoFit
        End With

        rowToUse = rowToUse + 1

    Next thisComment

    destSheet.Cells(1, colToUse).Value = Left(myDoc.Name, 4)
    'Put name of interview object in cell A1

    destSheet.Cells(1, colToUse + 1).Value = ActiveDocument.Words.Count
    'Put the number of words in cell B1

    Set myDoc = Nothing
    myWord.Quit

    colToUse = colToUse + 2

Next varFile

End If

End Sub


Public Sub PrintFirstColumnOnActiveSheetToSheetName()


ActiveSheet.Name = ActiveSheet.Range("A1")


End Sub

Upvotes: 0

Views: 1017

Answers (2)

Cindy Meister
Cindy Meister

Reputation: 25663

Since you're running this in Word, VBA only "knows" things that belong to the Word object model unless the code specifically indicates a different object model is the source. Word knows nothing about "Sheets" - only Excel knows what ActiveSheet is.

Even though the code you show us doesn't call the procedure PrintFirstColumnOnActiveSheetToSheetName(), where ActiveSheet is used, VBA sees it when it compiles the code. So you need to pass a reference to the Excel application to this procedure and use it. For example:

Public Sub PrintFirstColumnOnActiveSheetToSheetName(objExcel as Object)
  objExcel.ActiveSheet.Name = objExcel.ActiveSheet.Range("A1")
End Sub

To call this elsewhere in your code it would look something like the following line. Notice that the variable names for the same object do not need to be the same in different procedures - they can be, but it's not necessary. VBA "remembers" that the Excel application started previously is what's been passed to another procedure.

PrintFirstColumnOnActiveSheetToSheetName objExcelApp

Upvotes: 0

macropod
macropod

Reputation: 13505

Perhaps you should be using destSheet rather than Activesheet, since Activesheet isn't defined. Otherwise, you should prefix Activesheet with objExcelApp, thus:

objExcelApp.Activesheet

Upvotes: 1

Related Questions