I have a macro that opens a word document for the purpose of changing links. Sometimes, I get a Runtime Error 462: Remote Server machine does not exist. I think it has something to do with Word not being fully open yet. I've added a pause statement in my macro to allow time, but it's still cropping up sporadically. Is there any code that I can use to ensure that word is ready to go before the code proceeds?
Code:
Sub ChangeExcelSource()
Dim k As Integer
Dim ThisFile As String
Dim NewFile As String
Dim WordFile As String
Dim OldFile As String
Dim oRng, oFld As Field, i As Integer
Dim newLinkTxt As Field
Dim OldPath As String, NewPath As String, Parent As String
ThisFile = ActiveWorkbook.FullName
NewFile = ActiveWorkbook.Name
For i = 0 To (UBound(Split(ThisFile, "\")) - 0)
Parent = Parent & Split(ThisFile, "\")(i) & "\\"
Next i
NewPath = Parent
While Right(NewPath, 1) = "\"
NewPath = Left(NewPath, Len(NewPath) - 1)
Wend
WordFile = Application.GetOpenFilename
Dim appWD As Word.Application
Dim WDdoc As Document
Set appWD = CreateObject("Word.Application.8")
appWD.Visible = True
appWD.Documents.Open (WordFile)
appWD.Visible = True
Application.Wait Now + TimeValue("00:00:20")
MsgBox ("Starting Link Update")
For Each oRng In ActiveDocument.StoryRanges
For Each oFld In oRng.Fields
If InStr(Left(oFld.Code.Text, 30), "LINK Excel") <> 0 Then
If InStr(Split(oFld.Code.Text, Chr(34))(1), "!") <> 0 Then
OldPath = Split(oFld.Code.Text, Chr(34))(1)
OldPath = Split(OldPath, "!")(0)
oFld.Code.Text = Replace(oFld.Code.Text, OldPath, NewPath, , , 1)
End If
If InStr(Split(oFld.Code.Text, Chr(34))(1), "!") = 0 Then
OldPath = Split(oFld.Code.Text, Chr(34))(1)
oFld.Code.Text = Replace(oFld.Code.Text, OldPath, NewPath, , , 1)
End If
If InStr(oFld.Code.Text, "[") <> 0 Then
OldFile = Split(oFld.Code.Text, "[")(1)
OldFile = Split(OldFile, "]")(0)
oFld.Code.Text = Replace(oFld.Code.Text, OldFile, NewFile, , , 1)
End If
oFld.Code.Cut
oFld.Code.Paste
oFld.Locked = False
oFld.Update
oFld.Locked = True
End If
Next
Next
MsgBox ("Completed Link Update")
End Sub