VBA to open Word document and refresh links

Question

I am hoping to find a solution (be it VBA or not) to my problem. I have a Word document with linked Excel tables in it. All links work correctly, but I have to manually open the Word document in order for it to refresh with the Excel data. The reason I am using Word instead of Excel is due to the large amounts of text.

Is there a way that I could program some sort of code that would go through all Word documents in a folder, open each document, refresh all links, save document, and move onto the next one?

Here's what I have so far

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String

MyFolder = "LOCATION"
MyFile = Dir(MyFolder & "\*.docx")

Do While MyFile <> ""
    Documents.Open Filename:=MyFolder & "\" & MyFile
    MyFile = Dir
Loop

End Sub

UPDATE

Ok I was able to make the open files command work! Now I am attempting to make it overwrite/save the file and close it.

Error Message: Object Required

Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
Dim objWord As Object

Set objWord = CreateObject("Word.Application")
objWord.Visible = True
MyFolder = "LOCATION"
MyFile = Dir(MyFolder & "\*.docx")
Do While MyFile <> ""
objWord.Documents.Open Filename:=MyFolder & "\" & MyFile

Application.DisplayAlerts = False
ActiveDocument.SaveAs Filename:=MyFile
Application.DisplayAlerts = True

MyFile = Dir
Loop
End Sub

Yes there is a way. Why don't you give it a shot a post what you've tried. We'll help with any errors, but we won't do it from scratch. — Mr. Mascaro
you need to tell us what exact error you are receiving and on which line. — Mr. Mascaro
@jbarker2160, I am being told I need to create an object. I also don't think I have the "save" code correct. — amy6409

L42 L42 · Accepted Answer · 2014-11-04T22:30:04

You can try any of below:

Dim objDoc As Object
Do While Myfile <> ""
    Set objDoc = objWord.Documents.Open(Filename:=MyFolder & "\" & MyFile)
    objDoc.Save '~~> Save
    objDoc.Close '~~> Close
    Myfile = Dir
Loop

Or you can just use Close Method like this.

objDoc.Close True '~~> close with SaveChanges argument set to True

VBA to open Word document and refresh links

1 Answers