Activeprinter

[ben]

I am using an API call to populate a list of printers on my computer, that
works fine, but when I try to use the returned strings to set the
activeprinter in Excel the method ALWAYS Fails, the same exact method works
fine in Word. Any reason why the method should fail when I use a list
returned by that API call?
Ben

 

[Tom Ogilvy]

Because the format used by each Office app is different.

Query Application.ActivePrinter

to see what Excel is looking for

Sub SeeString()
msgbox Application.ActivePrinter
End sub

 

[ben]

ok I did that, excel uses the printer name plus "on neo3" or neo4 etc...
depending on the port(I assume) it uses, how can I pull up a list of printers
like that, is there a seperate API call to do so. I am using ENUM_PRINTERS
Ben

 

[Tom Ogilvy]

Some notes I had on this. I believe KeepItcool has posted a newer verion,
so you might want do a google groups search on this newsgroup for author
KeepItCool, terms: Printerlist Should be withing the last year for sure.

http://support.microsoft.com/?ID=166008
ACC: Enumerating Local and Network Printers

Enumerating Windows' Available Ports
http://www.mvps.org/vbnet/code/enums/enumports.htm
=======================

Posting by KeepItcool

Option Explicit

Private Declare Function GetProfileString Lib "kernel32" _
Alias "GetProfileStringA" _
(ByVal lpAppName As String, ByVal lpKeyName As String, _
ByVal lpDefault As String, ByVal lpReturnedString As String, _
ByVal nSize As Long) As Long

Sub showlist()
MsgBox Join(PrinterList, vbNewLine)
End Sub


Function PrinterList()
Dim lRet As Long
Dim sBuffer As String
Dim lSize As Long
Dim avTmp As Variant
Dim aPrn() As String
Dim n%, sPrn$, sConn$, sPort$

'Get localized Connection string
avTmp = Split(Excel.ActivePrinter)
sConn = " " & avTmp(UBound(avTmp) - 1) & " "
'Get Printers
lSize = 1024
sBuffer = Space(lSize)
lRet = GetProfileString("devices", vbNullString, vbNullString, _
sBuffer, lSize)
sBuffer = Left(sBuffer, lRet)
avTmp = Split(sBuffer, Chr(0))

ReDim Preserve avTmp(UBound(avTmp) - 1)
For n = 0 To UBound(avTmp)
lSize = 128
sBuffer = Space(lSize)
lRet = GetProfileString("devices", avTmp(n), vbNullString, _
sBuffer, lSize)
sPort = Mid(sBuffer, InStr(sBuffer, ",") + 1, _
lRet - InStr(sBuffer, ","))
avTmp(n) = avTmp(n) & sConn & sPort
Next
PrinterList = avTmp
End Function




================================

This posting by Jim Rech may be useful as well - certainly simpler:

From: "Jim Rech" <[email protected]>
Subject: Re: Setting active printers will Excel 97 VBA
Date: Thu, 19 Oct 2000 14:04:56 -0400
Lines: 9
Newsgroups: microsoft.public.excel.programming

This macro enumerates printers and their connections. Parsing it you may be
able to construct the syntax ActivePrinter wants:

Sub a()
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections
For i = 0 To oPrinters.Count - 1 Step 2
Debug.Print "Port " & oPrinters.Item(i) & " = " & _
oPrinters.Item(i + 1)
Next
End Sub


--
Jim Rech
Excel MVP
--------------------------


Code posted by Steven Kelder:

Option Explicit
Public WshNetwork As Object
Public oPrinters As Variant
Public oDrives As Variant

Sub ShowConnections()
Dim I As Integer
Set WshNetwork = CreateObject("WScript.Network")
Set oDrives = WshNetwork.EnumNetworkDrives
Set oPrinters = WshNetwork.EnumPrinterConnections

MsgBox "Printers:"
For I = 0 To oPrinters.Count - 1 Step 2
MsgBox "Port " & oPrinters.Item(I) & " = " & oPrinters.Item(I + 1)
Next

MsgBox "Drives:"
For I = 0 To oDrives.Count - 1 Step 2
MsgBox "Drive " & oDrives.Item(I) & " = " & oDrives.Item(I + 1)
Next

End Sub



In Windows NT:
when setting ActivePrinter, you have to
lookup in the registry (
HKEY_CURRENT_USER\Software\Microsoft\Windows NT\CurrentVersion\PrinterPorts
) to get the "port name" that Excel
expects (which has nothing to do with the actual port name of the
printer!).

 

[ben]

beautiful, much thanks