Save active workbook with existing name, but into a specific folder

[2Took]

Can's find syntax...
Looking to save active workbook with already existing name, but into a specific folder
e.g. if active workbook name is ABC123, I'd want it to be saved with that name, but into a specific folder.

And if I wanted to add a phrase to existing name
e.g. Make name "ABC123 - today's date"
or
"ABC123 - contents of cell A1 on Sheet1 of the same book"

 

[NateSC]

The file name and path are all string manipulation so there are tons of options.

Create your filename via:
Hard coded workbook name to get a value
myFileName = "ABC123" & " - " & Workbooks("Book1.xlsm").Worksheets("Sheet1").Range("A1").Value & ".xlsm"

Or using Activeworkbook to get a value
myFileName = "ABC123" & " - " & ActiveWorkbook.Worksheets("Sheet1").Range("A1").Value & ".xlsm"

Or with a date
myFileName = "ABC123" & " - " & Format(Now(), "mm-dd-yy") & ".xlsm"

You can add a path to save to a specific folder
myPath = "C:\Documents\"
myFileName = myPath & myFileName


Save using
ActiveWorkbook.SaveAs myFileName

Or as a macro free workbook with
myFileName = "ABC123" & " - other stuff" & ".xlsx"
ActiveWorkbook.SaveAs myFileName, xlOpenXMLStrictWorkbook

You can also add a password, make read only, etc with the SaveAs method:

Workbook.SaveAs method (Excel)

Office VBA reference topic

docs.microsoft.com

 

[2Took]

Thank you for the awesome answer, @*NateSC.

I tried to append cell value to the end of the file name and encountered a problem where an original .xlsx file was not allowed to be saved with an .xlsm extension.
Conversely, I will be using the Macro to save .xlsm files, with code in them, and will have to deal with dialog boxes popping up, if I want to keep appending at end of file name.

How do I keep the ability to append the file name at the end, and have VBA automatically pick the same extension after that, as whatever the original file was?

 

[NateSC]

I stole this from another thread:

With CreateObject("Scripting.FileSystemObject")
strExt = .GetExtensionName(ActiveWorkbook.FullName)
End With

However, the strExt will not have the "." before the extension so you will have to add it yourself.
myFileName = "ABC123" & [OTHER STUFF] & "." & strExt

 

[2Took]

I stole this from another thread:

With CreateObject("Scripting.FileSystemObject")
strExt = .GetExtensionName(ActiveWorkbook.FullName)
End With

However, the strExt will not have the "." before the extension so you will have to add it yourself.
myFileName = "ABC123" & [OTHER STUFF] & "." & strExt

One variation of that:

strExt = "." & .GetExtensionName(ActiveWorkbook.FullName)

Solution however still has a caveat, which is that the file extension appears twice in the file name, e.g. ".xlsx - cell value.xlsx"

 

[NateSC]

"Solution however still has a caveat, which is that the file extension appears twice in the file name, e.g. ".xlsx - cell value.xlsx" "

I can't completely follow the problem without seeing all the pieces involved. This is string manipulation so you can build it or parse it as needed. Try using Index, Left, Right, etc. functions to only keep the parts of the string you want. The strExt is just trying to figure out the file type of the active workbook.

If you want to post the string pieces you have and what the desired outcome would be, I can try to help from there.

-N

 

[2Took]

"Solution however still has a caveat, which is that the file extension appears twice in the file name, e.g. ".xlsx - cell value.xlsx" "

I can't completely follow the problem without seeing all the pieces involved. This is string manipulation so you can build it or parse it as needed. Try using Index, Left, Right, etc. functions to only keep the parts of the string you want. The strExt is just trying to figure out the file type of the active workbook.

If you want to post the string pieces you have and what the desired outcome would be, I can try to help from there.

-N

so, here's the code I'm running (stripped of private details):

Sub SaveTo_DEMO()

With CreateObject("Scripting.FileSystemObject")
'strExt = .GetExtensionName(ActiveWorkbook.FullName) 'xx1
strExt = "." & .GetExtensionName(ActiveWorkbook.FullName) 'xx2
End With

Const csPath As String = "\\ABC\target folder\"
MyName = ActiveWorkbook.Name

'ActiveWorkbook.SaveAs Filename:=csPath & MyName & " - " & Sheets("blah").Range("H32") & "." & strExt 'xx1
ActiveWorkbook.SaveAs Filename:=csPath & MyName & " - " & Sheets("blah").Range("H32") & strExt 'xx2

End Sub

It results in the file extension appearing twice in the file name, e.g. "file name.xlsx - cell value.xlsx"
Objective is for the file extension to be appearing only once and only at the end of the resulting file name.

Thank you for your contribution so far, and if you can solve this one -- even better.

 

[NateSC]

Try this:

With CreateObject("Scripting.FileSystemObject")
    strExt = "." & .GetExtensionName(ActiveWorkbook.FullName)
    MyName = .GetBaseName(ActiveWorkbook.Name)
End With

 

[2Took]

Try this:

With CreateObject("Scripting.FileSystemObject")
    strExt = "." & .GetExtensionName(ActiveWorkbook.FullName)
    MyName = .GetBaseName(ActiveWorkbook.Name)
End With

Almost there... This still keeps the dot after the BaseName, so file name saves as "File Name. - H32 cell content.xlsx"
Desired not to have that "." after the "File Name"

 

[NateSC]

Did you comment out the MyName= ActiveWorkbook.Name line in your code? I think it is still there and is overriding the line with the fso.

Sub SaveTo_DEMO()

With CreateObject("Scripting.FileSystemObject")
'strExt = .GetExtensionName(ActiveWorkbook.FullName) 'xx1
    strExt = "." & .GetExtensionName(ActiveWorkbook.FullName) 'xx2
    MyName = .GetBaseName(ActiveWorkbook.Name)
End With

Const csPath As String = "\\ABC\target folder\"
'----->!!!!! Comment out the line below this or delete it!!!<------
'----->MyName = ActiveWorkbook.Name                         <------

'ActiveWorkbook.SaveAs Filename:=csPath & MyName & " - " & Sheets("blah").Range("H32") & "." & strExt 'xx1
ActiveWorkbook.SaveAs Filename:=csPath & MyName & " - " & Sheets("blah").Range("H32") & strExt 'xx2

End Sub