VBA Save As New Name

[Gian624]

Hi Everyone,

I have a macro I run that opens the files in a folder, runs a macro to update the file and saves it (Save As) to another folder. What I want to do now is have the last 10 characters on the file name removed and replaced with 10 characters on the spreadsheet.

The last part of the code says:

ActiveWorkbook.SaveAs Filename:=Location & myFile & ".xlsm"
wb.Close

Location is the folder I am saving to and myFile is the name of the file. Id like to automatically rename myFile with the last 10 characters off and replaced with the contents in range("B13").

Thanks,
Matt

 

[JoeMo]

Untested and no error handling in the event that my file contains less than 10 characters.

ActiveWorkbook.SaveAs Filename:=Location & Left(myFile,Len(myFile)-10 & [B13] & ".xlsm"

 

[Gian624]

Thanks Joe,

I changed the -10 to -15 because I forgot to take out the .xlsm. Also, I Dim'ed another string for B13 and replaced it with what was in the range on the sheet. Other then that it worked great.

Thanks,
Matt

 

[JoeMo]

Thanks Joe,

I changed the -10 to -15 because I forgot to take out the .xlsm. Also, I Dim'ed another string for B13 and replaced it with what was in the range on the sheet. Other then that it worked great.

Thanks,
Matt

You are welcome - thanks for the reply.