Announcement

Collapse
No announcement yet.

VBA Code To Print To Specfic Printer

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • VBA Code To Print To Specfic Printer



    I have a macro that prints out several worksheets. My problem is that I want to print these worksheets to a specific color printer. There are many different users and most of them will have a black-and-white printer setup as their default. I want to be able to print using the ip address of the color printer.

    I have tried setting the active printer using:

    Code:
    Application.ActivePrinter = "\\WP0101\10.117.3.36"
    with little luck... This seems like such a simple thing! I have done a lot of research and can't find a good solution although I have seen the question raised several times!

    Ozgrid has been a great source of information and solutions in the past. Please help again!

    Thanks,

    William

  • #2
    Re: Printout

    This will show the Printer Dialog same as pressing Ctrl + P.

    Code:
    Application.Dialogs(xlDialogPrint).Show
    Edit: Have you tried recording a macro while setting the printer to the color printer? The IP address should be static for printers. I get the follwing line of code when recording a macro and have no problem printing to this color laserjet:

    Code:
    Application.ActivePrinter = "\\blkprt01\BK13 on Ne03:"
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

    Comment


    • #3
      Re: Printout

      AAE,

      Thanks for the reply. This problem with this method is that it uses the printer's name which changes from computer to computer (the "...on NeXX:" changes depending on which computer you are on). I am looking for a method to use the printer's IP Address (10.117.3.36) which should be static from computer to computer.

      Thanks,

      William

      Comment


      • #4
        Re: Printout

        Anyone? Please? This seems like it should have an easy answer. Surely, I am not the first person to run across this issue...

        Comment


        • #5
          Re: Printout

          Does this link help?

          http://www.xtremevbtalk.com/showthread.php?t=72314
          Reafidy

          Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

          Comment


          • #6
            Re: Printout

            Reafidy,

            Thanks for your reply. This link was one of the items that I found when I first started my search. It is basically an ugly work-around for what I need to do. I am really hoping to avoid this as a solution, but if I can't get a better approach (i.e. printing to an IP Address) I will have to go this route. The problem is that this method brings it's own set of problems that I believe can be competely avoided by using an IP Address.

            I still think that someone out there has solved this problem before.

            Thanks,

            William

            the faster you go, the rounder you get...

            Comment


            • #7
              Re: VBA Code To Print To Specfic Printer

              This problem with this method is that it uses the printer's name which changes from computer to computer (the "...on NeXX:" changes depending on which computer you are on).
              Use
              Code:
              On Error Resume Next
              above ALL possible names & paths.

              Comment


              • #8
                Re: VBA Code To Print To Specfic Printer

                Thanks Dave,

                I guess this method will have to do...

                I was really hoping to be able to use the IP Address so that I could avoid having to loop through all of the printers. The IP approach seems like a much more efficient solution.

                William

                beauty is a diamond splinter,
                reflecting crazily in the mind's eye...

                Comment


                • #9
                  Re: VBA Code To Print To Specfic Printer

                  I had a similar issue printing to PDF and I think I found a solution around the "NeXX" problem. See code below.

                  Code:
                  Sub cmdMakePDF_Click()
                      Dim Msg As String
                      Dim C As Integer
                      Dim PrinterName As String
                          
                      C = 1
                      
                      On Error GoTo MakePDFError:
                      
                  ResumePrinting:
                      If C < 10 Then
                          PrinterName = "Adobe PDF on Ne0" & C & ":"
                      Else
                          PrinterName = "Adobe PDF on Ne" & C & ":"
                      End If
                      
                      Application.ActivePrinter = PrinterName
                      ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
                          "Adobe PDF on Ne04:", collate:=True
                  
                      Exit Sub
                  
                  MakePDFError:
                      C = C + 1
                      Resume ResumePrinting:
                  End Sub

                  Comment


                  • #10
                    Re: VBA Code To Print To Specfic Printer

                    Michael,

                    Thanks for your input. I had gone down a similar route and had it working until I came across one users computer that had 'on WS0101' as a port. At that point I got frustrated and just had the select printer dialog box open as part of the code. It is an ugly solution, but at least it is functional.

                    I was really hoping that someone in this community had found a way to reference a printer using the IP Address. It seems to me that the IP Address approach would completely eliminate all of the other issues and loops that are used to find the right printer. Based upon all of the threads I have seen, the port issue creates a lot of problems for a lot of people. I also understand that it is not even a problem when using MS Word!

                    Oh well, c'est la vie...

                    Thanks again,

                    William

                    Comment


                    • #11
                      Re: VBA Code To Print To Specfic Printer

                      Hi Michael,

                      I found this thread searching for a solution to my problem. It is exact the same as yours and I also tried to print using IP-addresses.
                      Since your post is 2 years old, I was wondering if you have found a solution to this problem?

                      Hope to get a reply.

                      Thanks,

                      Timothy

                      Comment


                      • #12
                        Re: VBA Code To Print To Specfic Printer

                        Originally posted by Timothy.ceva View Post
                        Hi Michael,

                        I found this thread searching for a solution to my problem. It is exact the same as yours and I also tried to print using IP-addresses.
                        Since your post is 2 years old, I was wondering if you have found a solution to this problem?

                        Hope to get a reply.

                        Thanks,

                        Timothy

                        Timothy,

                        I did not find a solution for this. As stated in one of the earlier replies, I ended up having the print dialog window popup and the user could then select which printer to use. If I remember correctly, I had an information box open that told the end-user to select a color printer capable of printing to an 11" x 17" sheet which was why I was trying to print to a specific IP address originally. Again, not an elegant solution, but effective...

                        Sorry that I don't have anything better to offer.

                        William

                        Comment


                        • #13
                          Re: VBA Code To Print To Specfic Printer

                          Hi Michael,

                          Code:
                          Public Function FindPrinter(ByVal PrinterName As String) As String
                           
                            Dim Arr As Variant
                            Dim Device As Variant
                            Dim Devices As Variant
                            Dim Printer 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
                                  Printer = Device & " on " & Split(RegValue, ",")(1)
                                  If InStr(1, Printer, PrinterName, vbTextCompare) > 0 Then
                                     FindPrinter = Printer
                                     Exit Function
                                  End If
                                Next
                                
                          End Function
                          You can then use

                          Code:
                          Msgbox FindPrinter("PR0141")
                          Returns

                          "PR0141 on Ne11:"

                          Comment


                          • #14


                            Re: VBA Code To Print To Specfic Printer

                            I'm resurrecting a dead thread but I found a solution to the OP's question. I hope it helps someone!

                            Code:
                                    Public Function Printer_IPtoName(ByVal TargetIP As String) As String
                            
                            
                                Dim RegPath As String
                                Dim Printer As Variant
                                Dim CheckIP As String
                                Dim myWS As Object
                                Dim RegObj As Object
                                Dim ArrSubKeys()
                                Const HKEY_LOCAL_MACHINE = &H80000002
                                Set RegObj = GetObject("winmgmts:{impersonationLevel=impersonate}!\\.\root\default:StdRegProv")
                                Set myWS = CreateObject("WScript.Shell")
                                
                                ' This is the path to the printers in the registry.  I found it by opening regedit and
                                ' doing a "Find..." for the IP address of the printer I was looking for.
                                RegPath = "SOFTWARE\Microsoft\Windows NT\CurrentVersion\Print\Printers\"
                                                     
                                    ' This line gets the list of printers from the registry
                                    RegObj.EnumKey HKEY_LOCAL_MACHINE, RegPath, ArrSubKeys
                                        
                                    ' Examine each printer found in the registry in turn
                                    For Each Printer In ArrSubKeys
                                        'read key from registry
                                        CheckIP = myWS.RegRead("HKEY_LOCAL_MACHINE\" & RegPath & Printer & "\Port")
                                                 
                                            ' Evaluate result
                                            If CheckIP = "IP_" & TargetIP Then
                                            
                                            ' Return the printer name - cool!
                                            Printer_IPtoName = Printer
                                            End If
                                 
                                
                                    Next
                            
                            
                                End Function

                            Comment

                            Working...
                            X