设置多个和较大的打印区域

Question

我试图在excel中设置大型和多个打印区域。我有几个文档是在excel中创建的，对于每个范围区域，我都试图设置打印区域。

这是我的代码:

Sub SetPrintArea()
Dim ws As Worksheet
Dim yRng1() As Variant
Dim yRng2() As Variant
Dim yRng3() As Variant
Dim yRng4() As Variant
Dim yRng5() As Variant
Dim yRng6() As Variant
Dim yRng7() As Variant
Dim yRng8() As Variant
Dim yRng9() As Variant
Dim yRng10() As Variant
Dim yRng11() As Variant
Dim yRng12() As Variant
Dim yRng13() As Variant
Dim yRng14() As Variant
Dim yRng15() As Variant
Dim yRng16() As Variant
Dim yRng17() As Variant
Dim yRng18() As Variant
Dim yRng19() As Variant
Dim yRng20() As Variant
Dim combinedArrays() As Variant
Dim printRange As Range

Set ws = ThisWorkbook.Sheets("Manifest")
yRng1 = Array(ws.Range("A1:W59"), ws.Range("A62:W120"), ws.Range("A123:W181"), ws.Range("A184:W242"))
yRng2 = Array(ws.Range("A245:W303"), ws.Range("A306:W364"), ws.Range("A367:W425"), ws.Range("A428:W486"), ws.Range("A489:W547"))
yRng3 = Array(ws.Range("A550:W608"), ws.Range("A611:W669"), ws.Range("A672:W730"), ws.Range("A733:W791"), ws.Range("A794:W852"))
yRng4 = Array(ws.Range("A855:W913"), ws.Range("A916:W974"), ws.Range("A977:W1035"), ws.Range("A1038:W1096"))
yRng5 = Array(ws.Range("A1099:W1157"), ws.Range("A1160:W1218"), ws.Range("A1221:W1279"), ws.Range("A1282:W1340"), ws.Range("A1343:W1401"))
yRng6 = Array(ws.Range("A1404:W1462"), ws.Range("A1465:W1523"), ws.Range("A1526:W1584"), ws.Range("A1587:W1645"), ws.Range("A1648:W1706"))
yRng7 = Array(ws.Range("A1709:W1767"), ws.Range("A1770:W1828"), ws.Range("A1831:W1889"), ws.Range("A1892:W1950"), ws.Range("A1953:W2011"))
yRng8 = Array(ws.Range("A2014:W2072"), ws.Range("A2075:W2133"), ws.Range("A2136:W2194"), ws.Range("A2197:W2255"), ws.Range("A2258:W2316"))
yRng9 = Array(ws.Range("A2319:W2377"), ws.Range("A2380:W2438"), ws.Range("A2441:W2499"), ws.Range("A2502:W2560"), ws.Range("A2563:W2621"))
yRng10 = Array(ws.Range("A2624:W2682"), ws.Range("A2685:W2743"), ws.Range("A2746:W2804"), ws.Range("A2807:W2865"), ws.Range("A2868:W2926"))
yRng11 = Array(ws.Range("A2929:W2987"), ws.Range("A2990:W3048"), ws.Range("A3051:W3109"), ws.Range("A3112:W3170"), ws.Range("A3173:W3231"))
yRng12 = Array(ws.Range("A3234:W3292"), ws.Range("A3295:W3353"), ws.Range("A3356:W3414"), ws.Range("A3417:W3475"), ws.Range("A3478:W3536"))
yRng13 = Array(ws.Range("A3539:W3597"), ws.Range("A3600:W3658"), ws.Range("A3661:W3719"), ws.Range("A3722:W3780"), ws.Range("A3783:W3841"))
yRng14 = Array(ws.Range("A3844:W3902"), ws.Range("A3905:W3963"), ws.Range("A3966:W4024"), ws.Range("A4027:W4085"), ws.Range("A4088:W4146"))
yRng15 = Array(ws.Range("A4149:W4207"), ws.Range("A4210:W4268"), ws.Range("A4271:W4329"), ws.Range("A4332:W4390"), ws.Range("A4393:W4451"))
yRng16 = Array(ws.Range("A4454:W4512"), ws.Range("A4515:W4573"), ws.Range("A4576:W4634"), ws.Range("A4637:W4695"), ws.Range("A4698:W4756"))
yRng17 = Array(ws.Range("A4759:W4817"), ws.Range("A4820:W4878"), ws.Range("A4881:W4939"), ws.Range("A4942:W5000"), ws.Range("A5003:W5061"))
yRng18 = Array(ws.Range("A5064:W5122"), ws.Range("A5125:W5183"), ws.Range("A5186:W5244"), ws.Range("A5247:W5305"), ws.Range("A5308:W5366"))
yRng19 = Array(ws.Range("A5369:W5427"), ws.Range("A5430:W5488"), ws.Range("A5491:W5549"), ws.Range("A5552:W5610"), ws.Range("A5613:W5671"))
yRng20 = Array(ws.Range("A5674:W5732"), ws.Range("A5735:W5793"), ws.Range("A5796:W5854"), ws.Range("A5857:W5915"), ws.Range("A5918:W5976"))

