Problem when exporting MS Word comments into MS Excel

[jmccoughlin]

Hi All,

I've been trying to stitch together a Word VBA Script that takes all the Word comments and puts them into an Excel spreadsheet, then formats the spreadsheet.

It works great, but does anyone know why the "formatting" portion fails after every "other" run with "Error 91 - Object variable or With block variable not set" ?

Here's the code:


Function CopyMSWordCommentstoExcelFile()
'Takes MS Word comments and puts it into an Excel File

Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim i As Integer
Dim HeadingRow As Integer
HeadingRow = 3


Dim cmtRef As Range


Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlWB = xlApp.Workbooks.Add ' create a new workbook

With xlWB.Worksheets(1) 'START EXCEL INTEGRATION
'Create report info
.Cells(1, 1).Formula = "Reviewed document:"


'Create Heading
.Cells(HeadingRow, 1).Formula = "Issue #"
.Cells(HeadingRow, 2).Formula = "Section #"
.Cells(HeadingRow, 3).Formula = "Step #"
.Cells(HeadingRow, 4).Formula = "Issue abstract/comment/resolution:"

Dim strStep, strComment As String

For i = 1 To ActiveDocument.Comments.Count

'ISSUE #
.Cells(2, 1).Formula = ActiveDocument.Comments(i).Parent
.Cells(i + HeadingRow, 1).Formula = ActiveDocument.Comments(i).Index

'SECTION NUMBER
strDocSection = ReplaceAndSplit(ActiveDocument.Comments(i).Range, "(,)#")(1)
.Cells(i + HeadingRow, 2).Formula = strDocSection

'STEP NUMBER
strStempNumber = GetBetween(ActiveDocument.Comments(i).Range, ", #", ")")
.Cells(i + HeadingRow, 3).Formula = strStempNumber

'COMMENT
strComment = GetBetween(ActiveDocument.Comments(i).Range, ") ", " (")
.Cells(i + HeadingRow, 4).Formula = strComment

Next i


'--BEGIN FORMATTING--

'LEFT ALIGN COL C
ActiveSheet.Columns("C").HorizontalAlignment = xlHAlignLeft '<-- THIS FAILS AFTER EVERY-OTHER RUN WITH "ERROR 91 - Object variable or With block variable not set", BUT WORKS ON THE NEXT RUN

'--END FORMATTING--

End With 'END EXCEL INTEGRATION

End Function


Thanks so much,
JMC

[jmccoughlin]

SOLVED:

Credit given to someone else on another forum, but I wanted to share the answer here:

Remove the 'ActiveSheet' in front of the offending line:

With xlWB.Worksheets(1)
...
...
.Columns("C").HorizontalAlignment = xlHAlignLeft
...
...
End With