Quản lý in ấn chuyên nghiệp trên Excel
Mẫu bảng quản lý và thiết lập in ấn hàng trăm sheets
DOWNLOADS
Tải file mẫu theo phiên bản tương ứng tại đây
Code mẫu
Option Explicit
Public Sub Print_Reports()
Dim PrintArea As Variant
Dim i As Integer
Dim j As Integer
Dim sht As Long
Dim Orientation As String
Dim NCopies As Integer
Dim PWide As Integer
Dim PTall As Integer
Dim Footer As String
Dim Header As String
Dim Sheets As String
Dim gRow As Integer
Dim gCol As Integer
Dim PaperSize As String
Dim msg As String
Dim tmp As String
Turn off the Automatic Calculation so that it is faster and isn’t as jerky
Application.Calculation = xlCalculationManual
This loads the entire array of the Print_Control page into an array called PrintArea
PrintArea = Worksheets(“Print_Control”).Range(“Print_Control”).Value
This sets up a loop for the No of Total Copies of the Whole report
For j = 1 To [Copies].Value ‘Loop through the No of Copies
This sets up a loop for the to check each line of the Print Control area
For i = 1 To UBound(PrintArea, 1) ‘Loop through the print area
If the Column Status is On print using that line of settings
If UCase(PrintArea(i, 3)) = “ON” Then ‘When On is enabled Print using the settings
Extract the settings from the stored array, row i
Header = PrintArea(i, 2) ‘Set Header variable
Orientation = PrintArea(i, 6) ‘Set Orientation variable
PWide = PrintArea(i, 8 ) ‘Set Pages Wide variable
PTall = PrintArea(i, 9) ‘Set Pages Tall variable
NCopies = PrintArea(i, 10) ‘Set No Copies variable
gRow = PrintArea(i, 11) ‘Set Row Group Expansion
gCol = PrintArea(i, 12) ‘Set Column Group Expansion
Footer = PrintArea(i, 13) ‘Set Footer variable
Check paper sizes against the built in page sizes
If PrintArea(i, 7) = “A4” Then
PaperSize = 9
ElseIf PrintArea(i, 7) = “A3” Then
PaperSize = 8
ElseIf PrintArea(i, 7) = “A5” Then
PaperSize = 11
ElseIf PrintArea(i, 7) = “Legal” Then
PaperSize = 5
ElseIf PrintArea(i, 7) = “Letter” Then
PaperSize = 1
ElseIf PrintArea(i, 7) = “Quarto” Then
PaperSize = 15
ElseIf PrintArea(i, 7) = “Executive” Then
PaperSize = 7
ElseIf PrintArea(i, 7) = “B4” Then
PaperSize = 12
ElseIf PrintArea(i, 7) = “B5” Then
PaperSize = 13
ElseIf PrintArea(i, 7) = “10×14” Then
PaperSize = 16
ElseIf PrintArea(i, 7) = “11×17” Then
PaperSize = 17
ElseIf PrintArea(i, 7) = “Csheet” Then
PaperSize = 24
ElseIf PrintArea(i, 7) = “Dsheet” Then
PaperSize = 25
Else
PaperSize = 9 ‘Defaults to A4
End If
Activate the relevant sheet
This checks that the sheet exists first
tmp = PrintArea(i, 4)
SheetExists(tmp) is a UDF that’s checks if the sheet exists and returns True or False
If Not SheetExists(tmp) Then
msg = “Sheet ‘” + PrintArea(i, 4) + “‘ not found.” + vbCrLf + “Check the sheets Name.”
msg = msg + vbCrLf + vbCrLf + “Processing will continue for remaining sheets.”
tmp = MsgBox(msg, vbExclamation, “Sheet not Found”)
Else
The sheet exists now process
Select the sheet
Application.Sheets(PrintArea(i, 4)).Select
Check if it is a Worksheet or a Chartsheet
If ActiveSheet.Type = -4167 Then ‘Its a worksheet
Turn off screen updating
Application.ScreenUpdating = False
Select the relevnt area of the sheet
ActiveSheet.PageSetup.PrintArea = PrintArea(i, 5) ‘Select the relevent Print Area of the Sheet
Set Outline levels
ActiveSheet.Outline.ShowLevels RowLevels:=gRow, ColumnLevels:=gCol ‘Set Outline Grouping
Apply print settings
With ActiveSheet.PageSetup ‘Set print settings
.PrintTitleRows = “”
.PrintTitleColumns = “”
.LeftHeader = “”
.CenterHeader = Header ‘User Defined Header (Shift to Left or Right as required)
.RightHeader = “”
.LeftFooter = Footer ‘User Defined Footer (Shift to Left or Right as required)
.CenterFooter = “”
.RightFooter = “”
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(1.0)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.1)
.FooterMargin = Application.InchesToPoints(0.3)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Draft = False
.PaperSize = PaperSize ‘ User Defined Paper Size
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = PWide ‘User Defined No Pages Wide
.FitToPagesTall = PTall ‘User Defined No Pages Tall
.PrintErrors = xlPrintErrorsDisplayed
End With
Apply page orientation settings
If Orientation = “L” Then ‘User Defined Page Orientation
ActiveSheet.PageSetup.Orientation = xlLandscape
Else
ActiveSheet.PageSetup.Orientation = xlPortrait
End If
Turn Screen updating back on
Application.ScreenUpdating = True
Finished setting up Worksheet goto the Printing area
Else ‘Its a Chart page
Turn Screen updating off
Application.ScreenUpdating = False
Apply print settings
With ActiveChart.PageSetup
.LeftHeader = “”
.CenterHeader = Header
.RightHeader = “”
.LeftFooter = Footer
.CenterFooter = “”
.RightFooter = “”
.LeftMargin = Application.InchesToPoints(0.1)
.RightMargin = Application.InchesToPoints(0.1)
.TopMargin = Application.InchesToPoints(1#)
.BottomMargin = Application.InchesToPoints(0.4)
.HeaderMargin = Application.InchesToPoints(0.1)
.FooterMargin = Application.InchesToPoints(0.3)
.ChartSize = xlScreenSize
.PrintQuality = 600 ‘Change to 300 for Excel 97-03
.CenterHorizontally = True
.CenterVertically = True
.Orientation = xlLandscape
.Draft = False
.OddAndEvenPagesHeaderFooter = False ‘Removed from 97/03 Ver
.DifferentFirstPageHeaderFooter = False ‘Removed from 97/03 Ver
.EvenPage.LeftHeader.Text = “” ‘Removed from 97/03 Ver
.EvenPage.CenterHeader.Text = “” ‘Removed from 97/03 Ver
.EvenPage.RightHeader.Text = “” ‘Removed from 97/03 Ver
.EvenPage.LeftFooter.Text = “” ‘Removed from 97/03 Ver
.EvenPage.CenterFooter.Text = “” ‘Removed from 97/03 Ver
.EvenPage.RightFooter.Text = “” ‘Removed from 97/03 Ver
.FirstPage.LeftHeader.Text = “” ‘Removed from 97/03 Ver
.FirstPage.CenterHeader.Text = “” ‘Removed from 97/03 Ver
.FirstPage.RightHeader.Text = “” ‘Removed from 97/03 Ver
.FirstPage.LeftFooter.Text = “” ‘Removed from 97/03 Ver
.FirstPage.CenterFooter.Text = “” ‘Removed from 97/03 Ver
.FirstPage.RightFooter.Text = “” ‘Removed from 97/03 Ver
.PaperSize = PaperSize
.FirstPageNumber = xlAutomatic
.BlackAndWhite = False
.Zoom = 100
End With
Turn Screen Updating back on
Application.ScreenUpdating = True
End If
Now Print the active sheet using user defined No. Copies
ActiveWindow.SelectedSheets.PrintOut Copies:=NCopies, Collate:=True
End If
End If
Next i
Next j
Clear PrintArea array, just in case
PrintArea = Null
Turn Auto Calculation back on
Application.Calculation = xlCalculationAutomatic
Go back to the Print Control sheet
Application.Sheets(“Print_Control”).Select
End Sub
= = = = = = = = = = = = = = = = = = =
The SheetExists Function
This is a Function that is used by the Print_Reports subroutine to check if a sheet exists.
= = = = = = = = = = = = = = = = = = =
Function SheetExists(SheetName As String) As Boolean
‘ This function Returns TRUE if the sheet exists in the active workbook
SheetExists = False ‘Set default value of SheetExists
On Error GoTo NoSuchSheet ‘Set error trapping such that if the sheet doesn’t exist it will exit
Check length of sheet name, if the sheet exists it will return a value, otherwise an error
If Len(Sheets(SheetName).Name) > 0 Then
The sheet exists so set SheetExists = True and exit
SheetExists = True
Exit Function
End If
NoSuchSheet:
The sheet doesn’t exists so use default SheetExists = False and exit
End Function
= = = = = = = = = = = = = = = = = = =
The Setup_Print_Control_Named_Formula Subroutine
This is a simple subroutine that sets up the 2 named formula for use the first time a sheet is used.
= = = = = = = = = = = = = = = = = = =
Sub Setup_Print_Control_Named_Formula()
Setup Named Formula “Print_Control” which is the table of settings
ActiveWorkbook.Names.Add Name:=”Print_Control”, RefersToR1C1:= _
“=OFFSET(Print_Control!R4C2,1,,COUNTA(Print_Control!R5C2:R24C2),COUNTA(Print_Control!R4))”
ActiveWorkbook.Names(“Print_Control”).Comment = _
“Used by the Print_Reports Subroutine”
Setup Named Formula “Copies” which is the No of Copies of the Whole Report
ActiveWorkbook.Names.Add Name:=”Copies”, RefersToR1C1:= _
“=Print_Control!R26C13”
ActiveWorkbook.Names(“Copies”).Comment = “Specifies the No. of Copies for the Print_Reports Subroutine”
End Sub
?DienDan.Edu.Vn cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn. https://diendan.edu.vn/
DienDan.Edu.Vn Cám ơn bạn đã quan tâm và rất vui vì bài viết đã đem lại thông tin hữu ích cho bạn.DienDan.Edu.Vn! là một website với tiêu chí chia sẻ thông tin,... Bạn có thể nhận xét, bổ sung hay yêu cầu hướng dẫn liên quan đến bài viết. Vậy nên đề nghị các bạn cũng không quảng cáo trong comment này ngoại trừ trong chính phần tên của bạn.Cám ơn.
