jmccoughlin
12-17-2015, 08:29 AM
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
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