Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: VBA Code To Print To Specfic Printer

  1. #1
    Join Date
    3rd April 2008
    Posts
    7

    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:

    VB:
    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

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,206

    Re: Printout

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

    VB:
    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:

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

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

  3. #3
    Join Date
    3rd April 2008
    Posts
    7

    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

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    3rd April 2008
    Posts
    7

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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,921

  6. #6
    Join Date
    3rd April 2008
    Posts
    7

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

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,713

    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
    VB:
    On Error Resume Next 
    
    
    above ALL possible names & paths.

  8. #8
    Join Date
    3rd April 2008
    Posts
    7

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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    16th February 2007
    Posts
    26

    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.

    VB:
    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 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    3rd April 2008
    Posts
    7

    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

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. VBA Macro To Print To PDF Via Specific Printer
    By Michael S. in forum EXCEL HELP
    Replies: 5
    Last Post: May 7th, 2009, 11:30
  2. Macro To Print To A Network Printer
    By Givemethemilk in forum EXCEL HELP
    Replies: 2
    Last Post: September 6th, 2007, 22:52
  3. Print Out Spreadsheet in Dotmatrix Printer
    By udhay28 in forum EXCEL HELP
    Replies: 3
    Last Post: August 28th, 2006, 20:01
  4. Print to designated printer
    By granty in forum EXCEL HELP
    Replies: 2
    Last Post: January 5th, 2006, 22:16

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno