Save as xlsx to different location with current file name and append date

[hinesgg]

I have templates stored in a single location and I want the output stored in a different location with the same file name and date appended.

The code below works, but in it I specify the file name (in this example: Forecast HM SUPPLIERNAME). I would prefer for the file name to use the source file name and then just append the date.

ActiveWorkbook.SaveAs Filename:="\\aurnt503\Xcc$\11 Planning\New Reports\Forecast HM SUPPLIERNAME" & Format(Now, " mm-dd-yyyy") & ".xlsx", FileFormat:=51

Is this possible?

 

[JoeMo]

Maybe:

Dim fNam as string
fNam = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xls") - 1)
ActiveWorkbook.SaveAs Filename:="\\aurnt503\Xcc$\11 Planning\New Reports\" & fNam & Format(Now, " mm-dd-yyyy") & ".xlsx", FileFormat:=51

 

[hinesgg]

That's saving the file with the date as the name, but not retaining the original file name too.

 

[hinesgg]

The original file is an xlsm, not an xls, if that makes a difference. Sorry, I don't know much at all about VBA code.

 

[hinesgg]

I got this to work properly by modifying the code to recognize the original file's xlsm format. Thanks for your help.

Dim fNam as stringfNam = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xlsm") - 1)ActiveWorkbook.SaveAs Filename:="\\aurnt503\Xcc$\11 Planning\New Reports\" & fNam & Format(Now, " mm-dd-yyyy") & ".xlsx", FileFormat:=51[\Code]</pre>

 

[JoeMo]

I got this to work properly by modifying the code to recognize the original file's xlsm format. Thanks for your help.

Dim fNam as stringfNam = Left(ActiveWorkbook.Name, InStr(1, ActiveWorkbook.Name, ".xlsm") - 1)ActiveWorkbook.SaveAs Filename:="\\aurnt503\Xcc$\11 Planning\New Reports\" & fNam & Format(Now, " mm-dd-yyyy") & ".xlsx", FileFormat:=51[\Code][/QUOTE]
Shouldn't make any difference whether you use .xls or .xlsm in the InStr function. That simply locates the .xls in the string. I prefer .xls since that covers .xlsx and .xlsm and .xlsb type files.