首页 文章

Excel VBA将多个工作表导出为PDF

提问于
浏览 次
0

我一直在尝试将多个工作表上的命名范围导出到单个pdf . 最初，它似乎工作正常，然后我注意到当将各种纸张加载到数组中时所选范围会发生变化 .

通过使用 Selection.ExportAsFixedFormat 这是我的理解，这将仅导出所选单元格 . 因此，我有一个宏来循环遍历所需的工作表，选择需要的各种范围，然后为所有工作表创建一个数组，以允许导出到单个pdf .

Dim wb As Workbook
Dim srcSht As Worksheet
Dim toPrnt As String

Set wb = ThisWorkbook
Set srcSht = wb.Sheets("print_array")

Dim myArr1() As Variant
    myArr1 = srcSht.Range("myPrintArray")

Dim i As Long
Dim j As String
    For i = LBound(myArr1, 1) To UBound(myArr1, 1)
            j = myArr1(i, 1)

    wb.Sheets(j).Activate
        wb.ActiveSheet.Range("CCB_" & Left(j, 5) & "_Print").Select

    Next i

wb.Sheets(Array("CAT01 - Request for Resource", _
                            "CAT02 - Resource Allocation", _
                            "CAT03 - Product Data Sources", _
                            "CAT04 - Target & Control Cells", _
                            "CAT05 - Matching And Deduping", _
                            "CAT06 - Exclusions", _
                            "CAT07 - Data from other teams", _
                            "CAT08 - Outputs", _
                            "CAT09 - Special Instructions", _
                            "CAT10 - Brief Meeting Sign Off" _
                            )).Select

Selection.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_FileAndPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True

当单步执行代码时，一切都会计划到创建工作表数组，此时所选范围会发生变化 .

我也尝试过使用PageSetup，但结果是一样的 .

With ActiveSheet.PageSetup
    .Orientation = xlLandscape
    .PaperSize = xlPaperA4
    .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = 1
    .PrintArea = Range("CCB_" & Left(j, 5) & "_Print").Address
End With

在本论坛审阅了几篇类似的帖子后，我仍然感到茫然 .

任何人都可以了解创建阵列时所选范围发生变化的原因，还是有任何其他可能有用的建议？

非常感谢

excel vba excel-vba pdf excel-2010

2 回答

  • 0

    我设法通过将所选范围复制到临时文件然后从那里导出来解决我的问题 . 完整的解决方案看起来像这样......

    Dim wb As Workbook
Dim srcSht As Worksheet
Dim tempFile As String

Set wb = ThisWorkbook
Set srcSht = wb.Sheets("print_array")

Dim myArr1() As Variant
    myArr1 = srcSht.Range("myPrintArray")

Dim i As Long
Dim j As String
    For i = LBound(myArr1, 1) To UBound(myArr1, 1)
            j = myArr1(i, 1)

    wb.Sheets(j).Activate

    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
        .PaperSize = xlPaperA4
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintArea = Range("CCB_" & Left(j, 5) & "_Print").Address
    End With

        Next i

    wb.Sheets(Array("CAT01 - Request for Resource", _
                                "CAT02 - Resource Allocation", _
                                "CAT03 - Product Data Sources", _
                                "CAT04 - Target & Control Cells", _
                                "CAT05 - Matching And Deduping", _
                                "CAT06 - Exclusions", _
                                "CAT07 - Data from other teams", _
                                "CAT08 - Outputs", _
                                "CAT09 - Special Instructions", _
                                "CAT10 - Brief Meeting Sign Off" _
                                )).copy

                With ActiveWorkbook
                    .Save

                    tempFile = .FullName

                    .ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_FileAndPath, _
                        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
                        :=False, OpenAfterPublish:=True

                    .Close

                End With

                Kill tempFile

   End If

    我希望这可以帮助将来的某个人 .

    谢谢 .

    回复于
  • 0

    我遇到了同样的问题 . 我遇到了另一种更简单的解决方案 . 不需要临时文件 .

    在你.select你的数组，而不是 Selection .ExportAsFixedFormat，使用 ActiveSheet .ExportAsFixedFormat . 由于您的工作表在初始.select中处于活动状态，因此ActiveSheet将选择要导出的所有所需工作表 .

    您的代码应如下所示：

    Dim wb As Workbook
Dim srcSht As Worksheet
Dim toPrnt As String

Set wb = ThisWorkbook
Set srcSht = wb.Sheets("print_array")

Dim myArr1() As Variant
    myArr1 = srcSht.Range("myPrintArray")

Dim i As Long
Dim j As String
    For i = LBound(myArr1, 1) To UBound(myArr1, 1)
            j = myArr1(i, 1)

    wb.Sheets(j).Activate
        wb.ActiveSheet.Range("CCB_" & Left(j, 5) & "_Print").Select

    Next i

wb.Sheets(Array("CAT01 - Request for Resource", _
                            "CAT02 - Resource Allocation", _
                            "CAT03 - Product Data Sources", _
                            "CAT04 - Target & Control Cells", _
                            "CAT05 - Matching And Deduping", _
                            "CAT06 - Exclusions", _
                            "CAT07 - Data from other teams", _
                            "CAT08 - Outputs", _
                            "CAT09 - Special Instructions", _
                            "CAT10 - Brief Meeting Sign Off" _
                            )).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDF_FileAndPath, _
Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
    :=False, OpenAfterPublish:=True
    回复于

相关问题