VBA save file with path and name defined by cell value

[sasar]

Hi all,

I searched around but could not find an answer to my question.
Looking for a VBA that will save my file with a file name defined by cell A1 to a path defined by specific network path and folder name in cell A2.

Tried several versions of codes I found online, but cannot find all the conditions in one place. When I try to merge all the conditions, I keep getting errors.

Could you please help?

Private Sub filename_cellvalue()
Dim Path1 As String
Dim Path2 As String
Dim filename As String
Path1 = "X:\test1\test2\test3"
Path2 = Range("A2")
filename = Range("A1")
ActiveWorkbook.SaveAs filename:=Path1 & Path2 & filename & ".xls", FileFormat:=xlNormal
End Sub


Thanks,
Alex

 

[Joe4]

Welcome to the Board!

A big problem: never choose a reserved word for your variables! That causes confusion, ambiguity, unexpected results, and errors.
You should NOT name your variable "filename".

ActiveWorkbook.SaveAs [COLOR=#ff0000]filename[/COLOR]:=Path1 & Path2 & [COLOR=#ff0000]filename[/COLOR] & ".xls", FileFormat:=xlNormal

Also, what exactly is in cell A2?
Note that you are missing the last slash after "Path1". And if there is not one in Range("A2"), you will need to add that in too.

So your final code may look something like this:

Private Sub filename_cellvalue()
    
    Dim Path1 As String
    Dim Path2 As String
    Dim myfilename As String

    Path1 = "X:\test1\test2\test3"
    Path2 = Range("A2")
    myfilename = Range("A1")
    
    ActiveWorkbook.SaveAs filename:=Path1 & "/" & Path2 & "/" & myfilename & ".xls", FileFormat:=xlNormal
    
End Sub

 

[sasar]

Thanks for your help, really appreciate it.

Your amended code is now saving the file with a file name in cell A2 (which is the month name I want to use for the folder name). and the actual file name which is in cell A1 is missing.

 

[Joe4]

Sorry, I actually have the slashes pointing the wrong way.

Here is also a little trick you can use to make sure that you are building the correct path and filename. Store the whole thing in a string and return it via Message Box.
Once you have it working correctly, you can either comment out the Message Box line or delete it.

Private Sub filename_cellvalue()
    
    Dim Path1 As String
    Dim Path2 As String
    Dim myfilename As String
    Dim fpathname As String

    Path1 = "X:\test1\test2\test3"
    Path2 = Range("A2")
    myfilename = Range("A1")
    fpathname = Path1 & "\" & Path2 & "\" & myfilename & ".xls"
    
    MsgBox "You are trying to save the file to:" & vbCrLf & fpathname
    ActiveWorkbook.SaveAs filename:=fpathname, FileFormat:=xlNormal
    
End Sub

 

[sasar]

Seem to be very close, the message now gives the correct location, however eventually I get the run-time-error. Could it be because the folder to which I am trying to save does not exist yet? I want the code to create new folder if it does not yet exist.

 

[Joe4]

Could it be because the folder to which I am trying to save does not exist yet?

Absolutely. You cannot say a file to a folder that does not exist.
The SaveAs command does not create new folders.

You can check for its existence first, and create the folder if you need to.
See this: https://www.mrexcel.com/forum/excel...ermine-if-directory-exists-if-not-create.html

 

[sasar]

Thanks for sharing the additional code.
I have it incorporated now, and new folder is created as needed, however file is saved in the parent folder.

Any idea what am I doing wrong?

Private Sub filename_cellvalue()
Dim Path1 As String
Dim Path2 As String
Dim filename As String
Path1 = "X:\test1\test2\test3\"
Path2 = Range("A2")
filename = Range("A1")
If Dir("X:\test1\test2\test3\" & Path2, vbDirectory) = "" Then
MkDir Path:="X:\test1\test2\test3\" & Path2
MsgBox "Done"
Else
MsgBox "found it"
End If
ActiveWorkbook.SaveAs filename:=Path1 & Path2 & filename & ".xlsm", FileFormat:=xlNormal
End Sub

 

[Joe4]

What is in Range("A2")?

It looks like you removed the slash after the Path2 in your filename again.
If you use the MsgBox trick I showed you previously, you should be able to see this quite easily.

 

[sasar]

I keep on editing the code and looks like each time trying to fix one thing I break something new.
Here is the entire code as I have it in excel. When I run it, the folder gets correctly created based on the cell A2, however file name does not include value of cell A1 and then in the end I also get an error.
The message from the code which is meant to show the location of where the file will be saved is just blank.

Private Sub filename_cellvalue()
Dim Path1 As String
Dim Path2 As String
Dim filename As String
Path1 = "\\ant\dept-eu\CSCAN\4-ProcessTeam\3-Business-Analyst\Invoices"
Path2 = Range("A2")
filename = Range("A1")
If Dir("\\ant\dept-eu\CSCAN\4-ProcessTeam\3-Business-Analyst\Invoices" & Path2, vbDirectory) = "" Then
MkDir Path:="\\ant\dept-eu\CSCAN\4-ProcessTeam\3-Business-Analyst\Invoices" & Path2
MsgBox "Done"
Else
MsgBox "found it"
End If
MsgBox "You are trying to save the file to:" & vbCrLf & fpathname
ActiveWorkbook.SaveAs filename:=Path1 & Path2 & "" & myfilename & ".xls"
End Sub

 

[Joe4]

The message from the code which is meant to show the location of where the file will be saved is just blank.

Because you didn't include the actual calculation of fpathname.
And you are still using filename as a variable.
And myfilename isn't calculated anywhere in your code.

It looks like you partially added the things I gave you (so everything is "half-updated").

Try this:

Private Sub filename_cellvalue()

    Dim Path1 As String
    Dim Path2 As String
    Dim myfilename As String
    Dim fpathname As String

    Path1 = "\\ant\dept-eu\CSCAN\4-ProcessTeam\3-Business-Analyst\Invoices"
    Path2 = Range("A2")
    myfilename = Range("A1")
    fpathname = Path1 & "\" & Path2 & "\" & myfilename & ".xls"
        
    If Dir("\\ant\dept-eu\CSCAN\4-ProcessTeam\3-Business-Analyst\Invoices\" & Path2, vbDirectory) = "" Then
        MkDir Path:="\\ant\dept-eu\CSCAN\4-ProcessTeam\3-Business-Analyst\Invoices\" & Path2
        MsgBox "Done"
    Else
        MsgBox "found it"
    End If
    
    MsgBox "You are trying to save the file to:" & vbCrLf & fpathname
    ActiveWorkbook.SaveAs filename:=fpathname

End Sub