Return to Answer

#Performance I see three major issues with the performance of this code.

1. GUI operations are expensive and slow. Remove these lines, they're doing nothing but slowing you down.

ActiveSheet.Range(currentCellToAdd).Activate

 ActiveWindow.ScrollRow = ActiveCell.Row

You should avoid using activate and select anyway. They tend to lead to nasty bugs.

2. Don't loop through all of the text in the word document. Find it instead. Here's some pseudo code to get you started.

   Dim doc as Document
   Dim currentRange as Range
   Set doc = ActiveDocument
   Set currentRange = doc.Range
   With currentRange.Find
    .Forward = True
    .Text = strName
    .Execute 'execute will update current range to the first found instance
    If .Found Then
    SearchWordDoc = currentRange.Text ' in reality, I suspect you'll need to offset this
    End If
   End With 
   

If you're interested, you can take a look at this example of using Range.Find in word.

3. You create a new instance of Word and open the same document up each time you call the SearchWordDoc function. Both creating an instance of Word and opening documents are slow and expensive operations. You should instance Word and open the doc inside of SearchTextFile and pass the document as an argument.

#Other Notes

• Ditch the Hungarian notation. You're not doing it right, but don't feel bad. No one really does.

• Replace Magic Numbers with meaningfully named constants.

For i = 2 To 4

I imagine firstColumn and lastColumn would be good here.

• You should start using Option Explicit. It forces you to declare all of your variables. This means that everything will be strongly typed, which is good. It stops a lot of nasty run-times errors from ever happening.

• I highly recommend that you use early binding instead of late binding. It's kind of a debated topic, but I think the benefits outweigh the drawbacks. Just having intellisense alone makes early binding worth it in my mind.

#Quick Performance Boost

Inside of SearchWordDoc you can declare i as a static variable. This will allow i to retain it's value in between executions. This is, in effect, keeping track of the last paragraph where you found what you were looking for. However, be warned that static variables make code confusing and hard to debug. I don't recommend it as a long term solution.

#Performance I see three major issues with the performance of this code.

1. GUI operations are expensive and slow. Remove these lines, they're doing nothing but slowing you down.

ActiveSheet.Range(currentCellToAdd).Activate

 ActiveWindow.ScrollRow = ActiveCell.Row

You should avoid using activate and select anyway. They tend to lead to nasty bugs.

2. Don't loop through all of the text in the word document. Find it instead. Here's some pseudo code to get you started.

   Dim doc as Document
   Dim currentRange as Range
   Set doc = ActiveDocument
   Set currentRange = doc.Range
   With currentRange.Find
    .Forward = True
    .Text = strName
    .Execute 'execute will update current range to the first found instance
    If .Found Then
    SearchWordDoc = currentRange.Text ' in reality, I suspect you'll need to offset this
    End If
   End With 
   

If you're interested, you can take a look at this example of using Range.Find in word.

3. You create a new instance of Word and open the same document up each time you call the SearchWordDoc function. Both creating an instance of Word and opening documents are slow and expensive operations. You should instance Word and open the doc inside of SearchTextFile and pass the document as an argument.

#Other Notes

• Ditch the Hungarian notation. You're not doing it right, but don't feel bad. No one really does.

• Replace Magic Numbers with meaningfully named constants.

For i = 2 To 4

I imagine firstColumn and lastColumn would be good here.

• You should start using Option Explicit. It forces you to declare all of your variables. This means that everything will be strongly typed, which is good. It stops a lot of nasty run-times errors from ever happening.

• I highly recommend that you use early binding instead of late binding. It's kind of a debated topic, but I think the benefits outweigh the drawbacks. Just having intellisense alone makes early binding worth it in my mind.

#Quick Performance Boost

Inside of SearchWordDoc you can declare i as a static variable. This will allow i to retain it's value in between executions. This is, in effect, keeping track of the last paragraph where you found what you were looking for. However, be warned that static variables make code confusing and hard to debug. I don't recommend it as a long term solution.

#Performance I see three major issues with the performance of this code.

1. GUI operations are expensive and slow. Remove these lines, they're doing nothing but slowing you down.

ActiveSheet.Range(currentCellToAdd).Activate

 ActiveWindow.ScrollRow = ActiveCell.Row

You should avoid using activate and select anyway. They tend to lead to nasty bugs.

2. Don't loop through all of the text in the word document. Find it instead. Here's some pseudo code to get you started.

   Dim doc as Document
   Dim currentRange as Range
   Set doc = ActiveDocument
   Set currentRange = doc.Range
   With currentRange.Find
    .Forward = True
    .Text = strName
    .Execute 'execute will update current range to the first found instance
    If .Found Then
    SearchWordDoc = currentRange.Text ' in reality, I suspect you'll need to offset this
    End If
   End With 
   

