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!