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

问题描述：

我正在编写一个VBA代码，将excel中的一些表格导出到相同的PDF。我的excel文件中有几张图表，每个名称以（name）_Chart结尾。
我想将名称以wioth表结尾的所有表格导出到一个PDF文件。
这是我正在写的代码。

I am working on writing a VBA code to export some of the sheets in excel to same PDF. I have several chart sheets in my excel file each of which name ends with "(name)_Chart". I want to export all sheets with names ending wioth chart to one PDF file. Here is the code I am trying to write.

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

此代码不限制导出到图表单，而是导出整个工作簿。任何人都可以帮我看看我的代码中缺少什么。

This code is not limting export to only the chart sheets but exporting thy whole workbook. Can anyone help me with figurint out whats is missing in my code.

谢谢！

MODIFIED代码：

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

结束子

答

我很惊讶你的代码在第一个地方运行:)你应该实际上有一个错误运行时错误'13' ，类型不匹配

I am surprised that your code is running in the first place :) You should have actually got an error run time error '13', type mismatch

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

Sheets and Worksheets are two different things in Excel

Worksheets 集合是所有Worksheet对象的集合指定或活动的工作簿。每个Worksheet对象表示工作表。而另一方面， Sheets 集合不仅包括工作表的集合，还包括其他类型的工作表，以包括图表表，Excel 4.0宏表和Excel 5.0对话框

The Worksheets collection is a collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet. Whereas the Sheets collection, on the other hand, consist of not only a collection of worksheets but also other types of sheets to include Chart sheets, Excel 4.0 macro sheets and Excel 5.0 dialog sheets.

所以如果您将对象声明为工作表

So if you declare your object as Worksheet

Dim s As Worksheet

然后确保循环你循环通过正确的集合

Then ensure that while looping you loop through the correct collection

For Each s In ThisWorkbook.Worksheets

而不是

For Each s In ThisWorkbook.Sheets

否则你会得到一个运行时错误'13'，类型不匹配

else you will get a run time error '13', type mismatch

FOLLOWUP（基于评论）

@ Siddharth：1.是的，我想导出名为Chart的图表。 2.我想把所有这些图表放在一张PDF中，PDF的名称应该是原始文件名。（我将不得不将最终的PDF文件保存在不同的位置，因此文件不会重叠。） - 数据中心

@ 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

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

相关推荐