Excel VBA将多张表格导出为PDF [英] Excel VBA Export Multiple sheets to PDF
问题描述
我一直在尝试将多个工作表上的命名范围导出为单个pdf。最初，它似乎工作正常，然后我注意到，将各种工作表加载到数组中时，所选范围将发生变化。
使用 Selection.ExportAsFixedFormat 这将导出所选单元格。因此，我有一个宏循环遍历所需的工作表，选择各种范围，然后为所有工作表创建一个数组，以允许导出为单个pdf。
Dim wb As Workbook
Dim srcSht As Worksheet
Dim toPrnt As String
设置wb = ThisWorkbook
设置srcSht = wb.Sheets （print_array）
Dim myArr1（）As Variant
myArr1 = srcSht.Range（myPrintArray）
Dim i As Long
Dim j As String
对于i = LBound（myArr1，1）到UBound（myArr1，1）
j = myArr1（i，1）
wb.Sheets（j）.Activate
wb.ActiveSheet.Range（CCB_& Left（j，5）&_Print）。选择
Next i
wb.Sheets （Array（CAT01 - 资源请求，_
CAT02 - 资源分配，_
CAT03 - 产品数据源，_
CAT04 - 目标和控制单元 ，_
CAT05 - 匹配和重复数据删除，_
CAT06 - 除外，_
CAT07 - 其他团队的数据，_
CAT08 - 输出，_
选择
Selection.ExportAsFixedFormat类型：= xlTypePDF，文件名：= PDF_FileAndPath，_
质量：= xlQualityStandard，IncludeDocProperties：= True，IgnorePrintAreas _
：= False，OpenAfterPublish：= True
当逐步执行代码时，一切都将进行计划，直到创建工作表数组，此时所选范围更改。
我有也尝试使用PageSetup，但结果是一样的。
使用ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = Fals e
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintArea = Range（CCB_&左（j，5）& _Print）。地址
结束
在此论坛中查看了一些类似的帖子我还在亏损。
任何人都可以了解创建数组时所选范围变化的原因，还有其他可能有帮助的建议吗？ >
非常感谢
我设法通过复制所选范围来解决我的问题到一个临时文件，然后从那里导出。完整的解决方案看起来像这样...
Dim wb As Workbook
Dim srcSht As Worksheet
Dim设置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
使用ActiveSheet.PageSetup
.Orientation = xlLandscape
。 PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintArea = Range（CCB_& Left（j，5）& _Print）。地址
结束
下一步i
wb.Sheets（Array（CAT01 - 请求资源，_
CAT02 - 资源分配，_
CAT03 - 产品D ata Sources，_
CAT04 - Target&控制单元，_
CAT05 - 匹配和重复数据删除，_
CAT06 - 排除，_
CAT07 - 其他团队的数据，_
CAT08 - 输出，_
CAT09 - 特别说明，_
CAT10 - 简要会议注销_
））。copy
使用ActiveWorkbook
.Save
tempFile = .FullName
.ExportAsFixedFormat类型：= xlTypePDF，文件名：= PDF_FileAndPath，_
质量：= xlQualityStandard，IncludeDocProperties：= True， IgnorePrintAreas _
：= False，OpenAfterPublish：= True
.Close
结束
杀死tempFile
结束如果
我希望这有助于将来的人。
I have been trying to export a named range on multiple worksheets to a single pdf. Initially, it seemed to work fine, then I noticed the the selected range would change when loading the various sheets into an array.
It is my understanding by using Selection.ExportAsFixedFormat this would export the selected cells only. Therefore, I have a macro to loop through the required sheets, select the various ranges need then create an array for all the sheets to allow exporting to a single 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
When stepping through the code, everything goes to plan right up to creating the sheets array, at this point the selected ranges change.
I have also tried using PageSetup, but the result is the same.
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.PaperSize = xlPaperA4
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 1
.PrintArea = Range("CCB_" & Left(j, 5) & "_Print").Address
End With
After reviewing a few similar post in this forum, I am still at a loss.
Can anyone shed light on why the selected range changes when creating the array or, have any other suggestions that may help?
Many thanks
I managed to solve my problem by copying the selected ranges to a temporary file then exporting from there. The complete solution looks like this...
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
I hope this helps someone in the future.
Thanks.
这篇关于Excel VBA将多张表格导出为PDF的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
