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

--
When you lose your mind, you free your life.

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

--
Regards,
Tom Ogilvy

"ben" (remove this if mailing direct) wrote in message
...
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

--
When you lose your mind, you free your life.

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

--
When you lose your mind, you free your life.


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"ben" (remove this if mailing direct) wrote in message
...
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

--
When you lose your mind, you free your life.

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"
Subject: 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!).

--
Regards,
Tom Ogilvy

"ben" (remove this if mailing direct) wrote in message
...
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

--
When you lose your mind, you free your life.


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"ben" (remove this if mailing direct) wrote in
message
...
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

--
When you lose your mind, you free your life.

beautiful, much thanks

--
When you lose your mind, you free your life.


"Tom Ogilvy" wrote:

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"
Subject: 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!).

--
Regards,
Tom Ogilvy

"ben" (remove this if mailing direct) wrote in message
...
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

--
When you lose your mind, you free your life.


"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy

"ben" (remove this if mailing direct) wrote in
message
...
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

--
When you lose your mind, you free your life.