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

Posted on 2011-04-26
Last Modified: 2012-05-11
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?
Question by:dcsDavid
    LVL 81

    Accepted Solution

    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]

    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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


    Author Comment


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

    LVL 81

    Expert Comment

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

    LVL 81

    Expert Comment

    by:zorvek (Kevin Jones)
    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


    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now