VBA - Why opening Word from Excel is slow?

Question

I wondered if anyone can shed some light on why opening a Word document would take a few seconds from Excel? The code below quickly finds/opens a specific subfolder using InStr i.e. debug.print of the subfolder name is immediate, however opening the specific Word doc takes about 4 seconds. I tried testing a similar procedure in Word itself it opened the document almost immediately. I'm still learning VBA and I'm not sure what the reason would be other than its something to do with the last bit re strFile Any suggestions would be appreciated.

Sub LoopSubfolderAndFiles()

Dim fso As Object
Dim folder As Object
Dim subfolder1 As Object
Dim strTextFind1 As String
Dim strFileFound As String
Dim CurrFile As Object
Dim myFile As Object
Dim strFile As String
Dim strExtension As String
Dim wordApp As New Word.Application

Set fso = CreateObject("Scripting.FileSystemObject")
Set folder = fso.GetFolder("Enter FILEPATH name..........")
Set subfolder1 = folder.subfolders

strTextFind1 = "useful"
strFileFound = "test"
strExtension = ".doc"

For Each subfolder1 In subfolder1
If InStr(1, subfolder1.Name, strTextFind1, vbTextCompare) > 0 Then
        Set CurrFile = fso.GetFolder(subfolder1)
        Debug.Print subfolder1.Name
Exit For
End If
Next

For Each CurrFile In CurrFile.Files
If InStr(1, CurrFile.Name, strFileFound, vbTextCompare) > 0 Then
    Set myFile = fso.GetFile(CurrFile)
    strFile = myFile.Path
   wordApp.Visible = True
   wordApp.Documents.Open (strFile)
   Debug.Print strFile
End If
Next

Set fso = Nothing
Set folder = Nothing
Set subfolder1 = Nothing
Set CurrFile = Nothing
End Sub

It seems 4 seconds is not that bad at all considering the fact that you are calling Word from Excel and it does some other stuff before even starting to open the word document if you said it takes 40 seconds that would have been a problem, but why 4 seconds is a problem for you? — Ibo, Sep 12 '18 at 05:17

CatCat · Answer 1 · 2018-09-12T11:54:44.937

There is nothing substantive wrong with your code. Word is slow.

The difference could be inprocess vs outofprocess. Out of Process calls are made using the RPC networking remote call procedure. Hidden windows are created so messages can be received. It's all very complicated so out of process calls work under all circumstances. In Process Calls are just a machine code jump instruction. Several clock cycles vs tens of thousands or more.

There are some minor issues.

These lines are pointless. This is handled at the end of each line for implicit variables and every end function etc for explicit variables. See Declaring Variables Memory Leaks

Set fso = Nothing
Set folder = Nothing
Set subfolder1 = Nothing
Set CurrFile = Nothing

If you want to do this indirection then they need to be const. The compiler will put them into the line where used as literals. Use variables only where needed.

strTextFind1 = "useful"
strFileFound = "test"
strExtension = ".doc"

So

const strTextFind1 = "useful"
const strFileFound = "test"
const strExtension = ".doc"

You are late binding to FSO. Use early binding as you do for Word. See Pointers needed for speeding up nested loop macro in VBA. Then instead of Dim folder As Object dim it as you do word.

score 2 · Answer 2 · answered Sep 12 '18 at 05:18

Looking at your code, it's not just opening the document, it's also starting a new instance of the Word application*. So there are a number of factors that are taking time:

Starting Word. Have you ever timed how long it takes Word to start when you click the icon? First, the application itself needs to load. Then, there may be any number of add-ins loading, which will take time.
When an outside application "automates" another application there is a time "hit" for the "cross-barrier" communication. VBA within an Office application is usually quite fast; the same commands run from a different application will be (noticeably) slower.

'* You should never declare and instantiate an application in the same line in VBA. You should change your code to:

Dim wordApp as Word.Application
Set wordApp = New Word.Application

Thanks for the feedback, much appreciated – Carey Sep 12 '18 at 05:48 — Carey, Sep 12 '18 at 05:48

VBA - Why opening Word from Excel is slow?

2 Answers2