How do I send a ZPL string to a named printer in Excel VBA to print a label?

I have an Excel worksheet with a Print Label button. I need to send a ZPL string to a Zebra network printer. The printer is a named printer on the computer. I have to change from the default printer, send the ZPL string to print the label and change back to the default printer.

How do I send the ZPL string to the named printer in Excel VBA to print a label and select the printer?
Who is Participating?
zorvek (Kevin Jones)Connect With a Mentor ConsultantCommented:
The code below illustrates how to force all printouts to go to a specific network printer. The code sets the Application.ActivePrinter property using the network printer name derived from the SetActivePrinter function. After printing is complete it restores the active printer property.

Add the code below to the ThisWorkbook code module.

[Begin Code Segment]

Public Function SetActivePrinter( _
      ByVal PrinterDisplayName As String _
   ) As String

' Set the Application.ActivePrinter property using the user friendly printer
' name and return the current printer name.
' Syntax
' SetActivePrinter(PrinterDisplayName)
' PrinterDisplayName - The user friendly printer name for the printer. Network
' printers are usually in the form "\\server\printer". The server and printer
' are displayed in the Printers folder as "printer on server".
   Dim Tokens As Variant
   Dim LocalOn As String
   Dim Registry As Object
   Dim Value As String
   Dim Port As String
   ' Get the current printer
   SetActivePrinter = Application.ActivePrinter
   ' Get the local version of "on"
   Tokens = Split(SetActivePrinter)
   LocalOn = Tokens(UBound(Tokens) - 1)
   ' Reformat the printer name if necessary
   If PrinterDisplayName Like "* " & LocalOn & " *" Then
      Tokens = Split(PrinterDisplayName)
      PrinterDisplayName = "\\" & Tokens(2) & "\" & Tokens(0)
   End If
   ' Get the printer's port from the CURRENT_USER hive of the registry
   Set Registry = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
   On Error Resume Next
   Registry.GetStringValue &H80000001, "Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts\", PrinterDisplayName, Value
   If Err.Number <> 0 Then
      MsgBox "The printer " & PrinterDisplayName & " could not be found or is not available."
      Exit Function
   End If
   On Error GoTo 0
   Port = Split(Value, ",")(1)
   ' Set the active printer
   Application.ActivePrinter = PrinterDisplayName & Space(1) & LocalOn & Space(1) & Port

End Function

[End Code Segment]

zorvek (Kevin Jones)ConsultantCommented:
Looks like some Zebra printer code can be found here. No idea if it actually works because I don't have a printer. VB6 is essentially the same as VBA.

Title: vb6, ZPL, socket printing

dcsDavidAuthor Commented:

The first code did set the printer to the correct printer. How do I send the ZPL string to that printer?

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

zorvek (Kevin Jones)ConsultantCommented:
See the link I provided in my second post. There are some VB6 code examples which should transfer unedited to your VBA project.

zorvek (Kevin Jones)ConsultantCommented:
Another promising link. You have to open the printer in raw data mode to the printer’s queue. Note the link to the Microsoft site for the actual code. The article discusses how to use it with a fix for the Windows SDK call.

Title: Sending ZPL to a Zebra Printer via Visual Basic 2005

dcsDavidAuthor Commented:
I used your code above to make sure I had the correct printer and then I used additional code off the internet to send the text string to the Zebra printer. Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.