首页 文章

将一些表格从Excel工作簿导出为PDF

提问于
浏览
3

我正在编写一个VBA代码,将excel中的一些工作表导出为相同的PDF . 我的excel文件中有几个图表,每个图表都以“(name)_Chart”结尾 . 我想将名称以wioth 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

此代码不仅限于导出到图表工作表,而是导出整个工作簿 . 任何人都可以帮我搞清楚我的代码中缺少什么 .

谢谢!

MODIFIED CODE:

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

结束子

2 回答

  • 2

    我很惊讶你的代码首先运行:)你应该有一个错误 run time error '13', type mismatch

    SheetsWorksheets 是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
    

    否则你会得到 run time error '13', type mismatch

    FOLLOWUP (Based on Comments)

    @Siddharth:1 . 是的,我想导出以名称“Chart”结尾的图表 . 2.我希望所有这些图表都在一个PDF中,PDF的名称应该是“原始”文件名 . (我将不得不将最终的PDF文件保存在不同的位置,这样文件就不会重叠了 . ) - 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
    
  • 0

    此代码将查看所有工作表 . 如果工作表名称不匹配,则会将其隐藏 . 完成后,它将所有可见的工作表导出为一个PDF . 确保yuo之后不保存Excel文件,否则工作表将保持隐藏状态 .

    当然这个代码没有经过测试,所以如果你有问题请回来(或者尝试自己解决,因为你可能会学到一些东西)

    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") = 0 Then
                  ' Hide the sheet so it is not exported as PDF
                  s.Visible = False 
            End If
        Next s
    
        ' Export all sheets as PDF         
        ActiveSheet.ExportAsFixedFormat xlTypePDF, strPath & "TEST.pdf"
    End Sub
    

相关问题