save existing file name, different format.

[SteveDB1]

Hi all.
I've modified an existing macro, and want to do a save as.
1- I need to keep the same name.
I.e., if the existing file name is John, I'll be chaning it from
John.xls, or John.xlsm, to John.xlsx.

2- I'll be saving the file with the xlsx format.

Below is the code I'm using, after calling to another macro.
-------------------------------------------------------------------------
Sub SampleTest1()

Dim sFileNm As String

Application.Run "PERSONAL.XLSB!CompactAllSheets"
ChDir "S:\Assignments - Final\Truckee River Claims\BloatReducedFiles"
sFileNm = Application.InputBox(Prompt:="What's the name of the file?",
Type:=2)

ActiveWorkbook.SaveAs Filename:= _
"S:\Assignments - Final\Truckee River Claims\BloatReducedFiles\" &
sFileNm & ".xlsx", _
FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
End Sub

 

[Joel]

Sub test()
Folder = ActiveWorkbook.Path
FName = ActiveWorkbook.Name
'remove extension
FName = Left(FName, InStr(FName, ".") - 1)
SaveName = Folder & "\" & FName & ".xlsx"
ActiveWorkbook.SaveAs Filename:= _
FName, _
FileFormat:=xlOpenXMLWorkbook

End Sub

 

[JLGWhiz]

Joel, are you sure about that last line of code? Should that be FName or
SaveName?

 

[SteveDB1]

Hi,
Thank you for the response.

Ok, so that I understand this clearly.....

The "ActiveWorkbook.Path" is the path for the existing file to saved in,
correct?
I.e., if the original file is saved in C:\Steve\File.xls, then the new file
will be saved in the same directory, but the new extension--
C:\Steve\File.xlsx
Correct?

Then, FName is being assigned to the existing file name- WorkBook.Name,
which looks for the existing name in the string?

Then, SaveName is the location to which the file is to be saved, correct?

What would I modifiy SaveName to if I want to go one folder deeper?

I.e., presently the folder would just be: C:\Steve
But I now wish to place the new files with the xlsx extension into
C:\Steve\New\*.xlsx

It seems to me that I'd need to have something like:
SaveName = Folder & "\New\" & FName & ".xlsx"
with ActiveWorkbook.SaveAs Filename:= SaveName,_ FileFormat:=xlOpenXMLWorkbook

Is this correct?

Again... thank you for your helps!

 

[SteveDB1]

Bingo,
Both of you are correct!
Thank you both VERY MUCH!!!!!!!
Best.