vba - 通过 VBA 代码将单个 Excel 工作表保存为单独的 PDF 文件

Question

我正在尝试保存一些单独的 excel 表以分隔 PDF 文件并根据一些固定单元格和每个表的名称重命名每个文件，代码如下，但结果与我预期的不一样，请您帮我查看是否我的代码有问题吗？

Option Explicit

Sub SheetsToPDFs()

    Dim wks As Worksheet

    ActiveWorkbook.Sheets(Array("Overall", "AGRM", "AICI", "AMUI", "ARMT")).Select
    For Each wks In Worksheets
        wks.Select
        wks.ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:=Range("SavePath").Value & "\" & wks.Name & "_CYProductionReport_" & Range("ReportDate").Value, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, OpenAfterPublish:=False

    Next wks

End Sub

Answer 1

您正在设置一个数组或工作表，但之后并没有真正对它们做任何事情。在下文中，数组只是工作表的名称，由 With ... End With statement 使用。提供父工作表引用。

Sub SheetsToPDFs()
    Dim w As Long, aSHTs As Variant

    aSHTs = Array("Overall", "AGRM", "AICI", "AMUI", "ARMT")

    For w = LBound(aSHTs) To UBound(aSHTs)
        With Worksheets(aSHTs(w))
            'the filename should NOT have a .PDF file extension!
            Debug.Print Range("SavePath").Value & "\" & .Name & "_CYProductionReport_" & Range("ReportDate").Text
            .ExportAsFixedFormat Type:=xlTypePDF, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
                Filename:=Range("SavePath").Value & "\" & .Name & "_CYProductionReport_" & Range("ReportDate").Text, _
                IgnorePrintAreas:=False, OpenAfterPublish:=False
        End With
    Next w

End Sub