Set printer Excel vba macro

Question

Im trying to print through a macro to a shared printer on a colleagues PC.

I have tried the following but to no avail

Sub Tag()
ActiveCell.Resize(1, 1).Copy Worksheets("Sheet1").Range("A1")

ActiveCell.Offset(, 1).Resize(1, 1).Copy Worksheets("Sheet1").Range("A2")

'Worksheet("Sheet1").Range("A1:A2").Borders.LineStyle = xlNone
Sheets("Sheet1").Select
Range("A1:A2").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

Worksheets("Sheet1").Range("A2").WrapText = True
Worksheets("Sheet1").Range("A2").Font.Size = 44
Worksheets("Sheet1").Range("A2").ShrinkToFit = True

Application.ActivePrinter = "Microsoft XPS Document Writer on XPSPort"

'Worksheets("Sheet1").PrintOut ActivePrinter = "DYMO LabelWriter 450 (Copy2) on JCSP55J"


End Sub

the JCSP55J is the colleagues network name.

any ideas how to solve this please?

Solution

You can add the ActivePrinter as an argument to PrintOut.

For example:

Worksheets("Sheet1").PrintOut ActivePrinter:="DYMO LabelWriter 450 (Copy2) on JCSP55J"

Comments

[Máté Juhász]

Try to record a macro. It'll show the correct name and syntax.

[Ryanagray]

I have tried to record the macro but it does not show activeprinter,

[Ryanagray]

HI, this still gives the same error, Run-Time error '1004': Method 'ActivePrinter' of object'_Global' failed

[Jonno]

@Ryanagray That occurs if you're using the wrong name. Make sure you're using the name from the first box in "Printing Preferences" exactly as it occurs. i.imgur.com/KhBkTdT.jpg

[Ryanagray]

Thanks for the reply, I have tried this and still the same, does it make a difference it been a shared printer on another PC usb port

[Jonno]

@Ryanagray Shouldn't do, it's only using the name to interface. Perhaps rename the printer device to something simpler to be sure, just "DYMO" or something

[Ryanagray]

I have tried with other printers and still the same, I have edited the question above to include all the macro code, any more thoughts please?

[Jonno]

@Ryanagray Hm. Try without the " on XXXXX" bit. Thinking about it, that might just be the port name suffixed to it

[Ryanagray]

Still no joy, even with my other network printer