CODEWITHSUNDEEP
vbaexcel
bdkong
bdkong

Reputation: 191

How can I change the file path of a Workbook in VBA?

So I have a Workbook that is being saved. It is being saved under a name from two cells in a different workbook. As of right now, it saves automatically to the documents folder on my computer. I would like to change this to a folder of my choosing. I'm guessing this should be pretty straight-forward. Here's my code so far:

    Dim name As String

    name = Workbooks("Transfer Template").Sheets("Sheet1").Range("B1").Value & "_" & Workbooks("Transfer Template").Sheets("Sheet1").Range("B4").Value
    Windows("Protected_Form.xls").Activate
    ActiveWorkbook.SaveAs Filename:=name

Thanks!

Upvotes: 0

Views: 9011

Answers (2)

Nigel Heffernan
Nigel Heffernan

Reputation: 4726

Your problem is that the default folder isn't where you want to save this file. You can change this setting from the main menu, or in code:

Application.DefaultFilePath =  "H:\Projects\"

However... Your system security settings or 'roaming profile' might not let you do this, and the command will fail silently. Worse, you might do this, and succeed on your own PC, and pass it onto a user who has a differently-configured workstation.

It's best to specify the path explicitly:

Dim wbk As Excel.Workbook
Dim strName As String
Dim strPath As String

Set wbk = Application.Workbooks("Protected_Form.xls")

With Application.Workbooks("Transfer Template").Sheets("Sheet1")
    strName = .Range("B1").Value & "_" & .Range("B4").Value
End With

strPath = "H:\Projects\"

wbk.SaveAs Filename:=strPath & strName

Always check that you have the '\' backslash on your folder path: it's a very common error.

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166196

Const MY_PATH As String = "C:\Temp\"
Dim name As String

With Workbooks("Transfer Template").Sheets("Sheet1")
    name = .Range("B1").Value & "_" & .Range("B4").Value
End With    

Workbooks("Protected_Form.xls").SaveAs Filename:= MY_PATH & name

Upvotes: 2

Related Questions