CODEWITHSUNDEEP
excelvbams-word
jarnold231
jarnold231

Reputation: 23

Excel VBA ChDrive/ChDir not changing file save location of word document

I've been trying to get an Excel VBA script to open an existing word document (a master template) and then automatically save it as a .docx in another file location. The variables for the filename and file locations are saved in the Excel document (x y z) which are created via vlookups.

While the script is opening and saving the document it always saves in the default file location (My Documents) as opposed to the specified location (folder3).

My understanding is that the ChDrive and ChDir functions are needed to adjust this, but I haven't been able to get it to work.

It seems quite similar to this question / answer Excel VBA Saving File in Designated Location but I do not understand how to integrate that into this code or if it is actually the same issue.

Any help greatly appreciated.

Sub OpenDocSaveforUpdate()

Dim x As Range
Dim y As Range
Dim z As Range
Dim folder As String
Dim folder2 As String

Set wdApp = CreateObject("word.application")
wdApp.Visible = True
Set x = Sheets("Sheet1").Range("B31")
Set y = Sheets("Sheet1").Range("B32")
Set z = Sheets("Sheet1").Range("B33")

folder = "T:\Archive"
folder2 = x & y & z & ".docx"
folder3 = folder & x & y
Filename = (folder & folder2)

Documents.Open (Filename)

ChDrive "T"
ChDir (folder3)
wdApp.ActiveDocument.SaveAs (z & Format(Now, "yyyymmdd") & ".docx")

Set wdapp = Nothing

End Sub

Upvotes: 0

Views: 1143

Answers (1)

jarnold231
jarnold231

Reputation: 23

Thanks to both Jeeped and Comintern.

The much simpler solution to the problem is just to give the fullpath to SaveAs by using the folder names separated with a "\" in the filename.

So much simpler

Upvotes: 1

Related Questions