gmaxey
02-05-2016, 09:41 AM
I dabble with Word VBA and occasionally I have to venture into excel. For example, today I needed to extract some Word text meeting a condition to an Excel file:
Sub ExtractToExcel()
Dim oApp as Object, oSheet as Object
Dim oPar as Paragraph
Set oApp = CreateObject("Excel.Application")
Set oSheet = oApp.workbooks.Open("D:\Test.xlsx").Sheets("Sheet1")
lngIndex = 1
For Each oPar In ActiveDocument.Paragraphs
If oPar.Range.HighlightColorIndex = wdBrightGreen Then
oPar.Range.HighlightColorIndex = wdAuto
oSheet.Cells(lngIndex, 1) = oPar.Range.Text
lngIndex = lngIndex + 1
End If
Next oPar
oApp.workbooks(1).Close True
oApp.Quit
Set oApp =Nothing: oSheet = Nothing
End Sub
It works, but I'm wondering if it is written to work most efficiently with Excel. For example instead of the counter, is there a better way simple put data in the first empty row?
Thanks.
Sub ExtractToExcel()
Dim oApp as Object, oSheet as Object
Dim oPar as Paragraph
Set oApp = CreateObject("Excel.Application")
Set oSheet = oApp.workbooks.Open("D:\Test.xlsx").Sheets("Sheet1")
lngIndex = 1
For Each oPar In ActiveDocument.Paragraphs
If oPar.Range.HighlightColorIndex = wdBrightGreen Then
oPar.Range.HighlightColorIndex = wdAuto
oSheet.Cells(lngIndex, 1) = oPar.Range.Text
lngIndex = lngIndex + 1
End If
Next oPar
oApp.workbooks(1).Close True
oApp.Quit
Set oApp =Nothing: oSheet = Nothing
End Sub
It works, but I'm wondering if it is written to work most efficiently with Excel. For example instead of the counter, is there a better way simple put data in the first empty row?
Thanks.