Excel 到 PDF，多个范围相同的工作表(字符串)但 PrintArea 255 字符串限制

Question

我相信范围有 255 个字符的限制，所以我将范围分成 6 个单元格
B1 到 B6(下面的单元格 B1 和单元格 B2 示例都远低于 255 个字符)。

A1:I15, A17:I40, A42:I65, A92:I114, A116:I140, A142:I168, A170:I196, A198:I224, A226:I252, A254:I280, A282:I308, A310:I336, A338:I364, A366:I392, A394:I420, A422:I448

A450:I476, A478:I504, A526:I552, A554:I580, A582:I608, A610:I636, A638:I664, A666:I690, A692:I707, A730:I750, A752:I773, A775:I794, A796:I815, A817:I830, A855:I877, A879:I905, A907:I926

我尝试使用 Union 函数从这些范围生成 PDF，但不知何故我只从 B1 获取范围! B2 被忽略。这是我的代码:

Set rng = Union(shTemp.Range("B1"), shTemp.Range("B2"))

shTransformed.Activate
With ActiveSheet.PageSetup
    .Zoom = False
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .PrintArea = rng
 End With

 ActiveSheet.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:="c:\temp\test.pdf", _
 Quality:=xlQualityStandard, _
 IgnorePrintAreas:=False, _
 IncludeDocProperties:=True, _
 OpenAfterPublish:=True

Answer 1

可以使用此解决方法通过添加水平分页符并隐藏打印区域之间的行来绕过打印区域范围地址的 255 个字符限制。然而，它仅适用于这种情况，因为每个打印区域中最右边的列是相同的(即 I)，而且这种方法要求每个打印区域至少相隔一行。

它使用 OP 中定义的范围字符串成功测试。对表格名称、范围等进行一些修改。

Sub test()
Dim shTemp As Worksheet, shTr As Worksheet
Dim HideRng As Range, Rng As Range, MainRng As Range
Dim Ar As Range, cel As Range
Set shTemp = ThisWorkbook.Sheets(1)
Set shTr = ThisWorkbook.Sheets(2)

'To Dynamically Select Range containing Addresses
Dim SelRng As Range
Set SelRng = shTemp.Range("B1:B6")  ' Default range
shTemp.Activate
On Error Resume Next
Set SelRng = Application.InputBox("Select the range containing Print Range Addresses", "Select Range", SelRng.Address, , , , , 8)
    If Err > 0 Then
    Err.Clear
    Exit Sub
    End If
On Error GoTo 0
If SelRng Is Nothing Then Exit Sub

    For Each cel In SelRng.Cells
        If cel.Value <> "" Then
        If Not Range(cel.Value) Is Nothing Then
        'Debug.Print Range(cel.Value).Address
            If Rng Is Nothing Then
            Set Rng = Range(cel.Value)
            Else
            Set Rng = Union(Rng, Range(cel.Value))
            End If
        End If
        End If
    Next

If Rng Is Nothing Then Exit Sub

With shTr
 .Cells.PageBreak = xlPageBreakNone
pg = 1
maxcol = 1
    For Each Ar In Rng.Areas
        'Vartical Pagebreak: it is applicable only in this case where right column is same
        If pg = 1 Then
        Set MainRng = Ar(1, 1)
        .VPageBreaks.Add Ar(1, Ar.Columns.Count).Offset(0, 1)
        End If
    'Ar(1, 1).Value = "Page " & pg
    .HPageBreaks.Add Ar(Ar.Rows.Count, Ar.Columns.Count).Offset(1, 0)
        If pg > 1 Then
        If HideRng(HideRng.Rows.Count, 1).Row < Ar(1, 1).Row Then
        Set HideRng = Range(HideRng, Ar(1, 1).Offset(-1, 0))
        HideRng.EntireRow.Hidden = True
        End If
        End If
    Set HideRng = Ar(Ar.Rows.Count, 1).Offset(1, 0)
    If pg = Rng.Areas.Count Then Set MainRng = Range(MainRng, Ar(Ar.Rows.Count, Ar.Columns.Count))
    pg = pg + 1
    Next
End With

shTr.Activate
With ActiveSheet.PageSetup
    .Zoom = False
    .Orientation = xlPortrait
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .PrintArea = MainRng.Address
 End With

 ActiveSheet.ExportAsFixedFormat _
 Type:=xlTypePDF, _
 Filename:="c:\users\user\Desktop\test.pdf", _
 Quality:=xlQualityStandard, _
 IgnorePrintAreas:=False, _
 IncludeDocProperties:=True, _
 OpenAfterPublish:=True
End Sub

Screen Shot of created PDF