在一个打印作业 Excel VBA 中选择并打印多张纸
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23156594/
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
Select and print multiple sheets in one print job Excel VBA
提问by Simoney
I have inherited this awfulness, and been asked to make updates.
我继承了这种可怕的一面，并被要求进行更新。
Currently this workbook is built with one sheet that has a bunch of checkboxes, all associated with other sheets in the workbook. The user selects checkboxes, and then hits a button to print the sheets associated with each checkbox that is checked.
目前，此工作簿由一张带有一堆复选框的工作表构建，所有复选框都与工作簿中的其他工作表相关联。用户选择复选框，然后点击一个按钮来打印与每个选中的复选框相关联的工作表。
The way it's built now, it runs a print job for each sheet selected. I have been asked to run one print job for all sheets selected (to avoid having a hundred cover sheets).
按照现在的构建方式，它为每个选定的工作表运行一个打印作业。我被要求为所有选定的纸张运行一个打印作业（以避免有一百张封面）。
I have written a little VBA function that produces a string containing the name of every sheet for which the checkbox is checked, in quotes, comma separated.
我编写了一个小 VBA 函数，它生成一个字符串，其中包含选中复选框的每个工作表的名称，用引号括起来，以逗号分隔。
I need to figure out a way to use this information to select all the sheets and then print once each sheet I need is selected.
我需要想办法使用此信息来选择所有工作表，然后在选择我需要的每张工作表后进行打印。
Or even hell, I'd take being able to spit this string back into the macro that was originally written for the print ability. Right now the macro is another sheet in the workbook, and has 102 different print commands, controlled by a bunch of if statements. So I'd take being about to spit the string into that sheet so I could run one print command.
或者甚至是地狱，我希望能够将这个字符串吐回到最初为打印功能编写的宏中。现在宏是工作簿中的另一个工作表，有 102 个不同的打印命令，由一堆 if 语句控制。所以我打算把字符串吐到那张纸上，这样我就可以运行一个打印命令。
Either way, someone please help.
无论哪种方式，请有人帮忙。
Here is my code:
这是我的代码：
Public Function sheetString()
Dim c As Integer
Dim r As Integer
Dim sConcat As String
Dim ws As Worksheet
For c = 2 To 6 Step 2
For r = 1 To 46
If Sheet94.Cells(r, c) = True Then
sConcat = sConcat & Sheet94.Cells(r, c - 1) & ", "
End If
Next r
Next c
sConcat = Left(sConcat, Len(sConcat) - 2)
Debug.Print sConcat
End Function
The output of the code is like this (with varying names depending on which boxes are checked):
代码的输出是这样的（根据选中的框有不同的名称）：
"PR015", "PR018", "PR019", "PR026", "PR029A"
“PR015”、“PR018”、“PR019”、“PR026”、“PR029A”
EDIT: Thanks to simoco, I'm closer than I've been so far. Here is the code as it stands now.
编辑：多亏了 simoco，我比到目前为止更接近。这是现在的代码。
Public Function sheetString()
Dim c As Integer
Dim r As Integer
Dim sConcat As String
Dim ws As Worksheet
Set ws = Sheet94
For c = 2 To 6 Step 2
For r = 1 To 46
If ws.Cells(r, c) = True Then
sConcat = sConcat & ws.Cells(r, c - 1) & ","
End If
Next r
Next c
sConcat = Left(sConcat, Len(sConcat) - 1)
Debug.Print sConcat
sheetString = sConcat
End Function
Sub test()
'if cells with sheet names contains quotes
'Sheets(Split(Replace(sheetString, """", ""), ",")).Select
'if cells with sheet names doesn't contain quotes
Sheets(Split(sheetString, ",")).Select
ActiveSheet.PrintOut Copies:=1
End Sub
It's running without bombing out, but now it's only selecting the first sheet that has it's box checked.
它在没有轰炸的情况下运行，但现在它只选择第一个选中它的工作表。
回答by Dmitry Pavliv
Here is slightly modified function sheetString:
这是稍微修改的功能sheetString：
Public Function sheetString()
Dim c As Integer, r As Integer
Dim sConcat As String
Dim ws As Worksheet
Set ws = Sheet94
For c = 2 To 6 Step 2
For r = 1 To 46
If ws.Cells(r, c) Then
sConcat = sConcat & ws.Cells(r, c - 1) & ","
End If
Next r
Next c
sConcat = Left(sConcat, Len(sConcat) - 1)
Debug.Print sConcat
sheetString = sConcat
End Function
and then call use it like this:
然后像这样调用使用它：
Sub test()
'if cells with sheet names contains quotes
'Sheets(Split(Replace(sheetString, """", ""), ",")).PrintOut Copies:=1
'if cells with sheet names doesn't contain quotes
Sheets(Split(sheetString, ",")).PrintOut Copies:=1
End Sub
回答by user6904286
Sub Macro1()
Application.Dialogs(xlDialogPrint).Show
End Sub
Sub Macro()
Worksheets.PrintOut
End Sub
Use this code to print all sheets in xl. Also select your specific pri
使用此代码打印 xl 中的所有工作表。还要选择您的具体pri
