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.

Answer 1

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.

Answer 2

I would like to add to this.
You can also add this line 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.