vba Excel 宏：Application.Printcommunication = True（导致错误）

Excel Macro: Application.Printcommunication = True (results in error)

提问by user2904258

Since I'm not totally familiar with macros, I recorded a macro to auto-format a report in Excel 2010 that I send out to people every week. Part of the macro adjusts the Page Setup properties which includes changing the layout to Landscape and Legal size. I can run it just fine on my computer because my printer has Legal size paper as one of the defaults. However, some people have to manually adjust their printer before printing on Legal Size paper and they are getting an error. The error returned is: Run-time error '1004': Method 'PrintCommunication' of object '_Application' failed

由于我不完全熟悉宏，因此我录制了一个宏，用于在 Excel 2010 中自动格式化我每周发送给人们的报告。宏的一部分调整页面设置属性，包括将布局更改为横向和合法大小。我可以在我的计算机上很好地运行它，因为我的打印机将 Legal 尺寸的纸张作为默认设置之一。但是，有些人在使用 Legal Size 纸张打印之前必须手动调整他们的打印机，并且会出现错误。返回的错误是：运行时错误'1004'：对象'_Application'的方法'PrintCommunication'失败

When I debug it highlights the Application.Printcommunication = True line. When I change the code to Application.PrintCommunication = False the macro doesn't fully format it the way it's built (e.g., Zoom stays at 100%). I thought a workaround might be to change their default printer to Nitro Pro (similar to Adobe Pro) prior to running the macro, but the error still persists. Any feedback is greatly appreciated!

当我调试时，它会突出显示 Application.Printcommunication = True 行。当我将代码更改为 Application.PrintCommunication = False 时，宏并没有按照它的构建方式完全格式化它（例如，缩放保持在 100%）。我认为一种解决方法可能是在运行宏之前将他们的默认打印机更改为 Nitro Pro（类似于 Adob​​e Pro），但错误仍然存​​在。非常感谢任何反馈！

Sub Auto_Open()
'
' Macro2 Macro
'

'
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ":"
        .LeftMargin = Application.InchesToPoints(0.17)
        .RightMargin = Application.InchesToPoints(0.17)
        .TopMargin = Application.InchesToPoints(0.62)
        .BottomMargin = Application.InchesToPoints(0.48)
        .HeaderMargin = Application.InchesToPoints(0.17)
        .FooterMargin = Application.InchesToPoints(0.17)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 1200
        .CenterHorizontally = True
        .CenterVertically = False
        .Orientation = xlLandscape
        .Draft = False
        .PaperSize = xlPaperLegal
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 60
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    Application.PrintCommunication = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("N2:O2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight2
        .TintAndShade = 0.599993896298105
        .PatternTintAndShade = 0
    End With
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlCenter
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("L1").Select
    Columns("B:B").ColumnWidth = 10.86
    Columns("D:D").ColumnWidth = 18.86
    Columns("E:E").ColumnWidth = 13.43
    Columns("F:F").ColumnWidth = 11.29
    Columns("F:F").ColumnWidth = 19.29
    Columns("G:G").EntireColumn.AutoFit
    Range("H1").Select
    ActiveWindow.SmallScroll Down:=-33
    Range("I1").Select
    Range("G1").Select
    Columns("H:H").ColumnWidth = 13
    Columns("I:I").ColumnWidth = 18.71
    Columns("J:J").ColumnWidth = 19.86
    Columns("K:K").ColumnWidth = 13.57
    Columns("L:L").ColumnWidth = 11
    Columns("L:L").ColumnWidth = 11.43
    Columns("M:M").ColumnWidth = 12.71
    Columns("M:M").ColumnWidth = 15.86
    Columns("N:N").ColumnWidth = 41.86
    Columns("O:O").ColumnWidth = 18.57
    Columns("O:O").ColumnWidth = 28.86
    Columns("O:O").ColumnWidth = 42
    Columns("L:L").ColumnWidth = 12.71
    Range("K2:L2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.NumberFormat = "$#,##0"
    ActiveWindow.SmallScroll Down:=-30
    Columns("A:A").Select
    Selection.EntireColumn.Hidden = True
    Rows("1:1000").EntireRow.AutoFit
    Range("B1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    ActiveWindow.Zoom = 75
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Range("E2").Select
    ActiveWindow.SmallScroll Down:=-6
    ActiveWindow.LargeScroll ToRight:=-1
    With ActiveWindow
        .SplitColumn = 0
        .SplitRow = 1
    End With
    ActiveWindow.FreezePanes = True
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""-,Bold""&12Weekly Staffing Summary Request &D"
        .RightHeader = ""
        .LeftFooter = "&D"
        .CenterFooter = "&P"
        .RightFooter = "&F"
    End With

End Sub

回答by user2904258

The line .PrintQuality = 1200pulled in from the recorded macro and was causing the error. Removed that line and worked regardless of the printer and even worked with Nitro PDF as the default printer.

该行.PrintQuality = 1200从录制的宏中拉入并导致错误。删除了那条线，不管打印机如何工作，甚至使用 Nitro PDF 作为默认打印机。

回答by Zeth

Somewhat late reply obviously, but today I had the same error. I problem was that my computer couldn't contact the default printer.

显然有些晚回复，但今天我遇到了同样的错误。我的问题是我的电脑无法联系默认打印机。

I changed it to only a PDF printer which is working internally in the computer, and then everything worked OK!

我将其更改为仅在计算机内部工作的 PDF 打印机，然后一切正常！

回答by Picasso

The problem here is that your printer settings (nothing to do with the macro) do not allow the macro to communicate with the printer. When setting printer settings, communication is required. Go into your printer settings and play around with the settings, I would imagine you simply need to "enable bidirectionally communication" and the problem will dissappear.

这里的问题是您的打印机设置（与宏无关）不允许宏与打印机通信。设置打印机设置时，需要通信。进入您的打印机设置并调整设置，我想您只需要“启用双向通信”，问题就会消失。

回答by user8074182

Add this line at the top & every thing will be fine:

在顶部添加这一行，一切都会好起来的：

On Error Resume Next

回答by D. O.

You can use "on error resume next" like this :

您可以像这样使用“下一个错误恢复”：

On Error Resume Next
Application.PrintCommunication = False
Err.Clear

and then :

进而 ：

On Error Resume Next
Application.PrintCommunication = True
Err.Clear

It is not necessary to turn off PrintCommunication if you want only to make a page setup. But the difference can be seen in the execution time : the execution is faster with turning off the printer communication.

如果您只想进行页面设置，则无需关闭 PrintCommunication。但是在执行时间上可以看出不同：关闭打印机通信后执行速度更快。

I have Excel 2010, and sometime PrintCommunication works fine, sometime not. I did not figure out why until now!

我有 Excel 2010，有时 PrintCommunication 工作正常，有时则不行。直到现在我才弄明白为什么！