同时将多个图纸导出为PDF，而无需使用ActiveSheet或Select [英] Export multiple sheets to PDF simultaneously without using ActiveSheet or Select

问题描述

它已经深入人心，以避免出现错误并提供良好的用户体验，最好避免使用.Select，.Activate，ActiveSheet，ActiveCell等.

It has been drilled into my head, to avoid bugs and provide a good user experience, it is best to avoid using .Select, .Activate, ActiveSheet,ActiveCell, etc.

请牢记这一点，是否有一种方法可以在工作簿中的Sheets的子集上使用.ExportAsFixedFormat方法而不采用上述方法之一?到目前为止，我想出的唯一方法是采用以下两种方法之一:

Keeping this in mind, is there a way to use the .ExportAsFixedFormat method on a subset of Sheets in a workbook without employing one of the above? So far the only ways I have been able to come up with to do this are to either:

1. 使用For Each;但是，这会导致单独的PDF文件，这是不好的.

2. 使用与宏记录器生成的代码相似的代码，后者使用.Select和ActiveSheet:

1. use a For Each; however, this results in separate PDF files, which is no good.

2. use the code similar to that generated by the macro recorder, which uses .Select and ActiveSheet:

Sheets(Array("Sheet1", "Chart1", "Sheet2", "Chart2")).Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
    "exported file.pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, openafterpublish:= True

也许不使用ActiveSheet是不可能的，但是我至少可以以某种方式绕过使用.Select吗?

Perhaps it is impossible not to use ActiveSheet, but can I at least get around using .Select somehow?

我已经尝试过了:

Sheets(Array("Sheet1", "Chart1", "Sheet2","Chart2")).ExportAsFixedFormatType:= _
    xlTypePDF, Filename:= "exported file.pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, openafterpublish:= _
    True

这将产生:

错误438:对象不支持此属性或方法

error 438: Object doesn't support this property or method

推荐答案

讨厌疏通一个老问题，但是我不希望看到有人在这个问题上绊脚石，而在其他答案中却使用了代码体操. ExportAsFixedFormat方法仅导出可见工作表和图表.这样更清洁，更安全，更轻松:

Hate to dredge up an old question, but I'd hate to see somebody stumbling across this question resort to the code gymnastics in the other answers. The ExportAsFixedFormat method only exports visible Worksheets and Charts. This is much cleaner, safer, and easier:

Sub Sample()

    ToggleVisible False

    ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, FileName:= _
        "exported file.pdf", Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=True

    ToggleVisible True

End Sub

Private Sub ToggleVisible(state As Boolean)
    Dim ws As Object

    For Each ws In ThisWorkbook.Sheets
        Select Case ws.Name
        Case "Sheet1", "Chart1", "Sheet2", "Chart2"
        Case Else
            ws.Visible = state
        End Select
    Next ws
End Sub

这篇关于同时将多个图纸导出为PDF，而无需使用ActiveSheet或Select的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！