Excel VBA 2007：选择除指定工作表之外的所有工作表（对其执行操作）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3244142/
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
Excel VBA 2007: Select all sheets except for specified ones (to perform actions onto)
提问by Margaret
I'm trying to create a macro that selects most of the sheets in the workbook and then uses ExportAsFixedFormat to print them to PDF.
我正在尝试创建一个宏来选择工作簿中的大部分工作表，然后使用 ExportAsFixedFormat 将它们打印为 PDF。
The problem is, while I know the sheets I don'twant to include, I don't know exactly which sheets will be in place in at given time, so I can't make a static list of "Select these sheets" (It'd probably be painful to maintain anyway).
问题是，虽然我知道我的床单不希望包括，我不知道到底是哪片将就位在给定的时间，所以我不能做的“选择这些表”的静态列表（无论如何维护可能会很痛苦）。
Is there some way to, say, "Select all then unselect these specific sheets" or something similar?
有没有办法说，“全选然后取消选择这些特定的工作表”或类似的东西？
回答by Fionnuala
How about:
怎么样：
Dim s As Worksheet
DoNotInclude = "Sheet2"
For Each s In ActiveWorkbook.Worksheets
If InStr(DoNotInclude, s.Name) = 0 Then
''Do something
End If
Next
回答by GSerg
There isn't. Which doesn't prevent you from doing it yourself:
没有。这并不妨碍你自己做：
Public Sub SelectExceptThese(ByVal wb As Workbook, ExceptThese() As String)
Dim ws() As String
ReDim ws(1 To wb.Worksheets.Count)
Dim wt As Worksheet
Dim i As Long
For Each wt In wb.Worksheets
If Not IsInArray(ExceptThese, wt.Name) Then
i = i + 1
ws(i) = wt.Name
End If
Next
If i > 0 Then
ReDim Preserve ws(LBound(ws) To LBound(ws) + i - 1)
wb.Worksheets(ws).Select
End If
End Sub
Private Function IsInArray(arr() As String, val As String) As Boolean
Dim i As Long
For i = LBound(arr) To UBound(arr)
If arr(i) = val Then
IsInArray = True
Exit Function
End If
Next
End Function
回答by Kiran Kodukula
This might be a bit too late to answer however might be helpful for others.
这可能有点太晚了，但可能对其他人有帮助。
If you know what sheets you don't want to Export And If these Sheets are the same all the time you can do the following..
如果您知道不想导出哪些工作表，并且如果这些工作表始终相同，您可以执行以下操作。
Sheets("sheetnotincluded1").Visible = False
Sheets("sheetnotincluded2").Visible = False
ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"FilePath.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
True
Sheets("sheetnotincluded1").Visible = True
Sheets("sheetnotincluded2").Visible = True
You are simply hiding unwanted sheets, exporting the active workbook and unhiding unwanted sheets.
您只是隐藏不需要的工作表，导出活动工作簿并取消隐藏不需要的工作表。
