One Macro to "Save As" Using Current Workbook File Name With Changes

[sdbroyles]

Hi All,

I use one maco to process 6 different reports every morning. Each report has a similar yet unique file name. As an example, report 1 has a file name of "2247 Production Schedule YYMMDD.xls", report 2 has a file name of "7621 Production Schedule YYMMDD.xls" with the number in the file name being the variable and the date format being the current date. Using the same macro, I'm looking to save report 1 in the following format, "2247 Production Schedule YYMMDD_fmtd.xlsb, and reort 2 as "7621 Production Schedule YYMMDD_fmtd.xlsb". Thank you for any help.

 

[sdbroyles]

I've got it partially figured out. The code below saves to the original file location as I want it to and renames the file as I want it to, with one exception. The filename ends in ".xls_fmtd.xlsb". How do I get rid of the ".xls"?

Dim FN As Variant

FN = ActiveWorkbook.FullName

ActiveWorkbook.SaveAs Filename:=Path & FN & "_fmtd" & ".xlsb"

 

[MARK858]

Why are you using FullName when you already have the path?

ActiveWorkbook.SaveAs Filename:=Path & FN & "_fmtd" & ".xlsb"

If you have the path then you should only need ActiveWorkbook.Name i.e.

Dim FN As String

FN = ActiveWorkbook.Name

ActiveWorkbook.SaveAs Filename:=Path & "/" & Left(FN, InStrRev(FN, ".") - 1) & "_fmtd" & ".xlsb"

If you don't have the path then obviously use FullName and remove the & "/"

 

[sdbroyles]

Thank you very much for your help Mark, as I am just learning VBA and obviously have a long way to go. What I'm trying to do is save a file such as "2247 Production Schedule 220208.xls" to it's current path and change it's name to "2247 Production Schedule 220208_fmtd.xlsb". Using my original code saved the file to it's current path, but renamed it as "2247 Production Schedule 220208.xls_fmtd.xlsb. I need to get rid of the ".xls". Using the code you modified, I'm getting the error shown below.

Dim FN As String

FN = ActiveWorkbook.Name

ActiveWorkbook.SaveAs Filename:=Path & "/" & Left(FN, InStrRev(FN, ".") - 1) & "_fmtd" & ".xlsb"

 

[MARK858]

What exactly is the string in Path?

You also need to add

 ,FileFormat:=53

at the end of the line as you are changing the format

ActiveWorkbook.SaveAs Filename:=Path & "/" & Left(FN, InStrRev(FN, ".") - 1) & "_fmtd" & ".xlsb", FileFormat:=51

Separator in my code should have been "\"

or keep the FullPath and remove the & "\" and test (you still need the fileformat part)

Edit: Actually you are on a MAC and so your separator probably isn't / or \, check what it is or use Application.FileSeparator instead

 

[sdbroyles]

Thank you for your help. I was able to get accomplished what I needed using the code below. I also do not have a MAC.

Dim FN As String

FN = ActiveWorkbook.FullName

FN = ActiveWorkbook.Path & "\" & Replace(ActiveWorkbook.Name, ".xls", "")

ActiveWorkbook.SaveAs Filename:=FN & "_fmtd", FileFormat:=50

 

[MARK858]

I also do not have a MAC.

Then you have changed your platform under your profile, yes? because I am sure that it said MAC rather than Windows earlier.

Replace is good if you are only removing it from one format but it is not generic so you would need to amend the code each time if for instance you had a file that was an xlsx or an xlsm.

Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)

is generic

 

[sdbroyles]

Then you have changed your platform under your profile, yes? because I am sure that it said MAC rather than Windows earlier.

Replace is good if you are only removing it from one format but it is not generic so you would need to amend the code each time if for instance you had a file that was an xlsx or an xlsm.

Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)

is generic

Nope, I haven't changed anything on my profile and thanks again for all your help.

 

[MARK858]

You're welcome