change printer settings

trevorc · #1 07-16-2020, 09:21 PM

Hi All,
I've been trying to change some printer settings and so far have been successful, until needing to change the page size. This is for a small label printer the labels are 100mm x 50mm this keeps defaulting to the printer defaults and i cannot change them permanently for some reason i don't know,.

I can select the correct printer with this line of the code

Code:

Application.ActivePrinter = "\\U2-front-desk\bixolon slp-d420 on Ne05:"

And this to change some settings

Code:

With ActiveSheet.PageSetup

I have seen this from Microsoft

HTML Code:

https://docs.microsoft.com/en-us/office/vba/api/Excel.XlPaperSize

but it doesn't list the size i need to set, anyone know how to add to this list or manually enter one into it?

Any ideas would be helpful thanks

Guessed · #2 07-19-2020, 03:59 PM

Does this thread help?
Choosing paper size (NOT DEFAULT sizes) in excel vba - Stack Overflow

trevorc · #3 07-19-2020, 05:01 PM

Thanks for the reply, but i can't get the line of code below to work.
As shown in the example from the link you provided. Any ideas?
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.

Code:

Sub AAA()
With ActiveSheet.PageSetup
MsgBox PageSetup.PaperSize
End With
Exit Sub

Guessed · #4 07-20-2020, 04:09 PM

The code you would need is
MsgBox ActiveSheet.PageSetup.PaperSize

Did you do the pre-steps?

Quote:

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.

trevorc · #5 07-20-2020, 05:10 PM

Thanks for the update, but it reports 9 for any of the printers i have selected, all using different paper sizes.
Just going with this for now.

Code:

Sub AAA()
    Application.EnableEvents = False
    Select Case ActiveSheet.Name
        Case Is = "Mac Label"
            Application.ActivePrinter = "\\U2-front-desk\bixolon slp-d420 on Ne05:"
        Case Is = "Address Label", "Part Labels"
            Application.ActivePrinter = "ZDesigner GK420t on 9100"
        Case Is = "Repair Labels"
            Application.ActivePrinter = "\\U2-front-desk\bixolon slp-d420 on Ne05:"
        Case Else
            Application.ActivePrinter = "Brother DCP-7065DN Printer on Ne04:"
    End Select
    ActiveSheet.Range("D22") = Application.ActivePrinter
    Application.Dialogs(xlDialogPrint).Show
    Application.EnableEvents = True
    Application.ActivePrinter = "Brother DCP-7065DN Printer on Ne04:"
End Sub

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How do I lock printer paper size in Word docs saved to PDF?	bwofficer	Word	6	08-24-2017 12:30 AM
Can we NAME our own paper size that will show up on the printer?	FearlessStamper	Word	1	09-10-2016 03:23 PM
Exporting a Large Paper Size as a Single PDF Page	JamesEddy	Word	1	01-05-2016 11:23 AM
Custom page size settings	ibclare	Publisher	0	10-27-2014 08:34 PM
how change size font to inches size	kkepo	Word	4	08-28-2012 08:53 PM