• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3895
  • Last Modified:

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?
  • 4
  • 2
1 Solution
zorvek (Kevin Jones)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
Link: http://stackoverflow.com/questions/3396656/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?

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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
Link: http://blog.darrellittle.com/?p=9

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.

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now