combinedArrays = Array(yRng1, yRng2, yRng3, yRng4, yRng5, yRng6, yRng7, yRng8, yRng9, yRng10, yRng11, yRng12, yRng13, yRng14, yRng15, yRng16, yRng17, yRng18, yRng19, yRng20)

Set printRange = combinedArrays(0)(0)
For i = 0 To UBound(combinedArrays)
For j = 0 To UBound(combinedArrays(i))
If i = 0 And j = 0 Then
Set printRange = combinedArrays(i)(j)
Else
Set printRange = Union(printRange, combinedArrays(i)(j))
End If
Next j
Next i
ws.PageSetup.printArea = printRange.Address
End Sub

然而，当我尝试打印时，我只得到18页，其中包括"A1038:W1096"范围。我希望上面定义的每个范围都有一个页面。

这是excel的限制吗?还是我做错了什么?

惟妙惟肖,

Answer 1

如果有人感兴趣，我只是使用了office JavaScript:

function main(workbook: ExcelScript.Workbook) {
let manifestSheet = workbook.getWorksheet("Manifest");
// Set print area for selectedSheet to range "A1:D5, A7:D12, A14:D19"
let yRng1 = "A1:W59, A62:W120, A123:W181, A184:W242"
let yRng2 = "A245:W303, A306:W364, A367:W425, A428:W486, A489:W547"
let yRng3 = "A550:W608, A611:W669, A672:W730, A733:W791, A794:W852"
let yRng4 = "A855:W913, A916:W974, A977:W1035, A1038:W1096"
let yRng5 = "A1099:W1157, A1160:W1218, A1221:W1279, A1282:W1340, A1343:W1401"
let yRng6 = "A1404:W1462, A1465:W1523, A1526:W1584, A1587:W1645, A1648:W1706"
let yRng7 = "A1709:W1767, A1770:W1828, A1831:W1889, A1892:W1950, A1953:W2011"
let yRng8 = "A2014:W2072, A2075:W2133, A2136:W2194, A2197:W2255, A2258:W2316"
let yRng9 = "A2319:W2377, A2380:W2438, A2441:W2499, A2502:W2560, A2563:W2621"
let yRng10 = "A2624:W2682, A2685:W2743, A2746:W2804, A2807:W2865, A2868:W2926"
let yRng11 = "A2929:W2987, A2990:W3048, A3051:W3109, A3112:W3170, A3173:W3231"
let yRng12 = "A3234:W3292, A3295:W3353, A3356:W3414, A3417:W3475, A3478:W3536"
let yRng13 = "A3539:W3597, A3600:W3658, A3661:W3719, A3722:W3780, A3783:W3841"
let yRng14 = "A3844:W3902, A3905:W3963, A3966:W4024, A4027:W4085, A4088:W4146"
let yRng15 = "A4149:W4207, A4210:W4268, A4271:W4329, A4332:W4390, A4393:W4451"
let yRng16 = "A4454:W4512, A4515:W4573, A4576:W4634, A4637:W4695, A4698:W4756"
let yRng17 = "A4759:W4817, A4820:W4878, A4881:W4939, A4942:W5000, A5003:W5061"
let yRng18 = "A5064:W5122, A5125:W5183, A5186:W5244, A5247:W5305, A5308:W5366"
let yRng19 = "A5369:W5427, A5430:W5488, A5491:W5549, A5552:W5610, A5613:W5671"
let yRng20 = "A5674:W5732, A5735:W5793, A5796:W5854, A5857:W5915, A5918:W5976"
let printRanges = [yRng1, yRng2, yRng3, yRng4, yRng5, yRng6, yRng7, yRng8, yRng9, yRng10, yRng11, yRng12, yRng13, yRng14, yRng15, yRng16, yRng17, yRng18, yRng19, yRng20];
let printRange = printRanges.join(',');
manifestSheet.getPageLayout().setPrintArea(printRange);

}

这解决了我的问题。