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
---------------------------------------------------------------------

I'd prefer to bypass the inputbox, and just save whatever the existing file
name is-- I'm modifying the file, and saving the new version one folder
deeper into my directory structure.

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

"SteveDB1" wrote:

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
---------------------------------------------------------------------

I'd prefer to bypass the inputbox, and just save whatever the existing file
name is-- I'm modifying the file, and saving the new version one folder
deeper into my directory structure.

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

"Joel" wrote:

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

"SteveDB1" wrote:

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
---------------------------------------------------------------------

I'd prefer to bypass the inputbox, and just save whatever the existing file
name is-- I'm modifying the file, and saving the new version one folder
deeper into my directory structure.