How to modify these VBA codes to save excel sheet in excel format (.xlsx)?

Question

I have added a button in excel sheet and now when I click that button it saves the sheet at specific path with specific name but in pdf format.

I want to save this sheet simply in excel format (.xlsx). so

Sub PDFActiveSheet2()

Dim ws As Worksheet
Dim strFile As String

On Error GoTo errHandler

strFile = "m:formats" & Range("H8")
Set ws = ActiveSheet

ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

MsgBox "file has been created."

exitHandler:
        Exit Sub
errHandler:
        MsgBox "Could not create the file"
        Resume exitHandler

End Sub

What do I need to change?

Prasanna · Answer

Change your following code from

ws.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=strFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=False

To the following code

ActiveWorkbook.SaveAs Filename:="C:Users46506090DesktopBook1.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

Change the path (C:Users46506090DesktopBook1.xlsm) to what you would want and see if it works.

CreateBackup:=False

This is optional

Edit:

The complete code

Option Explicit

Sub Button1_Click()
'Sub PDFActiveSheet2()

Dim ws As Worksheet
Dim strFile As String

On Error GoTo errHandler

strFile = "m:formats" & Range("H8")
Set ws = ActiveSheet

ActiveWorkbook.SaveAs Filename:="C:Users46506090DesktopBook1.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'Comment: Replace "C:Users46506090DesktopBook1.xlsm" to your desired filename
MsgBox "file has been created."

exitHandler:
        Exit Sub
errHandler:
        MsgBox "Could not create the file"
        Resume exitHandler

End Sub

How to modify these VBA codes to save excel sheet in excel format (.xlsx)?

One Answer

Edit:

Add your own answers!

Ask a Question