Vba - remove filetype (.xlsm) when using 'activeworkbook.saveas'

[moni_tm]

Good Morning,

I have a macro which takes an existing workbook, makes changes to said workbook and saves a copy of the workbook adding to the existing filename.

The code I currently have takes the existing workbook name including the filetype and then adds the following on the end " - Owner.xlsm"

Example:

Original Filename - "Test.xlsm"
Current New Filename - "Test.xlsm - Owner.xlsm"

Required New Filename "Test - Owner.xlsm

Current Code:

ActiveWorkbook.SaveAs FileName:=ActiveWorkbook.FullName & " - OWNER.xlsm"

As always, any help, greatly appreciated.

Tom

 

[RoryA]

You could use Instrrev to find the last full stop like this:

Dim sFile as String
sFile = ActiveWorkbook.FullName
sfile = Left$(sFile, Instrrev(sfile, ".") - 1) & " - OWNER.xlsm"
ActiveWorkbook.SaveAs FileName:=sFile, fileformat:=xlOpenXMLWorkbookMacroEnabled

Note that you should include the file type too.

 

[moni_tm]

Yep Magic, thanks.

Tom