☰

Excel VBA：将选定的工作表导出为单个 PDF，Excel 不会导出除第一张工作表之外的其他工作表

tag-icon • tag-icon microsoft-excel vba
Excel VBA：将选定的工作表导出为单个 PDF，Excel 不会导出除第一张工作表之外的其他工作表

我尝试了几种方法来包含选定的图纸，然后导出为 pdf。每次只有第一张图纸包含在 pdf 文件中。我想实现的是：1. 手动选择图纸 2. 运行宏。宏将：1. 将选定的图纸导出为单个 pdf。2. 取消选择选定的图纸（以避免以后无意中编辑选定的图纸。）

 Sub Export_Selected_Sheets_To_PDF()
    Dim ws As Worksheet
    Dim PDF_Name, Doc_ID, Excel_Name, SelectedSheets() As String
    Dim n, i As Long
    Dim fso: Set fso = CreateObject("Scripting.FileSystemObject")

    'Code for naming the PDF (equals excel filename including a prefix from a cell named "DocID")
    'Will be saved at same location as the excel-file
    Doc_ID = Range("DocID").Value                           'DocID = named cell in the WS
    Excel_Name = fso.GetBaseName(ActiveWorkbook.FullName)    'Excel filename without file extension
    PDF_Name = Doc_ID & "_" & Excel_Name & ".pdf"            'PDF filename

    'Showing the PDF_Name
    Debug.Print PDF_Name

    'Code for selecting the "selected" sheets
    n = 0
    For Each ws In ActiveWindow.SelectedSheets
        ReDim Preserve SelectedSheets(n)
        SelectedSheets(n) = ws.Name
        n = n + 1
    Next

    'Showing the list of selected sheets
    For i = LBound(SelectedSheets) To UBound(SelectedSheets)
        Debug.Print SelectedSheets(i)
    Next i

    'Export selected sheets to pdf
    Sheets(SelectedSheets).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_Name, Quality:= _
    xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=False, _
    OpenAfterPublish:=True

    'Deactivate selection to avoid editing across selected sheets.
    ActiveSheet.Select
End Sub

答案1

在文中 Excel VBA 将选定的工作表导出为 PDF 提出了和你相同的方法。

然而，进一步的测试表明，这种技术依赖于被已选择在每个工作表上。

然后将代码修改为如下形式：

Sub Macro1()

   Sheets("Sheet1").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet2").Activate
   ActiveSheet.UsedRange.Select
   Sheets("Sheet3").Activate
   ActiveSheet.UsedRange.Select

   ThisWorkbook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select
   Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
      "C:\Users\James\Desktop\pdfmaker.pdf", Quality:=xlQualityStandard, _
      IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
      True
End Sub

这个答案得到了高度赞同，所以可能对你的情况有帮助。

相关内容