CODEWITHSUNDEEP
vbaexcel
S.MART
S.MART

Reputation: 29

How to convert from xlsm to xlsx keeping the same name

how can I change the format from xlsm to xlsx. I need a script that will save a copy of the xlsm file with xlsx extension, but doesn't ask user to input file name, it needs to keep the original name, the only thing the user needs to do is select where to save the file. 

Sub changeext()

Dim s_as As String

s_as = ThisWorkbook.FullName

s_as = Left(s_as, InStrRev(s_as, ".") - 1) & ".xlsx"

ThisWorkbook.SaveAs FileName:=s_as

Application.DisplayAlerts = False

End Sub

Upvotes: 1

Views: 5557

Answers (2)

Jeremy
Jeremy

Reputation: 1337

You need to create FolderPicker Function:

Sub changeext()
Dim objFolder As Object, objFSO As Object

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(ChooseFolder)

ThisWorkbook.SaveCopyAs Filename:=objFolder & "\" & Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4) & "xlsx"

End Sub

Function ChooseFolder() As String
Dim fldr As FileDialog
Dim sItem As String

Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
With fldr
    .Title = "Select a Folder to save down the copy of this workbook"
    .AllowMultiSelect = False
    .InitialFileName = strPath
    If .Show <> -1 Then GoTo NextCode
    sItem = .SelectedItems(1)
End With

NextCode:
    ChooseFolder = sItem
    Set fldr = Nothing
End Function

Upvotes: 1

GMalc
GMalc

Reputation: 2628

I think you only want to add a .xlxs copy not write over your macro enabled workbook. Try the below line of code, it should work, put your workbook name in "put Workbook name here", the file left open will be the .xlsx file.

Application.ScreenUpdating = False
Application.DisplayAlerts = False

ActiveWorkbook.SaveAs Filename:=ActiveWorkbook.Path & "\" & "Put Workbook Name here" & ".xlsx", FileFormat:=51, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Application.DisplayAlerts = True
Application.ScreenUpdating = True

Upvotes: 2

Related Questions