excel - 如何使用 vbs 导出 excel 打印文件，页码在页脚中？

问题描述

我有一个将电子表格打印到 pdf 文件的宏。

页码和页数的左右页脚值未导出

代替：

1                                   12

我明白了：

&R                                  &L

传递的参数是&P和&N，所以&R和&L完全是个谜。

这是我的代码，（我很抱歉没有声明......这只是测试一个想法......）。大部分代码只是一个宏录制。记录所有页面设置和页码信息。我的贡献是底部的声明、循环结构和文件导出代码。

除了缺少页码外，这一切都有效。

Sub TEST()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
COP = ActiveSheet.PivotTables("PivotTable3").PivotFields("SEC USER DEPARTMENT").PivotItems().Count
For PT = 1 To COP
    With ActiveSheet.PivotTables("PivotTable3").PivotFields("SEC USER DEPARTMENT")
        .CurrentPage = .PivotItems(PT).Value
    End With
    SEC_USER_DEPARTMENT = Trim(Cells(1, 2).Value)
    DT = "121519"
    HOME = "C:\Gunhead\_ANALYTICS\MSP Reports\"
    FNAME1 = "Passport SECU "
    ActiveSheet.PivotTables("PivotTable3").PivotFields("WORKSTATION ID"). _
    ShowDetail = True
    LASTROW = Cells(Rows.Count, 1).End(xlUp).Row
    LASTCOL = Cells(6, Columns.Count).End(xlToLeft).Column
    Range(Cells(1, 1), Cells(LASTROW, LASTCOL)).Select
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
    End With
    Selection.Rows.AutoFit
    Selection.Columns.AutoFit
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$6"
        .PrintTitleColumns = ""
    End With
    Application.PrintCommunication = True
    ActiveSheet.PageSetup.PrintArea = ""
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = ""
        .RightHeader = ""
        **.LeftFooter = "&P"**
        .CenterFooter = ""
        **.RightFooter = "&N"**
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = False
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    Sheets("PIVOT").Range(Cells(1, 1), Cells(LASTROW, LASTCOL)).Select
    ActiveSheet.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=HOME & FNAME1 & "-" & SEC_USER_DEPARTMENT & " - " & DT & ".PDF", _
    Quality:=xlQualityStandard, _
    OpenAfterPublish:=True
Next
End Sub

标签： excelpdfvbscript

excel - 如何使用 vbs 导出 excel 打印文件，页码在页脚中？

问题描述

解决方案

推荐阅读