Setting default printer in Excel

[dhaywood]

Hi,

I'm having difficulties setting the default printer in an Excel workbook where I want to automate printing to a specific network printer.

The problem I have is that although I can successfully query the current network printers using EnumPrinters I can't manage to consistently set it using Application.ActivPrinter because the network extension "on NExx" changes from workstation to workstation. Also I trid to reference this by using a counter as I queried the printers but found that if a printer had been deleted before the requird network printer was required then it left a gap in the list eg. 1,2,3,5,6 etc.

I hope I haven't over complicated my description.

Cheers

D

 

[PHV]

As a workaround you may consider using something like this:
Application.Dialogs(xlDialogPrinterSetup).Show

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[lamber]

Make this in VBA
ActivePrinter = "printer name without on NExx:"

If you want to know the name of the defalut printer in a Word+VBA, do:
msgbox activeprinter

 

[dhaywood]

lamber,

I don't know if I did it wrong but I tried that

ActivePrinter = "\\Org01\FSCertificates"

but unless I put

ActivePrinter = "\\Org01\FSCertificates on NE01"

it wouldn't work.

The problem then is that on each machine the network interface at the end changed depending on which order they were installed

 

[innov]

D,

I just found your thread while searching for a solution to THE SAME ISSUE. Have you resolved it yet? If so, I'd love to know how you went about it.

Thanks!

INNOV

 

[dhaywood]

No I haven't. I'm still working on it if you can believe that.

What I did in the meantime was use PHV's solution as a temporary work-around. I just didn't expect it to take so long to find a solution.

I'll let you know if I ever crack it.....

Duncan

 

[gecko27]

I know this is way late, but this macro seems to be working pretty well now to solve this. Win98/ME handles network printer port naming differently, so this macro even checks for OS type and will set the active printer differently for those OSs. You'll need to change the printer_name and IP info for the 98/ME, and the printer_name info for the 2000/XP sections

Public Declare Function GetVersionExA Lib "kernel32" _
               (lpVersionInformation As OSVERSIONINFO) As Integer
 
            Public Type OSVERSIONINFO
               dwOSVersionInfoSize As Long
               dwMajorVersion As Long
               dwMinorVersion As Long
               dwBuildNumber As Long
               dwPlatformId As Long
               szCSDVersion As String * 128
            End Type
 
            Public Function getVersion() As String
               Dim osinfo As OSVERSIONINFO
               Dim retvalue As Integer
 
               osinfo.dwOSVersionInfoSize = 148
               osinfo.szCSDVersion = Space$(128)
               retvalue = GetVersionExA(osinfo)
 
               With osinfo
               Select Case .dwPlatformId
 
                Case 1
                
                    Select Case .dwMinorVersion
                        Case 0
                            getVersion = "Windows 95"
                        Case 10
                            getVersion = "Windows 98"
                        Case 90
                            getVersion = "Windows Mellinnium"
                    End Select
    
                Case 2
                    Select Case .dwMajorVersion
                        Case 3
                            getVersion = "Windows NT 3.51"
                        Case 4
                            getVersion = "Windows NT 4.0"
                        Case 5
                            If .dwMinorVersion = 0 Then
                                getVersion = "Windows 2000"
                            Else
                                getVersion = "Windows XP"
                            End If
                    End Select
    
                Case Else
                   getVersion = "Failed"
            End Select
 
               End With
            End Function
            
Public Function GetFullNetworkPrinterName(strNetworkPrinterName As String) As String
' returns the full network printer name
' returns an empty string if the printer is not found
' e.g. GetFullNetworkPrinterName("printer_name")
' might return "printer_name on Ne04:"
Dim strCurrentPrinterName As String, strTempPrinterName As String, i As Long
    strCurrentPrinterName = Application.ActivePrinter
    i = 0
    Do While i < 100
        strTempPrinterName = strNetworkPrinterName & " on Ne" & Format(i, "00") & ":"
        On Error Resume Next ' try to change to the network printer
        Application.ActivePrinter = strTempPrinterName
        On Error GoTo 0
        If Application.ActivePrinter = strTempPrinterName Then
            ' the network printer was found
            GetFullNetworkPrinterName = strTempPrinterName
            i = 100 ' makes the loop end
        End If
        i = i + 1
    Loop
    ' remove the line below if you want the function to change the active printer
     ' change back to the original printer
       Application.ActivePrinter = strCurrentPrinterName
End Function
            


Sub BarcodePrint()
'
' BarcodePrint Macro
' Print Barcode Sheet
'
' Keyboard Shortcut: Ctrl+p
    'get os version
    osinfo = getVersion()
    


    Dim STDprinter As String
    STDprinter = Application.ActivePrinter
    If osinfo = "Windows 95" Or osinfo = "Windows 98" Then
        If (STDprinter <> "printer_name on IP_address") Then
            ' change printer IN Win98
            Application.ActivePrinter = "printer_name on IP_address"
            ActiveSheet.PrintOut
            End If
    ElseIf osinfo = "Windows 2000" Or osinfo = "Windows XP" Then
          'change printer in Win 2000/XP
          PrintToNetworkPrinter
        
        
    End If
End Sub
Sub PrintToNetworkPrinter()
            Dim strCurrentPrinter As String, strNetworkPrinter As String
            strNetworkPrinter = GetFullNetworkPrinterName("printer_name")
            If Len(strNetworkPrinter) > 0 Then ' found the network printer
            strCurrentPrinter = Application.ActivePrinter
            ' change to the network printer
            Application.ActivePrinter = strNetworkPrinter
            ActiveSheet.PrintOut ' print something
            ' change back to the previously active printer
            Application.ActivePrinter = strCurrentPrinter
    End If
End Sub

 

[FaneDuru]

Completed with this:

Sub PrinterEnum()
  Dim net As Object, i
  Set net = CreateObject("Wscript.Network")
  For i = 0 To net.EnumPrinterConnections.Count - 1
   If Int(i / 2) <> i / 2 Then Debug.Print net.EnumPrinterConnections(i)
  Next i
  Set net = Nothing
End Sub

you may change the default printer as you wish...

Fane Duru