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"

 

[2Took]

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

Yes, I did commented that line out, before replying back to you. I don't just blindly copy & paste the code.
There can't be both of those lines.
But it must be considering that dot as part of the BaseName, and not part of the extension -- and so it keeps it.
How do we get it out of there?

 

[NateSC]

It doesn't add the dot on my machine, but you can use:

MyName = Left(MyName, Len(MyName) - 1)

 

[2Took]

It doesn't add the dot on my machine, but you can use:

MyName = Left(MyName, Len(MyName) - 1)

a. I'd venture a guess, your machine is set not to display file extensions? Whereas, mine does. Could that be the reason?
b. How do you combine the two lines, one that you propose now with the current code I'm running:

MyName = .GetBaseName(ActiveWorkbook.Name)

 

[NateSC]

I am showing extensions, but sometimes different versions of Excel behave slightly differently. No worries.

To fix the problem, use both statements. The first will give you the name with the "." and the second will strip the "." (more accurately, it will strip the last character from MyName no matter what it is).

If this doesn't work, we need to know where the "." is coming from. So only if this code fails, Add a Watch to the MyName variable so you can see how it changes as it steps through the code.

MyName = .GetBaseName(ActiveWorkbook.Name)
MyName = Left(MyName, Len(MyName) - 1)

 

[2Took]

I am showing extensions, but sometimes different versions of Excel behave slightly differently. No worries.

To fix the problem, use both statements. The first will give you the name with the "." and the second will strip the "." (more accurately, it will strip the last character from MyName no matter what it is).

If this doesn't work, we need to know where the "." is coming from. So only if this code fails, Add a Watch to the MyName variable so you can see how it changes as it steps through the code.

MyName = .GetBaseName(ActiveWorkbook.Name)
MyName = Left(MyName, Len(MyName) - 1)

Would be nice to condition for the second line to run only if last character of the BaseName is a "."
What's the syntax for that?

 

[NateSC]

Good idea. Just use the Right function.

Right function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

 

[2Took]

Good idea. Just use the Right function.

Right function (Visual Basic for Applications)

Office VBA reference topic

docs.microsoft.com

Thanks! Came up with this, will try it out during the coming week:

MyName = .GetBaseName(ActiveWorkbook.Name)

Dim AnyString, MyStr
AnyString = MyName
MyStr = Right(AnyString, 1)
If MyStr = "." Then
MyName = Left(MyName, Len(MyName) - 1)
End If
'these lines go before "End With"

 

[2Took]

Thanks! Came up with this, will try it out during the coming week:

MyName = .GetBaseName(ActiveWorkbook.Name)

Dim AnyString, MyStr
AnyString = MyName
MyStr = Right(AnyString, 1)
If MyStr = "." Then
MyName = Left(MyName, Len(MyName) - 1)
End If
'these lines go before "End With"

That worked.

Now I'm trying to say if cell H32 is empty, then append cell H33 to the file name, but get this error on both "MyRng = ActiveWorkbook.Sheets..." lines...

What's the fix?

Dim MyRng 'xx3
If ActiveWorkbook.Sheets("blah").Range("H32") = "" Then
MyRng = ActiveWorkbook.Sheets("blah").Range("H33)
End If
MyRng = ActiveWorkbook.Sheet("blah").Range("H32)

ActiveWorkbook.SaveAs Filename:=csPath & MyName & " - " & MyRng & strExt 'xx3

 

[NateSC]

That worked.

Now I'm trying to say if cell H32 is empty, then append cell H33 to the file name, but get this error on both "MyRng = ActiveWorkbook.Sheets..." lines...

What's the fix?

View attachment 70573

Dim MyRng 'xx3
If ActiveWorkbook.Sheets("blah").Range("H32") = "" Then
MyRng = ActiveWorkbook.Sheets("blah").Range("H33)
End If
MyRng = ActiveWorkbook.Sheet("blah").Range("H32)

ActiveWorkbook.SaveAs Filename:=csPath & MyName & " - " & MyRng & strExt 'xx3

Sorry for the delay.

Try adding .value after the ranges:
ActiveWorkbook.Sheets("blah").Range("H32").Value
ActiveWorkbook.Sheets("blah").Range("H33).Value

You are testing against a range instead of the value in that range.