vba - 将某些工作表从 Excel 工作簿导出为 PDF

Question

我正在编写一个 VBA 代码，将 Excel 中的一些工作表导出到同一个 PDF。我的 Excel 文件中有几个图表工作表，每个图表工作表的名称都以“(name)_Chart”结尾。我想将名称以图表结尾的所有工作表导出到一个 PDF 文件。这是我正在尝试编写的代码。

Sub FindWS()
        'look if it at least contains part of the name
        Dim s As Worksheet
        Dim strPath As String

        strPath = ActiveWorkbook.Path & "\"

        For Each s In ThisWorkbook.Sheets
            If InStr(1, s.Name, Chart) Then
                s.Activate
                ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & s.Name & ".pdf"
                Exit Sub
            End If
        Next s
End Sub

此代码不限制仅导出图表，而是导出整个工作簿。谁能帮我弄清楚我的代码中缺少什么。

谢谢!

修改代码:

Sub FindWS()
'look if it at least contains part of the name
Dim s As Worksheet
Dim strPath As String

strPath = ActiveWorkbook.Path & "\"

For Each s In ThisWorkbook.Worksheets
      If InStr(1, s.Name, "Chart") = 0 Then
          ' Hide the sheet so it is not exported as PDF
          s.Visible = False
             End If
Next s
          With ActiveWorkbook
          .ExportAsFixedFormat xlTypePDF, strPath & "TEST.pdf"
                End With

结束子

Answer 1

我很惊讶你的代码首先运行了:)你实际上应该得到一个错误运行时错误'13'，类型不匹配

工作表和工作表是 Excel 中的两个不同的东西

Worksheets 集合是指定或事件工作簿中所有 Worksheet 对象的集合。每个 Worksheet 对象代表一个工作表。另一方面，Sheets 集合不仅包含工作表集合，还包含其他类型的工作表，包括图表工作表、Excel 4.0 宏工作表和 Excel 5.0 对话框工作表。

因此，如果您将对象声明为 Worksheet

Dim s As Worksheet

然后确保在循环时循环遍历正确的集合

For Each s In ThisWorkbook.Worksheets

而不是

For Each s In ThisWorkbook.Sheets

否则您将收到运行时错误“13”，类型不匹配

跟进(基于评论)

@ Siddharth: 1. Yes, I want to export Chart sheets that ends with name "Chart". 2. I want all those charts in one PDF and the name of the PDF should be the "original" file name. (I will have to save the final PDF files in different location so there will be no overlapping of files.) – datacentric

Option Explicit

Sub Sample()
    Dim ws As Object
    Dim strPath As String, OriginalName As String, Filename As String

    On Error GoTo Whoa

    '~~> Get activeworkbook path
    strPath = ActiveWorkbook.Path & "\"
    '~~> Get just the name without extension and path
    OriginalName = Left(ActiveWorkbook.Name, (InStrRev(ActiveWorkbook.Name, ".", -1, vbTextCompare) - 1))
    '~~> PDF File name
    Filename = strPath & OriginalName & ".pdf"

    '~~> Loop through Sheets Collesction
    For Each ws In ActiveWorkbook.Sheets
        '~~> Check if it is a Chart Sheet and also it ends in "Chart"
        If ws.Type = 3 And UCase(Right(Trim(ws.Name), 5)) = "CHART" Then
            ws.Visible = True
        Else
            ws.Visible = False
        End If
    Next ws

    '~~> Export to pdf
    ActiveWorkbook.ExportAsFixedFormat xlTypePDF, Filename

LetsContinue:
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub