save existing file name, different format.
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.
|