首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >将工作表保存为pdf

问将工作表保存为pdf
EN

Stack Overflow用户
提问于 2015-04-10 07:11:35
回答 2查看 1.3K关注 0票数 1

我有这个问题,试图打印多张单张在一个pdf。浏览在线和论坛,我发现了这段代码,但当我使用它时,我得到错误9“子脚本超出范围”,我不明白为什么。我尝试了一个新的工作簿--代码--并且它正常工作。有人能帮我吗?

代码语言:javascript
复制
Private Sub cmd_PrintPDF_Click()

ThisWorkbook.Sheets(Array("Costs", "Cars")).Select

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
ThisWorkbook.Path & "/" & "Cost&Car", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub

这个宏从我的工作簿的主面板上运行,其中有各种命令按钮,在请求的sheets.When中,我运行这个宏--工作表"Cars“是隐藏的,这可能是问题吗?在编写代码之前,我尝试过使用.Activate,但它仍然无法工作。

EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2015-04-10 09:19:47

我使用此代码导出工作表到pdf。也许会对你有用。

代码语言:javascript
复制
Sub SheetsToPdf()

Dim Arr() As String
Dim PdfFileName As String
PdfFileName = "Cost&Car"

ReDim Arr(1, 1)
'sheets name in 1st column, 2nd column for info about visibility sheets
Arr(0, 0) = "Costs"
Arr(1, 0) = "Cars"

Cells(1, 1).Select
For i = LBound(Arr, 1) To UBound(Arr, 1)

Arr(i, 1) = ThisWorkbook.Sheets(Arr(i, 0)).visible ' info about visibility sheets

If Arr(i, 1) = "0" Then 'check visible Sheets "-1" - visible = True, "0" - visible = False
    ThisWorkbook.Sheets(Arr(i, 0)).visible = True
    OrgVisible = False
End If

If i = 0 Then
ThisWorkbook.Sheets(Arr(i, 0)).Select
Else
ThisWorkbook.Sheets(Arr(i, 0)).Select False 'select all sheets with names in arr()
End If  



Next i

'select all data
Cells.Select

'export to pdf
Selection.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
ThisWorkbook.path & "/" & PdfFileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

'restore old visibility for sheets
For i = LBound(Arr, 1) To UBound(Arr, 1)
    If Arr(i, 1) = "0" Then 'set old visible
        ThisWorkbook.Sheets(Arr(i, 0)).visible = False
    End If
Next i

End Sub

也许你需要这个更简单的版本?仅出口可见的纸张:

代码语言:javascript
复制
Sub SheetsToPdf2()

Dim PdfFileName As String
PdfFileName = "Cost&Car"

Cells(1, 1).Select
For Each Sheets_ In Sheets
    If Sheets_.visible Then
    ThisWorkbook.Sheets(Sheets_.Name).Select False
    End If
Next
'select all data in one sheet
Cells.Select

'export to pdf
Selection.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
ThisWorkbook.path & "/" & PdfFileName, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True

End Sub
票数 1
EN

Stack Overflow用户

发布于 2015-04-10 07:47:11

实际上,如果没有无用的、重源的Select,就无法打印隐藏的工作表,下面是您的代码:

代码语言:javascript
复制
Private Sub cmd_PrintPDF_Click()

ThisWorkbook.Sheets("Costs").Visible = True
ThisWorkbook.Sheets("Cars").Visible = True

ThisWorkbook.Sheets(Array("Costs", "Cars")).ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=ThisWorkbook.Path & "/" & "Cost&Car", _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True


ThisWorkbook.Sheets("Costs").Visible = False
ThisWorkbook.Sheets("Cars").Visible = False

End Sub
票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/29555445

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档