VBA Print Sheet crashes.

[And180y]

I am using the code below to print to the specified printer however
it is giving an error as attached. Grateful for any help.

Sub PrintLabel()
Dim sCurrentPrinter As String
Const MyPrinter As String = ""
sCurrentPrinter = Application.ActivePrinter
Application.ActivePrinter = MyPrinter
ActiveSheet.PrintOut '
Application.ActivePrinter = sCurrentPrinter
End Sub

[Logit]

.
You can use this to replace your entire macro :

ActiveSheet.PrintOut

https://docs.microsoft.com/en-us/off...heets.printout

[And180y]

Thanks mate. I was trying to force the sheet to use only that printer though?

[Logit]

.
Does this help ?

http://https://superuser.com/questio...xcel-vba-macro

[And180y]

Thanks for replying. :-)

Not really mate. Basically there are two printers plugged into the laptop.
One an A4 and the label printer as above. The idea was that when printing a label (designed in Excel) a button could be pressed to
in the sheet to send the label to the label printer without having to select it from the print menu.

[jolivanes]

This works for me on my desktop.

Sub Print_To_LinePrinter()
Dim STDprinter As String
STDprinter = Application.ActivePrinter
Application.ActivePrinter = "HP Officejet Pro 8600 (Network) on Ne03:"
With Sheets("Blah Blah").PageSetup
    .PrintArea = Range("A1:N36").Address
    .Orientation = xlLandscape
End With
Sheets("Blah Blah").PrintOut
Application.ActivePrinter = STDprinter
End Sub

When I need to know the different printers, I go to
Control Panel - Hardware and Sound - Devices and Printers
and set the printer in question as default printer and run this

Sub Get_Printer_Name()
    MsgBox Application.ActivePrinter
End Sub

In my case I get
"Microsoft Print to PDF on Ne01:"
for saving to PDF
and
"HP Officejet Pro 8600 (Network) on Ne03:"
when I have the line printer as default.

I am sure you must be able to get a list of all available printers in excel but this works for me.

[jolivanes]

I found this by Rory Archibald.

Sub PrintersAndPorts()

'This works with Windows 2000 and up

   Dim Arr               As Variant
   Dim Device            As Variant
   Dim Devices           As Variant
   Dim msg               As String
   Dim RegObj            As Object
   Dim RegValue          As String
   Const HKEY_CURRENT_USER = &H80000001

   Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr

   For Each Device In Devices
      RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
      msg = msg & Device & " on " & Split(RegValue, ",")(1) & vbCrLf
   Next

   MsgBox msg, vbInformation, "Printers and Ports"

End Sub

If you want the result in Column A, starting at cell A2, as Leith Ross did with this.

Sub PrintersAndPorts()

 'This works with Windows 2000 and up
 
  Dim Arr As Variant
  Dim Device As Variant
  Dim Devices As Variant
  Dim Msg As String
  Dim RegObj As Object
  Dim RegValue As String
  Dim R As Long
  Dim Rng As Range
  Dim Wks As Worksheet
  
  Const HKEY_CURRENT_USER = &H80000001
  
    Set Wks = Worksheets("Sheet1")
    Set Rng = Wks.Range("A2")
       
    Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
    RegObj.enumvalues HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Devices, Arr
    
      For Each Device In Devices
          RegObj.getstringvalue HKEY_CURRENT_USER, "Software\Microsoft\Windows NT\CurrentVersion\Devices", Device, RegValue
          Rng.Offset(R, 0) = Device & " on " & Split(RegValue, ",")(1)
          R = R + 1
      Next
    
End Sub

[And180y]

# Post 6 worked once and gives me the same error as above thereafter. This is the actual error line in the code; Application.ActivePrinter = "Samsung ML1660 Series (Usb 01) on Ne02:"

Edit. Post #7 just displayed a load of text in the actual label.

[jolivanes]

Re: "a load of text in the actual label"
Maybe an example of the "load of text" might help and what do you consider a "label"? The Message Box as it is referred to in the code I assume.

So show us the code after you changed it.

[And180y]

Thanks Jolivanes :-) I just read the 2nd Macro again, it is set to send the result to A2 which in my haste I changed to A2:G22. The code was right, I was stoopid. Sorry.

I have a label designed which sits in the range A22:G22 this is the code that ran once.

Sub Print_To_LinePrinter()
Dim STDprinter As String
STDprinter = Application.ActivePrinter
Application.ActivePrinter = "Samsung ML1660 Series (Usb 01) on Ne02:"
With Sheets("Label").PageSetup
    .PrintArea = Range("A2:G22").Address
    .Orientation = xlLandscape
End With
Sheets("Label").PrintOut
Application.ActivePrinter = STDprinter
End Sub

[jolivanes]

So does it all work now?


In cases like this, the reference to A2 should be changed to a range where you have free rows below that cell or just run it in a new and empty temp sheet.
All you need it for is to have a data range with all your printers so you can change your code accordingly.

[And180y]

No still not working. :-(

[jolivanes]

What is the result from the 2nd macro in Post #7?
Run it on an empty sheet.

[And180y]

Hi Jolvanes, thanks for sticking with this.

Send To OneNote 2007 on Ne01:
Samsung ML-1660 Series (USB001) on Ne02:
PDFCreator on Ne03:
Microsoft XPS Document Writer on Ne04:
Microsoft Print to PDF on Ne05:
Microsoft Office Document Image Writer on Ne06:
HP3A01E0 (HP Photosmart C4500 series) on Ne06:
Fax on Ne07:
Brother QL-570 on Ne09:
Samsung Universal Print Driver 3 on Ne01:
ZDesigner LP 2844-Z on Ne00:
EPSON ET-2650 Series on Ne08:
Send To OneNote on nul:
OneNote on Ne10:

[jolivanes]

In Post #10, you have this (Usb 01)

Application.ActivePrinter = "Samsung ML1660 Series (Usb 01) on Ne02:"

However, the result from running the code has this (USB001)

"Samsung ML-1660 Series (USB001) on Ne02:"

If you change your code to the proper line, exactly as above, does that work.
Use a copy and paste so you won't type wrong

[jolivanes]

I guess we answered at the same time.
You've got it licked at that's what count.
Good Luck and stay safe

[And180y]

Jolivanes, I removed the " " marks from around the printer, the line of code went red but when I put them back in it now works perfectly.
Not sure what happened but thank you so much for taking the time to help. :-)