涉及对于Excel Report的再处理,而原系统无法很好做到这点,于是通过宏做处理。
Sub COQC宏()
'
' COQC宏 宏
' coqc Excel格式处理
'
'
For Each wk In Workbooks
If Left(wk.Name, 5) = "COQC_" Then
For Each sh In wk.Sheets
'操作sheet
'去除保护
sh.Unprotect
'分页重置
sh.ResetAllPageBreaks
'每隔47rows分页
For i = 48 To sh.UsedRange.Rows.Count Step 47
sh.HPageBreaks.Add Before:=Cells(i, 1)
Next
'设置页眉
sh.PageSetup.LeftHeader = "Page &P of &N"
sh.PageSetup.RightHeader = "&D"
'设置页边距
sh.PageSetup.LeftMargin = Application.InchesToPoints(0.47244094488189)
sh.PageSetup.RightMargin = Application.InchesToPoints(0.47244094488189)
sh.PageSetup.TopMargin = Application.InchesToPoints(1.2992125984252)
sh.PageSetup.BottomMargin = Application.InchesToPoints(0.748031496062992)
sh.PageSetup.HeaderMargin = Application.InchesToPoints(0.31496062992126)
sh.PageSetup.FooterMargin = Application.InchesToPoints(0.31496062992126)
'打印设置
'sh.PageSetup.AlignMarginsHeaderFooter = False
'打印区域设置
'sh.PageSetup.PrintArea = "$A$1:$I$sh.UsedRange.Rows.Count"
'去除网格线
sh.PageSetup.PrintHeadings = False
sh.PageSetup.PrintGridlines = False
sh.PageSetup.PrintComments = xlPrintNoComments
sh.PageSetup.CenterHorizontally = False
sh.PageSetup.CenterVertically = False
sh.PageSetup.Orientation = xlPortrait
sh.PageSetup.Draft = False
sh.PageSetup.PaperSize = xlPaperA4
sh.PageSetup.FirstPageNumber = xlAutomatic
sh.PageSetup.BlackAndWhite = False
sh.PageSetup.Zoom = 100
sh.PageSetup.PrintErrors = xlPrintErrorsDisplayed
sh.PageSetup.OddAndEvenPagesHeaderFooter = False
sh.PageSetup.DifferentFirstPageHeaderFooter = False
sh.PageSetup.ScaleWithDocHeaderFooter = True
sh.PageSetup.AlignMarginsHeaderFooter = True
sh.PageSetup.PrintQuality = 600
Next
MsgBox (wk.Name + " reformat successfully !")
End If
Next
End Sub
需注意的是,如果通过宏作个人工作簿,自定义快捷功能设置,因为active workbook为personal所在,于是获取当前所有workbooks,对于个人所需要处理的一类进行处理。纯粹为同事打印所需