Hi All,
Not sure if this falls in the Word or Excel VBA bucket (I think Excel?). I've been trying to stitch together a Word VBA Script that takes all the Word comments and puts them into a new Excel spreadsheet, then formats the Excel spreadsheet.
However the "Excel" portion of the code seems to fail after every "other" run. Specifically the error occurs when trying to Left-Align Column C. The error that occurs is "Error 91 - Object variable or With block variable not set". The error happens every 2 runs.
Would anyone know how to fix the Error that occurs below, please? Thanks so much in advance!
(The offending/highlighted line below is: ActiveSheet.Columns("C").HorizontalAlignment = xlHAlignLeft )
Function CopyWordCommentstoNewExcelFile()
'Takes MS Word comments and puts them 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 'COL A
'SECTION NUMBER
strDocSection = ReplaceAndSplit(ActiveDocument.Comments(i).Range, "(,)#")(1)
.Cells(i + HeadingRow, 2).Formula = strDocSection 'COL B
'STEP NUMBER
strStempNumber = GetBetween(ActiveDocument.Comments(i).Range, ", #", ")")
.Cells(i + HeadingRow, 3).Formula = strStempNumber 'COL C
'COMMENT
strComment = GetBetween(ActiveDocument.Comments(i).Range, ") ", " (")
.Cells(i + HeadingRow, 4).Formula = strComment 'COL D
Next i
'--BEGIN FORMATTING-- 'THE PROBLEM HAPPENS WHEN RUNNING THE LEFT-ALIGN CODE BELOW
'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
Bookmarks