If you're interested, you can take a look at this example of using Range.Find in word.

3. You create a new instance of Word and open the same document up each time you call the SearchWordDoc function. Both creating an instance of Word and opening documents are slow and expensive operations. You should instance Word and open the doc inside of SearchTextFile and pass the document as an argument.

#Other Notes

• Ditch the Hungarian notation. You're not doing it right, but don't feel bad. No one really does.

• Replace Magic Numbers with meaningfully named constants.

For i = 2 To 4

I imagine firstColumn and lastColumn would be good here.

• You should start using Option Explicit. It forces you to declare all of your variables. This means that everything will be strongly typed, which is good. It stops a lot of nasty run-times errors from ever happening.

• I highly recommend that you use early binding instead of late binding. It's kind of a debated topic, but I think the benefits outweigh the drawbacks. Just having intellisense alone makes early binding worth it in my mind.

#Quick Performance Boost

Inside of SearchWordDoc you can declare i as a static variable. This will allow i to retain it's value in between executions. This is, in effect, keeping track of the last paragraph where you found what you were looking for. However, be warned that static variables make code confusing and hard to debug. I don't recommend it as a long term solution.

#Performance I see three major issues with the performance of this code.

1. GUI operations are expensive and slow. Remove these lines, they're doing nothing but slowing you down.

ActiveSheet.Range(currentCellToAdd).Activate

 ActiveWindow.ScrollRow = ActiveCell.Row

You should avoid using activate and select anyway. They tend to lead to nasty bugs.

2. Don't loop through all of the text in the word document. Find it instead. Here's some pseudo code to get you started.

   Dim doc as Document
   Dim currentRange as Range
   Set doc = ActiveDocument
   Set currentRange = doc.Range
   With currentRange.Find
    .Forward = True
    .Text = strName
    .Execute 'execute will update current range to the first found instance
    If .Found Then
    SearchWordDoc = currentRange.Text ' in reality, I suspect you'll need to offset this
    End If
   End With 
   

If you're interested, you can take a look at this example of using Range.Find in word.

3. You create a new instance of Word and open the same document up each time you call the SearchWordDoc function. Both creating an instance of Word and opening documents are slow and expensive operations. You should instance Word and open the doc inside of SearchTextFile and pass the document as an argument.

#Other Notes

• Ditch the Hungarian notation. You're not doing it right, but don't feel bad. No one really does.

• Replace Magic Numbers with meaningfully named constants.

For i = 2 To 4

I imagine firstColumn and lastColumn would be good here.

• You should start using Option Explicit. It forces you to declare all of your variables. This means that everything will be strongly typed, which is good. It stops a lot of nasty run-times errors from ever happening.

• I highly recommend that you use early binding instead of late binding. It's kind of a debated topic, but I think the benefits outweigh the drawbacks. Just having intellisense alone makes early binding worth it in my mind.

#Quick Performance Boost

Inside of SearchWordDoc you can declare i as a static variable. This will allow i to retain it's value in between executions. This is, in effect, keeping track of the last paragraph where you found what you were looking for. However, be warned that static variables make code confusing and hard to debug. I don't recommend it as a long term solution.

#Performance I see two major issues with the performance of this code.

#Immediate Performance Boost

It would also be faster to open word outside of this function, and pass the doc object into it. Leaving it open until you're done processing the excel file.

'Searches word file for name, finds the associated paragraph, and returns the date'
Function SearchWordDoc(strPath, strName, objWord)
 Static i as long
 For i = 1 To a.Paragraphs.Count
 If InStr(a.Paragraphs(i).Range.Text, strName) <> 0 Then
 SearchWordDoc = Left(Right(a.Paragraphs(i).Range.Text, 22), 11)
 End If
 Next i
End Function

#Performance I see three major issues with the performance of this code.

3. You create a new instance of Word and open the same document up each time you call the SearchWordDoc function. Both creating an instance of Word and opening documents are slow and expensive operations. You should instance Word and open the doc inside of SearchTextFile and pass the document as an argument.

• You should start using Option Explicit . It forces you to declare all of your variables. This means that everything will be strongly typed, which is good. It stops a lot of nasty run-times errors from ever happening.

• I highly recommend that you use early binding instead of late binding. It's kind of a debated topic, but I think the benefits outweigh the drawbacks. Just having intellisense alone makes early binding worth it in my mind.

#Quick Performance Boost