編輯：

Question

我在 Excel 工作表中新增了一個按鈕，現在當我單擊該按鈕時，它會以 pdf 格式將工作表保存在具有特定名稱的特定路徑中。

我想簡單地以 Excel 格式 (.xlsx) 儲存此工作表。所以

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

我需要改變什麼？

Answer 1

將以下程式碼更改為

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

到下面的程式碼

ActiveWorkbook.SaveAs Filename:="C:\Users\46506090\Desktop\Book1.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

更改路徑（C：\使用者\46506090\桌面\Book1.xlsm）到你想要的，看看它是否有效。

CreateBackup:=False  

這是可選的

編輯：

完整程式碼

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:\Users\46506090\Desktop\Book1.xlsm", _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
'Comment: Replace "C:\Users\46506090\Desktop\Book1.xlsm" to your desired filename
MsgBox "file has been created."

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

End Sub

Answer 2

更正其他答案應該只有一個保存位置

Option Explicit

Sub Button1_Click()
'Sub PDFActiveSheet2()

Dim ws As Worksheet
Dim strFile As String

On Error GoTo errHandler

strFile = "C:\Users\yourName\Desktop\Book1.xlsm"
'Comment: Replace "C:\Users\yourName\Desktop\Book1.xlsm" to your desired filename

Set ws = ActiveSheet

ActiveWorkbook.SaveAs Filename:=strFile, _
            FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

MsgBox "file has been created."

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

End Sub