'Speeding up a VBA Script to Update MS Word Links
I've got a VBA script that loops through a folder containing word docs, opening them, updating the links, saving and closing them and it's running slowly, about 20 seconds to cycle through one word doc.
I've tried searching for others having similar issues but I can't seem to figure out how to get their solutions to work in this code and I'm not very familiar with VBA.
I'm guessing the speed of the whole operation is related to the time it takes to open an instance of MS Word and close it out each time. Is there a way this can be done in the background or by keeping MS Word open and just opening & closing documents? There's a decent amount of files to get through so any tips on how to increase the speed would be much appreciated! Happy to provide any additional info if it helps.
Sub UpdateSpecHeaders()
Dim oWordApp As Object
Dim oWordDoc As Object
Dim sFolder As String, strFilePattern As String
Dim strFileName As String, sFileName As String
'> Folder containing files to update
sFolder = Range("A20").Value
'> Identify file extension to search for
strFilePattern = "*.doc"
'> Establish a Word application object
On Error Resume Next
Set oWordApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
Set oWordApp = CreateObject("Word.Application")
End If
Err.Clear
On Error GoTo 0
oWordApp.Visible = True
Application.ScreenUpdating = False
'> Loop through the folder to get the word files
strFileName = Dir$(sFolder & strFilePattern)
Do Until strFileName = ""
sFileName = sFolder & strFileName
'> Open the word doc
Set oWordDoc = oWordApp.Documents.Open(sFileName)
Application.DisplayAlerts = False
'> Update Fields
oWordApp.ActiveDocument.Fields.Update
'> Save and close the file
oWordDoc.Save
oWordDoc.Close SaveChanges:=True
'> Find next file
strFileName = Dir$()
Loop
'> Quit and clean up
Application.ScreenUpdating = True
oWordApp.Quit
Set oWordDoc = Nothing
Set oWordApp = Nothing
End Sub
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
