在不使用ActiveSheet或Select的情况下同时使用多张纸张上的方法 [英] Use a method on multiple sheets simultaneously without using ActiveSheet or Select

问题描述

我遇到的问题是很简单的描述。

它已经钻进了我的头脑（通过大量阅读SO和其他Excel VBA论坛/资源），一般来说，为了避免错误并提供一个好的用户体验，最好避免使用。选择， .Activate ， ActiveSheet <编写VBA代码时，code>， ActiveCell 等。





记住这一点：有一种方法可以在<$ c的子集上使用 .ExportAsFixedFormat 方法$ c> Sheets 在没有使用上述之一的工作簿中？到目前为止，我唯一可以做到这一点的方法是：

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



2. 使用类似于由宏记录器生成的代码，它使用 .Select 和 ActiveSheet ：

     （Sheet1，Chart1，Sheet2，Chart2））选择
    ActiveSheet.ExportAsFixedFormat类型：= xlTypePDF，文件名：= _ 
   导出的file.pdf，质量：= xlQualityStandard，_ 
    IncludeDocProperties：= True，IgnorePrintAreas：= False，openafterpublish：= True 
     

任何想法？也许不可能不使用 ActiveSheet ，但是我至少可以使用。选择某种方式？

编辑：

为了拯救任何人的修补麻烦，我已经尝试过：

  Sheets（Array（Sheet1，Chart1，Sheet2，Chart2））ExportAsFixedFormatType：= _ 
 xlTypePDF， ：=export file.pdf，质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True，IgnorePrintAreas：= False，openafterpublish：= _ 
 True 
  / pre> 
 
 
生成错误438：对象不支持此属性或方法 
解决方案
憎恨一个旧的问题，但是我讨厌看到有人绊倒了这个问题，在其他答案中，使用代码体操。  ExportAsFixedFormat 方法仅导出可见工作表和图表。这样更干净，更安全，更简单：
  Sub Sample（）
 
 ToggleVisible False 
 
 ThisWorkbook.ExportAsFixedFormat类型：= xlTypePDF，FileName：= _ 
exports file.pdf，质量：= xlQualityStandard，_ 
 IncludeDocProperties：= True，IgnorePrintAreas：= False，_ 
 OpenAfterPublish：= True 
 
 ToggleVisible True 
 
 End Sub 
 
 Private Sub ToggleVisible（state As Boolean）
 Dim ws作为对象
 
对于每个ws InWorkbook.Sheets 
选择案例ws.Name 
案例Sheet1，Chart1，Sheet2，Chart2
 Case Else 
 ws.Visible = state 
结束选择
下一个ws 
 End Sub 
  
 
The problem I'm having is fairly simple to describe. 

It has been drilled into my head (through lots of reading on SO and other Excel VBA forums/resources) that in general, in order to avoid bugs and provide a good user experience, it is best to avoid using .Select, .Activate, ActiveSheet,ActiveCell, etc etc when writing VBA code. 

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:

use a For Each; however, this results in separate PDF files, which is no good.
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

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

EDIT:

To save anyone the trouble of tinkering, I have already tried this:
Sheets(Array("Sheet1", "Chart1", "Sheet2","Chart2")).ExportAsFixedFormatType:= _
    xlTypePDF, Filename:= "exported file.pdf", Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, IgnorePrintAreas:=False, openafterpublish:= _
    True
Produces error 438: Object doesn't support this property or method 
解决方案
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


                        
这篇关于在不使用ActiveSheet或Select的情况下同时使用多张纸张上的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！