vba 在excel vba中选择纸张尺寸（非默认尺寸）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/27939513/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Choosing paper size (NOT DEFAULT sizes) in excel vba
提问by Nawed Nabi Zada
I have Brother QL-720NW Label Printer, which I want to print some labels on.
我有 Brother QL-720NW 标签打印机，我想在上面打印一些标签。
The printer has a roll of width 62mm
打印机有一卷宽度为62mm
When I try to print something to it, I need to setup the page, and define page size. If the page size is incorrect (width more than 62mm) the printer won't print anything.
当我尝试向其打印某些内容时，我需要设置页面并定义页面大小。如果页面尺寸不正确（宽度超过 62 毫米），打印机将不会打印任何内容。
Now my problem is that I am using excel with macros to send some data to the printer. I know there is some predefined page sizes (http://msdn.microsoft.com/en-us/library/office/ff834612%28v=office.15%29.aspx) which can be used, but in my case all of them are too big for this purpose.
现在我的问题是我使用带有宏的 excel 向打印机发送一些数据。我知道有一些预定义的页面大小（http://msdn.microsoft.com/en-us/library/office/ff834612%28v=office.15%29.aspx）可以使用，但在我的情况下它们对于这个目的来说太大了。
Here a sample of the code I have so far:
这是我到目前为止的代码示例：
Sub CreateTestCode()
' setting printer
Dim objPrinter As String
objPrinter = ActivePrinter
ActiveSheet.PageSetup.PrintArea = Range("Img")
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
.PrintHeadings = False
.PrintGridlines = False
.RightMargin = Application.InchesToPoints(0.39)
.LeftMargin = Application.InchesToPoints(0.39)
.TopMargin = Application.InchesToPoints(0.39)
.BottomMargin = Application.InchesToPoints(0.39)
.PaperSize = xlPaperUser
.Orientation = xlLandscape
.Draft = False
End With
Dim printerName As String
printerName = "BrotherQL720NW Labelprinter on XYZ"
ActiveSheet.PrintOut Preview:=True, ActivePrinter:=printerName
ActivePrinter = objPrinter
End Sub
Now I have 3 questions:
现在我有3个问题：
1: At the .PaperSize = xlPaperUser I get a runtime-error '1004'. Unable to set the PaperSize of the PageSetup class. What is wrong here ?
1：在 .PaperSize = xlPaperUser 处，我收到运行时错误“1004”。无法设置 PageSetup 类的 PaperSize。这里有什么问题？
2: How can I set the paper size to something like 62mm x 50mm ?
2：如何将纸张尺寸设置为 62mm x 50mm 之类的？
3: Even that I define the print area to Range("Img") it still print the whole sheet ?!?
3：即使我将打印区域定义为 Range("Img") 它仍然打印整张纸？！？
By the way I am totally new to vba, this is my first attempt to use vba.
顺便说一下，我对 vba 完全陌生，这是我第一次尝试使用 vba。
回答by TheEngineer
Question 1
问题 1
xlPaperUseris a User-Defined paper size that is assigned a constant value of 256. If this has not been defined, it may throw an error.
xlPaperUser是用户定义的纸张尺寸，被分配了一个常数值 256。如果尚未定义，则可能会引发错误。
Question 2
问题2
There is no way to create custom paper sizes in Excel, howeveryou can create custom paper sizes on many printers. Under Page Setup, click the Options button. This will bring up the printer properties dialog box. Change your paper size to a custom size using this dialog box and click OK.
无法在 Excel 中创建自定义纸张尺寸，但您可以在许多打印机上创建自定义纸张尺寸。在页面设置下，单击选项按钮。这将打开打印机属性对话框。使用此对话框将纸张尺寸更改为自定义尺寸，然后单击确定。
Then in Excel run this: MsgBox PageSetup.PaperSize. This will give you the new constant value assigned to that paper size in Excel. Then change .PaperSize = xlPaperUserin your macro to .PaperSize =& whatever the constant is that you just found.
然后在Excel中运行以下命令：MsgBox PageSetup.PaperSize。这将为您提供分配给 Excel 中该纸张大小的新常量值。然后将.PaperSize = xlPaperUser您的宏更改为.PaperSize =& 任何您刚刚找到的常量。
Question 3
问题 3
.PrintAreatakes a string input, not a range. Change your line to ActiveSheet.PageSetup.PrintArea = Range("Img").Addressand it should work.
.PrintArea接受一个字符串输入，而不是一个范围。将您的线路更改为ActiveSheet.PageSetup.PrintArea = Range("Img").Address，它应该可以工作。
回答by drunkenvash
I would like to add to this.
You can also add this line Application.Dialogs(xlDialogPageSetup).Show
我想补充一点。
你也可以添加这一行Application.Dialogs(xlDialogPageSetup).Show
An Example would be:
一个例子是：
Sub printGraphs()
Application.Dialogs(xlDialogPrinterSetup).Show
Application.Dialogs(xlDialogPageSetup).Show
With ActiveSheet.PageSetup
.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)
.PrintQuality = 1200
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWorkbook.PrintOut From:=1, To:=3, Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
End Sub
This prompts the user to choose their page size based on printer selected. I use this to print a workbook with multiple tabs all at once, and only the tabs I want.
这会提示用户根据所选的打印机选择页面大小。我使用它一次打印包含多个选项卡的工作簿，并且只打印我想要的选项卡。
