Advise on "Best" or "Better" practices

[gmaxey]

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.

[Natasha]

I think this is the perfectly written. I have tries this method with Excel and it works efficiently. If I will find any other better way then I will definitely share with you.

[SamT]

In the case of incrementing rows to an unknown limit, the incremented index is best.

I tend to follow the industry wide programming practice of using i, j, and k, (or in the specific case of Excel Rows and Columns, r and c) for simple indexing variables.


i = i + 1' Generic indexing
r = r + 1 'Excel Row indexing
I also like to make it a practice of using Column Letters when the column isn't being incremented. Quick, Which Column is number 45?

oSheet.Cells(r, "A") = oPar.Range.Text

[gmaxey]

Natasha, thank you.

BREAK

SamT, but what if there is already data in row 1. What is the best way to determine the value to initialize lngIndex

[SamT]

LastUsedRow = Cells(Rows.Count("A").End(xlUp).Row
FirstEmptyRow = Cells(Rows.Count("A").End(xlUp).Row + 1

Rows.Count is the Row number of the bottom row on any sheet.

ps: I was editing my previous when you posted that question

[gmaxey]

Thanks!