vba 将 Excel 工作簿中的一些工作表导出为 PDF
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14929344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Exporting Some Sheets from Excel Workbook to PDF
提问by datacentric
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.
我正在编写 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
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.
此代码不仅限制导出到图表表，还限制导出整个工作簿。任何人都可以帮助我弄清楚我的代码中缺少什么。
Thanks!
谢谢！
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
End Sub
结束子
采纳答案by Siddharth Rout
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
我很惊讶你的代码一开始就在运行 :) 你实际上应该有一个错误 run time error '13', type mismatch
Sheetsand Worksheetsare two different things in Excel
Sheets并且Worksheets在 Excel 中是两种不同的东西
The Worksheetscollection is a collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet. Whereas the Sheetscollection, 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.
该Worksheets集合是指定或活动工作簿中所有 Worksheet 对象的集合。每个 Worksheet 对象代表一个工作表。而Sheets另一方面，该集合不仅包含工作表集合，还包含其他类型的工作表，包括图表工作表、Excel 4.0 宏工作表和 Excel 5.0 对话框工作表。
So if you declare your object as Worksheet
因此，如果您将对象声明为 Worksheet
Dim s As Worksheet
Then ensure that while looping you loop through the correct collection
然后确保在循环时循环通过正确的集合
For Each s In ThisWorkbook.Worksheets
and not
并不是
For Each s In ThisWorkbook.Sheets
else you will get a run time error '13', type mismatch
否则你会得到一个 run time error '13', type mismatch
FOLLOWUP (Based on Comments)
跟进（基于评论）
@ 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
@ Siddharth：1. 是的，我想导出以“Chart”名称结尾的图表表。2. 我希望所有这些图表都在一个 PDF 中，并且 PDF 的名称应该是“原始”文件名。（我必须将最终的 PDF 文件保存在不同的位置，以免文件重叠。） – 以数据为中心
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
回答by Nick.McDermaid
This code will look through all the sheets. If the sheet name doesn't match it will hide it. When it's finished that it exports all visible sheets into one PDF. Make sure yuo don't save the Excel file afterwards or the sheets will remain hidden.
此代码将查看所有工作表。如果工作表名称不匹配，它将隐藏它。完成后，它将所有可见的工作表导出为一个 PDF。确保您之后不要保存 Excel 文件，否则工作表将保持隐藏状态。
Of course this code is not tested so if you have issues ask back (or try and resolve themself as you may learn something)
当然，这段代码没有经过测试，所以如果你有问题请回问（或者尝试自己解决，因为你可能会学到一些东西）
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
