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
    1. 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

  • Re: Printout


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


    Code
    1. 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
    1. Application.ActivePrinter = "\\blkprt01\BK13 on Ne03:"
  • 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

  • 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...

  • Re: VBA Code To Print To Specfic Printer


    Quote

    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
    1. On Error Resume Next

    above ALL possible names & paths.

  • 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...

  • 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.


  • 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

  • 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

  • Re: VBA Code To Print To Specfic Printer




    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

  • Re: VBA Code To Print To Specfic Printer


    Hi Michael,



    You can then use


    Code
    1. Msgbox FindPrinter("PR0141")


    Returns


    "PR0141 on Ne11:"

  • 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!