提问人：Nazirkulov Botir 提问时间：11/7/2023 最后编辑：Nazirkulov Botir 更新时间：11/7/2023 访问量：62
VBA 宏将选定的工作表导出为单独的 pdf 文件
VBA Macros to export selected sheets into seperate pdf files
问：
我需要将工作簿中选定的工作表导出为单独的 pdf 文件。但是下面的代码并没有完全做到这一点，它的作用是将整个工作表导出为一个 pdf，并使用不同的工作表名称复制相同的 pdf。
有什么建议可以使代码将每个选定的工作表导出为单独的pdf吗？
Sub ExportAsPDF()
Dim Folder_Path As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Folder path"
If .Show = -1 Then Folder_Path = .SelectedItems(1)
End With
If Folder_Path = "" Then Exit Sub
Dim sh As Worksheet
For Each sh In ActiveWindow.SelectedSheets
sh.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & Range("RD").Text & " " & sh.Name & " 2024.pdf"
Next
MsgBox "Done"
End Sub
答：
1赞
CDP1802
11/7/2023
#1
Option Explicit
Sub ExportAsPDF()
Dim Folder_Path As String
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Select Folder path"
If .Show = -1 Then Folder_Path = .SelectedItems(1)
End With
If Folder_Path = "" Then Exit Sub
Dim sh As Worksheet, n As Long
For Each sh In ActiveWindow.SelectedSheets
sh.Activate
sh.Select
ActiveSheet.ExportAsFixedFormat xlTypePDF, _
Folder_Path & Application.PathSeparator & Range("RD").Text & " " & sh.Name & " 2024.pdf", _
IgnorePrintAreas:=True
n = n + 1
Next
MsgBox n & " Sheets Exported"
End Sub
评论
0赞
Ron Rosenfeld
11/7/2023
我认为你不需要这个声明。 单独工作正常。sh.Activatesh.Select
0赞
Lord-JulianXLII
11/7/2023
我知道您的解决方案有效 - 但为什么不更改我们正在循环浏览的集合？- 循环循环的集合是否在循环开始时创建的重复项中循环，或者我在哪里遗漏了某些内容？sh.Select
1赞
CDP1802
11/7/2023
@Lord 是的，迭代器的参数类似于 ByValue 而不是 ByRef。
0赞
Nazirkulov Botir
11/7/2023
哇，它奏效了，非常感谢！它适用于 sh。单独选择就好了。
0赞
Lord-JulianXLII
11/7/2023
#2
Dim sh As Worksheet
Dim selectedWorksheets As New Collection
For Each sh In ActiveWindow.SelectedSheets
selectedWorksheets.Add sh
Next sh
For Each sh In worksheets
sh.Select
sh.ExportAsFixedFormat xlTypePDF, Folder_Path & Application.PathSeparator & sh.Name & " 2024.pdf"
Next sh
'// The following part resets the Selection to what it was at the start of this procedure (remove if not needed)
Dim i As Long
Dim worksheetNames As Variant
i = 1
ReDim worksheetNames(1 To selectedWorksheets.Count)
For Each sh In selectedWorksheets
worksheetNames(i) = sh.Name
i = i + 1
Next sh
ThisWorkbook.worksheets(worksheetNames).Select
MsgBox "Done"
End Sub
评论
Selectsh.SelectFor Each...