I am struggling to convert my XLSM to XLSX File..it was before transformation to PDF, and I tried to change a bit but didnt succeed.

I wanna to have the same name as the workbook has, but just in XLSX format.

Sub xlsmtoxlsx()
Dim PathFile As String
Dim PathArray() As String
Dim PathPDF As String
   
'Get file path
PathFile = Application.ThisWorkbook.FullName

'Split file path in path and file ending
PathArray() = Split(PathFile, ".")

'Creat file path with ".pdf" ending
PathPDF = PathArray(0) & ".xlsx"
    
'PDF File is saved in directory of workbook
ActiveSheet.ExportAsFixedFormat Type:=xlTypeXlsx, Filename:= _
    PathPDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
    
'Closes Workbook after generating PDF
ActiveWorkbook.Saved = True
Application.Quit

CodePudding user response：

Backup as XLSX

Option Explicit

Sub BackupAsXLSX()
    
    ' Create a reference to the source workbook.
    Dim swb As Workbook: Set swb = ThisWorkbook ' workbook containing this code
    
    ' Determine the destination file path ('FullName').
    Dim sFilePath As String: sFilePath = swb.FullName
    Dim DotPosition As Long: DotPosition = InStrRev(sFilePath, ".")
    Dim dFilePath As String: dFilePath = Left(sFilePath, DotPosition) & "xlsx"
    
    Application.ScreenUpdating = False
    
    ' Copy all sheets to a new (destination) workbook.
    swb.Sheets.Copy
    Dim dwb As Workbook: Set dwb = ActiveWorkbook
    ' Save and close the destination workbook.
    Application.DisplayAlerts = False ' overwrite without confirmation
    dwb.SaveAs Filename:=dFilePath, FileFormat:=xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    dwb.Close SaveChanges:=False
    
    Application.ScreenUpdating = True
    
    ' Inform.
    MsgBox "XLSX backup created.", vbInformation
    
    ' Note that the source workbook hasn't been modified in any way.
    
End Sub