Choosing paper size (NOT DEFAULT sizes) in excel vba

Question

I have Brother QL-720NW Label Printer, which I want to print some labels on.

The printer has a roll of width 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.

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.

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:

1: At the .PaperSize = xlPaperUser I get a runtime-error '1004'. Unable to set the PaperSize of the PageSetup class. What is wrong here ?

2: How can I set the paper size to something like 62mm x 50mm ?

3: Even that I define the print area to Range("Img") it still print the whole sheet ?!?

By the way I am totally new to vba, this is my first attempt to use vba.

Solution

Question 1

xlPaperUser is a User-Defined paper size that is assigned a constant value of 256. If this has not been defined, it may throw an error.

Question 2

There is no way to create custom paper sizes in Excel, however you 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.

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 = xlPaperUser in your macro to .PaperSize = & whatever the constant is that you just found.

Question 3

.PrintArea takes a string input, not a range. Change your line to ActiveSheet.PageSetup.PrintArea = Range("Img").Address and it should work.

Comments

[Nawed Nabi Zada]

I assume your answer is correct, therefor accepted. But I guess I will never know because I decided to do the job in C# and make a DLL for the macro.

[zeta]

xlPaperUser, how is this defined? It seems like it might be set in the printer preferences page size?

[TheEngineer]

@zeta My understanding is that xlPaperUser is defined by the printer, which is why you cannot set it using VBA.

[Nawed Nabi Zada]

I didn't really write it in the question, but the whole idea was to make an automated process of getting some labels (with QR codes) printed based on excel sheets. All dialogs should be avoided. In case of dialogs I would just use the default printing dialog.