ActivePrinter in PrintOut Method

  • Is there a way of creating a list of Active Printers on a users computer for the user to select to be used in the PrintOut method i.e. I am bypassing the Print option but some users want to print in colour so I need to be able to select other printers and pass the name to the PrintOut method...


    Any ideas would be appreciated...


    To give a better idea I have created a dynamic page of graphs and to print all the graphs i need to change Source Data and then print the page so I cannot do a Print Entire Workbook...

  • Create a list of available printers using Data Validation in A1 then use this code:


    Dim SelPr As String


    SelPr = Range("A1").Value
    '

    ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
    Printer, Collate:=True



    Then you can select the specific printer before running the code

  • This will get the list of availble printers. Specific to user.


    Sub get_printers()


    On Error Resume Next


    Set WshNetwork = CreateObject("WScript.Network")
    Set oPrinters = WshNetwork.EnumPrinterConnections
    For i = 0 To oPrinters.Count - 1 Step 1
    Cells(i + 1, 1) = "Port " & oPrinters.Item(i) & " = " & oPrinters.Item(i + 1)
    Next
    End Sub