☰

편집하다:

tag-icon • tag-icon microsoft-excel microsoft-excel-2010 worksheet-function microsoft-excel-2007 vba
편집하다:

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

무엇을 바꿔야 합니까?

답변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:\Users\46506090\Desktop\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

답변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

관련 정보