Excel Liste über Userform Drucken mit Netzwerkdrucker
07.02.2020 13:53:07
Marina
Ich wollte über eine Userform eine Excel Liste Drucken indem ich eine Combobox mit Netzwerkdruckern befülle.
Mein Problem dabei ist, dass in der Combobox nur die Lokalen Drucker angezeigt wird.
Könnt ihr mir sagen wie ich alles Drucker zur Auswahl bekommen bzw. wie ich z.B nur 3-4 bestimmte Drucker angezeigt bekomme anhand von den Drucker Name.
Das ist soweit mein Code:
(ps: Code stibitzt von jemand anderem)
Private Sub DruckenCommandButton_Click()
Dim sOldPrinter As String
sOldPrinter = Application.ActivePrinter '
On Error GoTo Fehler:
If DruckerComboBox.ListIndex = -1 Then
MsgBox "Kein Printer gewählt", vbExclamation
Exit Sub
End If
With ThisWorkbook.Worksheets(1) '
.Range("a1") = "Was zum Drucken"
.PageSetup.PrintArea = "$A$1:$B$4" '
.PrintOut , , , , DruckerComboBox.Text '
Aufraeumen:
Application.ActivePrinter = sOldPrinter '
Exit Sub
Fehler:
MsgBox Err.Description
Resume Aufraeumen
End With
End Sub
Private Sub UserForm_Initialize()
Dim i As Long
If GetPrinter(DruckerComboBox) = False Then
MsgBox "Kein Drucker gefunden", vbExclamation
Exit Sub
End If
For i = 0 To DruckerComboBox.ListCount - 1
If ActivePrinter Like DruckerComboBox.List(i) & "*" Then '
DruckerComboBox.ListIndex = i
Exit For
End If
Next i
End Sub
Option Explicit
Option Private Module
Private Declare Function lstrcpy Lib "kernel32.dll" Alias "lstrcpyA" _
(ByVal lpString1 As String, ByVal lpString2 As Long) As Long
Private Declare Function lstrlen Lib "kernel32.dll" Alias "lstrlenA" _
(ByVal lpString As Long) As Long
Declare Function EnumPrinters Lib "winspool.drv" _
Alias "EnumPrintersA" ( _
ByVal flags As Long, _
ByVal Name As String, _
ByVal Level As Long, _
pPrinterEnum As Long, _
ByVal cdBuf As Long, _
pcbNeeded As Long, _
pcReturned As Long) As Long
Const PRINTER_ENUM_CONNECTIONS = &H2
Private Type PRINTER_INFO_1
flags As Long
pDescription As String
pName As String
pComment As String
End Type
Public Function GetPrinter(DieCombo As MSForms.ComboBox) As Boolean
Dim longbuffer() As Long ' resizable array receives information from the function
Dim numbytes As Long ' size in bytes of longbuffer()
Dim numneeded As Long ' receives number of bytes necessary if longbuffer() is too small
Dim numprinters As Long ' receives number of printers found
Dim c As Integer, retval As Long ' counter variable & return value
Dim sTemp As String
DieCombo.Clear
numbytes = 3076 ' should be sufficiently big, but it may not be
ReDim longbuffer(0 To numbytes / 4) As Long ' resize array -- note how 1 Long = 4 bytes
retval = EnumPrinters(PRINTER_ENUM_CONNECTIONS, "", 1, longbuffer(0), _
numbytes, numneeded, numprinters)
If retval = 0 Then ' try enlarging longbuffer() to receive all necessary information
numbytes = numneeded
ReDim longbuffer(0 To numbytes / 4) As Long ' make it large enough
retval = EnumPrinters(PRINTER_ENUM_CONNECTIONS, "", 1, _
longbuffer(0), numbytes, numneeded, numprinters)
If retval = 0 Then ' failed again!
GetPrinter = False
Exit Function
End If
End If
If numprinters 0 Then ReDim printinfo(0 To numprinters - 1) As PRINTER_INFO_1
GetPrinter = True
For c = 0 To numprinters - 1 ' loop, putting each set of information into each element
sTemp = Space(lstrlen(longbuffer(4 * c + 2)))
retval = lstrcpy(sTemp, longbuffer(4 * c + 2))
DieCombo.AddItem sTemp
Next c
End Function