近日,有读者后台私信,能否多工作簿统一设置打印区域?今天在这里分享如何用VBA实现该功能。
VBA实现思路
1.选择多个工作簿所在的文件路径(FileDialog对象);
2.遍历每个工作簿的所有工作表(For循环);
3.若工作表可见,则设置该工作表的打印区域(PageSetup对象);
4.将工作簿导出为PDF文件(ExportAsFixedFormat对象),并保存在同一路径下。
VBA代码
Sub 多工作簿统一设置打印区域并导出为PDF()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "请选择工作簿所在的文件夹路径"
If .Show Then
Dim pathSelected As String, fn As String
pathSelected = .SelectedItems(1)
'工作簿文件类型可以是xlsx/xls/xlsm等
fn = Dir(pathSelected & "\*.xls*")
Dim wb As Workbook, sht As Worksheet
Dim exPDF As String
Do While fn <> ""
Set wb = Workbooks.Open(pathSelected & "\" & fn, False)
exPDF = pathSelected & "\" & Split(fn, ".")(0) & ".pdf"
For Each sht In wb.Sheets
If sht.Visible = xlSheetVisible Then
'统一设置打印区域,读者根据实际情况设置
sht.PageSetup.PrintArea = "$A$1:$A$20"
End If
Next
'将工作簿导出为PDF文件
wb.ExportAsFixedFormat Type:=xlTypePDF, Filename:=exPDF, Quality:=xlQualityStandard
wb.Save: wb.Close
fn = Dir
Loop
End If
End With
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
其实,统一设置打印区域,适用范围有限。如何根据每个工作簿的实际情况,动态设置多个工作簿的打印区域,这也是后续代码可以进一步优化的地方。
技术交流,软件开发,欢迎微信沟通: