is word really smarter than excel?

Bill Kuunders · 02-06-2005, 03:51 PM

Jezbel from the word vba newsgroup wrote...........

The difference must lie elsewhere. VBA is exactly the same in both
> cases -- literally: it's the same library.

My question remains................
>
> "Bill Kuunders" <[email protected]> wrote in message
> news:[email protected]...
>> The code below is for an excel routine to find the port number for a
>> network printer.
>> The portnumbers are selected at random when logging on to the network.
>> For us it is normally ne01 ne02 ne03 or ne04
>>
>>
>> Dim OldPname As String
>> Dim TempPname As String
>> OldPname = Application.ActivePrinter
>> For J = 0 To 99
>> On Error Resume Next
>> If J < 10 Then
>> TempPname = "Adobe PDF on Ne0" & J & ":"
>> Application.ActivePrinter = TempPname
>> ElseIf J >= 10 Then
>> TempPname = "Adobe PDF on Ne" & J & ":"
>> Application.ActivePrinter = TempPname
>> End If
>> If Application.ActivePrinter = TempPname Then
>> Exit For
>> End If
>> Next J
>> Application.ActivePrinter = "TempPname"
>> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
>> =TempPname, Collate:=True
>> Application.ActivePrinter = OldPname
>>
>>
>> The code works perfect.
>> My question is..............
>> Why does word not need to go through the loop of allocating numbers until
>> there is a match?
>>
>> In word these two lines return the right printer including the right port
>>
>> TempPname = "Adobe PDF "
>> Application.ActivePrinter = TempPname
>>
>> Do I really have to admit that word is smarter than excel?
>>
>>
>> Regards
>> Bill Kuunders

K Dales · 02-06-2005, 04:34 PM

If you print manually, instead of using VBA, how does Adobe appear in the
Print dialog? It looks like in Excel you need to specify "Adobe PDF on ..."
with the port number but in Word it just recognizes it as "Adobe PDF". The
printer name is a Windows setting so I don't know why it would be different
between the two apps.

"Bill Kuunders" wrote:

> Jezbel from the word vba newsgroup wrote...........
>
> The difference must lie elsewhere. VBA is exactly the same in both
> > cases -- literally: it's the same library.
>
> My question remains................
> >
> > "Bill Kuunders" <[email protected]> wrote in message
> > news:[email protected]...
> >> The code below is for an excel routine to find the port number for a
> >> network printer.
> >> The portnumbers are selected at random when logging on to the network.
> >> For us it is normally ne01 ne02 ne03 or ne04
> >>
> >>
> >> Dim OldPname As String
> >> Dim TempPname As String
> >> OldPname = Application.ActivePrinter
> >> For J = 0 To 99
> >> On Error Resume Next
> >> If J < 10 Then
> >> TempPname = "Adobe PDF on Ne0" & J & ":"
> >> Application.ActivePrinter = TempPname
> >> ElseIf J >= 10 Then
> >> TempPname = "Adobe PDF on Ne" & J & ":"
> >> Application.ActivePrinter = TempPname
> >> End If
> >> If Application.ActivePrinter = TempPname Then
> >> Exit For
> >> End If
> >> Next J
> >> Application.ActivePrinter = "TempPname"
> >> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
> >> =TempPname, Collate:=True
> >> Application.ActivePrinter = OldPname
> >>
> >>
> >> The code works perfect.
> >> My question is..............
> >> Why does word not need to go through the loop of allocating numbers until
> >> there is a match?
> >>
> >> In word these two lines return the right printer including the right port
> >>
> >> TempPname = "Adobe PDF "
> >> Application.ActivePrinter = TempPname
> >>
> >> Do I really have to admit that word is smarter than excel?
> >>
> >>
> >> Regards
> >> Bill Kuunders
>
>
>

Tom Ogilvy · 02-06-2005, 07:06 PM

Yes, word is smarter. Printing is through the application, not VBA and
excel needs the port.

--
Regards,
Tom Ogilvy
"Bill Kuunders" <[email protected]> wrote in message
news:%[email protected]...
> Jezbel from the word vba newsgroup wrote...........
>
> The difference must lie elsewhere. VBA is exactly the same in both
> > cases -- literally: it's the same library.
>
> My question remains................
> >
> > "Bill Kuunders" <[email protected]> wrote in message
> > news:[email protected]...
> >> The code below is for an excel routine to find the port number for a
> >> network printer.
> >> The portnumbers are selected at random when logging on to the network.
> >> For us it is normally ne01 ne02 ne03 or ne04
> >>
> >>
> >> Dim OldPname As String
> >> Dim TempPname As String
> >> OldPname = Application.ActivePrinter
> >> For J = 0 To 99
> >> On Error Resume Next
> >> If J < 10 Then
> >> TempPname = "Adobe PDF on Ne0" & J & ":"
> >> Application.ActivePrinter = TempPname
> >> ElseIf J >= 10 Then
> >> TempPname = "Adobe PDF on Ne" & J & ":"
> >> Application.ActivePrinter = TempPname
> >> End If
> >> If Application.ActivePrinter = TempPname Then
> >> Exit For
> >> End If
> >> Next J
> >> Application.ActivePrinter = "TempPname"
> >> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
> >> =TempPname, Collate:=True
> >> Application.ActivePrinter = OldPname
> >>
> >>
> >> The code works perfect.
> >> My question is..............
> >> Why does word not need to go through the loop of allocating numbers
until
> >> there is a match?
> >>
> >> In word these two lines return the right printer including the right
port
> >>
> >> TempPname = "Adobe PDF "
> >> Application.ActivePrinter = TempPname
> >>
> >> Do I really have to admit that word is smarter than excel?
> >>
> >>
> >> Regards
> >> Bill Kuunders
>
>

Bill Kuunders · 02-07-2005, 06:06 AM

Thanks Tom
I've seen a lot of your answers over the years so I do believe you.
What can I say...................It is more than likely way above my head.
I'm just disappointed that excel couldn't do the same.
I still like excel though.

Regards
Bill K

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> Yes, word is smarter. Printing is through the application, not VBA and
> excel needs the port.
>
> --
> Regards,
> Tom Ogilvy
> "Bill Kuunders" <[email protected]> wrote in message
> news:%[email protected]...
>> Jezbel from the word vba newsgroup wrote...........
>>
>> The difference must lie elsewhere. VBA is exactly the same in both
>> > cases -- literally: it's the same library.
>>
>> My question remains................
>> >
>> > "Bill Kuunders" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> The code below is for an excel routine to find the port number for a
>> >> network printer.
>> >> The portnumbers are selected at random when logging on to the network.
>> >> For us it is normally ne01 ne02 ne03 or ne04
>> >>
>> >>
>> >> Dim OldPname As String
>> >> Dim TempPname As String
>> >> OldPname = Application.ActivePrinter
>> >> For J = 0 To 99
>> >> On Error Resume Next
>> >> If J < 10 Then
>> >> TempPname = "Adobe PDF on Ne0" & J & ":"
>> >> Application.ActivePrinter = TempPname
>> >> ElseIf J >= 10 Then
>> >> TempPname = "Adobe PDF on Ne" & J & ":"
>> >> Application.ActivePrinter = TempPname
>> >> End If
>> >> If Application.ActivePrinter = TempPname Then
>> >> Exit For
>> >> End If
>> >> Next J
>> >> Application.ActivePrinter = "TempPname"
>> >> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
>> >> =TempPname, Collate:=True
>> >> Application.ActivePrinter = OldPname
>> >>
>> >>
>> >> The code works perfect.
>> >> My question is..............
>> >> Why does word not need to go through the loop of allocating numbers
> until
>> >> there is a match?
>> >>
>> >> In word these two lines return the right printer including the right
> port
>> >>
>> >> TempPname = "Adobe PDF "
>> >> Application.ActivePrinter = TempPname
>> >>
>> >> Do I really have to admit that word is smarter than excel?
>> >>
>> >>
>> >> Regards
>> >> Bill Kuunders
>>
>>
>
>

Bill Kuunders · 02-07-2005, 06:06 AM

Thanks K Dales

I will try that out at work tomorrow.

Regards
Bill K
"K Dales" <[email protected]> wrote in message
news:[email protected]...
> If you print manually, instead of using VBA, how does Adobe appear in the
> Print dialog? It looks like in Excel you need to specify "Adobe PDF on
> ..."
> with the port number but in Word it just recognizes it as "Adobe PDF".
> The
> printer name is a Windows setting so I don't know why it would be
> different
> between the two apps.
>
> "Bill Kuunders" wrote:
>
>> Jezbel from the word vba newsgroup wrote...........
>>
>> The difference must lie elsewhere. VBA is exactly the same in both
>> > cases -- literally: it's the same library.
>>
>> My question remains................
>> >
>> > "Bill Kuunders" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> The code below is for an excel routine to find the port number for a
>> >> network printer.
>> >> The portnumbers are selected at random when logging on to the network.
>> >> For us it is normally ne01 ne02 ne03 or ne04
>> >>
>> >>
>> >> Dim OldPname As String
>> >> Dim TempPname As String
>> >> OldPname = Application.ActivePrinter
>> >> For J = 0 To 99
>> >> On Error Resume Next
>> >> If J < 10 Then
>> >> TempPname = "Adobe PDF on Ne0" & J & ":"
>> >> Application.ActivePrinter = TempPname
>> >> ElseIf J >= 10 Then
>> >> TempPname = "Adobe PDF on Ne" & J & ":"
>> >> Application.ActivePrinter = TempPname
>> >> End If
>> >> If Application.ActivePrinter = TempPname Then
>> >> Exit For
>> >> End If
>> >> Next J
>> >> Application.ActivePrinter = "TempPname"
>> >> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
>> >> =TempPname, Collate:=True
>> >> Application.ActivePrinter = OldPname
>> >>
>> >>
>> >> The code works perfect.
>> >> My question is..............
>> >> Why does word not need to go through the loop of allocating numbers
>> >> until
>> >> there is a match?
>> >>
>> >> In word these two lines return the right printer including the right
>> >> port
>> >>
>> >> TempPname = "Adobe PDF "
>> >> Application.ActivePrinter = TempPname
>> >>
>> >> Do I really have to admit that word is smarter than excel?
>> >>
>> >>
>> >> Regards
>> >> Bill Kuunders
>>
>>
>>

Tom Ogilvy · 02-07-2005, 10:06 AM

You don't have to take my word. You can test it yourself. Both Excel and
Word support application.ActivePrinter

In word
? application.ActivePrinter
\\006-pm2-fs02\PRN4N3 on NE02:
' now change to another manually
? application.ActivePrinter
\\006-pm2-fs02\prn4n1 on NE01:

Now set it back with code

application.ActivePrinter = "\\006-pm2-fs02\PRN4N3"

In Excel
? application.ActivePrinter
\\006-pm2-fs02\prn4n1 on Ne01:
' now change to another manually
? application.ActivePrinter
\\006-pm2-fs02\PRN4N3 on Ne02:

Now set it back with code:
Application.ActivePrinter = "\\006-pm2-fs02\prn4n1"
Raises an error
but this works:

Application.ActivePrinter = "\\006-pm2-fs02\prn4n1 on Ne01:"

of course in word, adding the "on Ne01:" also works, so Word is more
flexible.

From what I have read in a discussion here, Access is different again.

While I like your idea of brute force testing, KeepItCool has posted this
code for Excel which seems to work.

http://groups.google.com/groups?selm...210&output=gpl

Newsgroups: microsoft.public.excel.programming
Subject: Re: COMBOBOX WITH PRINTERS LIST
From: keepitcool <[email protected]>
References: <[email protected]>
Organization: xlSupport.com
Message-ID: <[email protected]>
User-Agent: Xnews/5.04.25
Lines: 119
Date: Wed, 26 May 2004 06:45:47 GMT
NNTP-Posting-Host: 24.132.231.213
X-Complaints-To: [email protected]
X-Trace: amsnews02.chello.com 1085553947 24.132.231.213 (Wed, 26 May 2004
08:45:47 MEST)
NNTP-Posting-Date: Wed, 26 May 2004 08:45:47 MEST

Christian..

the function below returns an array of the installed printers
( in complete localized strings)
Can be called when you're filling a combobox on a form or in a dropdown
from worksheet as in demo (as in the demo)

Sub Demo()
Dim v As Variant
Dim i As Long
Workbooks.Add xlWBATWorksheet
v = PrinterList
For i = LBound(v) To UBound(v)
Cells(i + 1, 1) = v(i)
Cells(i + 1, 2).Formula = "=printerlist(" & i & ")"
Next
Cells(1, 3).Resize(i, 1).FormulaArray = "=transpose(printerlist())"
Cells(1, 4).Resize(1, i).FormulaArray = "=printerlist()"
Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
MsgBox Join(v, vbNewLine)
End Sub

Function PrinterList(Optional PrinterNr As Integer = -1)
Dim i%, n%, lRet&, sBuf$, sOn$, sPort$, aPrn
Const lSize& = 1024, sKey$ = "devices"

'-----------------------------------------------------------
'Author: keepITcool 1st posted nl.office.excel 23/10/2003
'Function returns a zerobased array of installed printers
'include for xl97: supplemental functions split/join/replace
'-----------------------------------------------------------

'Get localized Connection string
aPrn = Split(Excel.ActivePrinter)
sOn = " " & aPrn(UBound(aPrn) - 1) & " "
'Read Printers
sBuf = Space(lSize)
lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf, lSize)
If lRet = 0 Then Exit Function
'Make Array from String
aPrn = Split(Left(sBuf, lRet - 1), vbNullChar)
'Add Port for each Printer
For n = LBound(aPrn) To UBound(aPrn)
sBuf = Space(lSize)
lRet = GetProfileString(sKey, aPrn(n), vbNullString, sBuf, lSize)
sPort = Mid(sBuf, InStr(sBuf, ",") + 1, lRet - InStr(sBuf, ","))
aPrn(n) = aPrn(n) & sOn & sPort
Next
'Sort
qSort aPrn
'Return the results
If PrinterNr = -1 Then PrinterList = aPrn Else PrinterList = aPrn( _
PrinterNr)
End Function

Public Sub qSort(v, Optional n& = True, Optional m& = True)
Dim i&, j&, p, t
If n = True Then n = LBound(v): If m = True Then m = UBound(v)
i = n: j = m: p = v((n + m) \ 2)
While (i <= j)
While (v(i) < p And i < m): i = i + 1: Wend
While (v(j) > p And j > n): j = j - 1: Wend
If (i <= j) Then
t = v(i): v(i) = v(j): v(j) = t
i = i + 1: j = j - 1
End If
Wend
If (n < j) Then qSort v, n, j
If (i < m) Then qSort v, i, m
End Sub

'**********************************************************
' Optional Split function for xl97
'**********************************************************
#If VBA6 Then
#Else

Function Split(sText As String, _
Optional sDelim As String = " ") As Variant
Dim i%, sFml$, v0, v1
Const sDQ$ = """"

If sDelim = vbNullChar Then
sDelim = Chr(7)
sText = Replace(sText, vbNullChar, sDelim)
End If
sFml = "{""" & Application.Substitute(sText, sDelim, """,""") & """}"
v1 = Evaluate(sFml)
'Return 0 based for compatibility
ReDim v0(0 To UBound(v1) - 1)
For i = 0 To UBound(v0): v0(i) = v1(i + 1): Next

Split = v0

End Function
#End If

keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >
---------------------------------------------------

He also recently stated that in the split function

v1 = Evaluate(sFml)
'Return 0 based for compatibility
ReDim v0(0 To UBound(v1) - 1)
For i = 0 To UBound(v0): v0(i) = v1(i + 1): Next

Split = v0

could be replaced by
v1 = Evaluate(sFml)
ReDim Preserve v1(0 To UBound(v1) - 1)
Split = v1

--
Regards,
Tom Ogilvy

"Bill Kuunders" <[email protected]> wrote in message
news:[email protected]...
> Thanks Tom
> I've seen a lot of your answers over the years so I do believe you.
> What can I say...................It is more than likely way above my head.
> I'm just disappointed that excel couldn't do the same.
> I still like excel though.
>
> Regards
> Bill K
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > Yes, word is smarter. Printing is through the application, not VBA and
> > excel needs the port.
> >
> > --
> > Regards,
> > Tom Ogilvy
> > "Bill Kuunders" <[email protected]> wrote in message
> > news:%[email protected]...
> >> Jezbel from the word vba newsgroup wrote...........
> >>
> >> The difference must lie elsewhere. VBA is exactly the same in both
> >> > cases -- literally: it's the same library.
> >>
> >> My question remains................
> >> >
> >> > "Bill Kuunders" <[email protected]> wrote in message
> >> > news:[email protected]...
> >> >> The code below is for an excel routine to find the port number for a
> >> >> network printer.
> >> >> The portnumbers are selected at random when logging on to the
network.
> >> >> For us it is normally ne01 ne02 ne03 or ne04
> >> >>
> >> >>
> >> >> Dim OldPname As String
> >> >> Dim TempPname As String
> >> >> OldPname = Application.ActivePrinter
> >> >> For J = 0 To 99
> >> >> On Error Resume Next
> >> >> If J < 10 Then
> >> >> TempPname = "Adobe PDF on Ne0" & J & ":"
> >> >> Application.ActivePrinter = TempPname
> >> >> ElseIf J >= 10 Then
> >> >> TempPname = "Adobe PDF on Ne" & J & ":"
> >> >> Application.ActivePrinter = TempPname
> >> >> End If
> >> >> If Application.ActivePrinter = TempPname Then
> >> >> Exit For
> >> >> End If
> >> >> Next J
> >> >> Application.ActivePrinter = "TempPname"
> >> >> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
> >> >> =TempPname, Collate:=True
> >> >> Application.ActivePrinter = OldPname
> >> >>
> >> >>
> >> >> The code works perfect.
> >> >> My question is..............
> >> >> Why does word not need to go through the loop of allocating numbers
> > until
> >> >> there is a match?
> >> >>
> >> >> In word these two lines return the right printer including the right
> > port
> >> >>
> >> >> TempPname = "Adobe PDF "
> >> >> Application.ActivePrinter = TempPname
> >> >>
> >> >> Do I really have to admit that word is smarter than excel?
> >> >>
> >> >>
> >> >> Regards
> >> >> Bill Kuunders
> >>
> >>
> >
> >
>
>

Bill Kuunders · 02-07-2005, 04:06 PM

Again Tom,
Thank you very much for the detailed explanations and time.
I continue to check the news groups and learn every day.
Regards
Bill K

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> You don't have to take my word. You can test it yourself. Both Excel and
> Word support application.ActivePrinter
>
> In word
> ? application.ActivePrinter
> \\006-pm2-fs02\PRN4N3 on NE02:
> ' now change to another manually
> ? application.ActivePrinter
> \\006-pm2-fs02\prn4n1 on NE01:
>
> Now set it back with code
>
> application.ActivePrinter = "\\006-pm2-fs02\PRN4N3"
>
>
> In Excel
> ? application.ActivePrinter
> \\006-pm2-fs02\prn4n1 on Ne01:
> ' now change to another manually
> ? application.ActivePrinter
> \\006-pm2-fs02\PRN4N3 on Ne02:
>
> Now set it back with code:
> Application.ActivePrinter = "\\006-pm2-fs02\prn4n1"
> Raises an error
> but this works:
>
> Application.ActivePrinter = "\\006-pm2-fs02\prn4n1 on Ne01:"
>
> of course in word, adding the "on Ne01:" also works, so Word is more
> flexible.
>
> From what I have read in a discussion here, Access is different again.
>
> While I like your idea of brute force testing, KeepItCool has posted this
> code for Excel which seems to work.
>
>
> http://groups.google.com/groups?selm...210&output=gpl
>
>
> Newsgroups: microsoft.public.excel.programming
> Subject: Re: COMBOBOX WITH PRINTERS LIST
> From: keepitcool <[email protected]>
> References: <[email protected]>
> Organization: xlSupport.com
> Message-ID: <[email protected]>
> User-Agent: Xnews/5.04.25
> Lines: 119
> Date: Wed, 26 May 2004 06:45:47 GMT
> NNTP-Posting-Host: 24.132.231.213
> X-Complaints-To: [email protected]
> X-Trace: amsnews02.chello.com 1085553947 24.132.231.213 (Wed, 26 May 2004
> 08:45:47 MEST)
> NNTP-Posting-Date: Wed, 26 May 2004 08:45:47 MEST
>
>
> Christian..
>
> the function below returns an array of the installed printers
> ( in complete localized strings)
> Can be called when you're filling a combobox on a form or in a dropdown
> from worksheet as in demo (as in the demo)
>
> Sub Demo()
> Dim v As Variant
> Dim i As Long
> Workbooks.Add xlWBATWorksheet
> v = PrinterList
> For i = LBound(v) To UBound(v)
> Cells(i + 1, 1) = v(i)
> Cells(i + 1, 2).Formula = "=printerlist(" & i & ")"
> Next
> Cells(1, 3).Resize(i, 1).FormulaArray = "=transpose(printerlist())"
> Cells(1, 4).Resize(1, i).FormulaArray = "=printerlist()"
> Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
> MsgBox Join(v, vbNewLine)
> End Sub
>
>
> Function PrinterList(Optional PrinterNr As Integer = -1)
> Dim i%, n%, lRet&, sBuf$, sOn$, sPort$, aPrn
> Const lSize& = 1024, sKey$ = "devices"
>
> '-----------------------------------------------------------
> 'Author: keepITcool 1st posted nl.office.excel 23/10/2003
> 'Function returns a zerobased array of installed printers
> 'include for xl97: supplemental functions split/join/replace
> '-----------------------------------------------------------
>
> 'Get localized Connection string
> aPrn = Split(Excel.ActivePrinter)
> sOn = " " & aPrn(UBound(aPrn) - 1) & " "
> 'Read Printers
> sBuf = Space(lSize)
> lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf, lSize)
> If lRet = 0 Then Exit Function
> 'Make Array from String
> aPrn = Split(Left(sBuf, lRet - 1), vbNullChar)
> 'Add Port for each Printer
> For n = LBound(aPrn) To UBound(aPrn)
> sBuf = Space(lSize)
> lRet = GetProfileString(sKey, aPrn(n), vbNullString, sBuf, lSize)
> sPort = Mid(sBuf, InStr(sBuf, ",") + 1, lRet - InStr(sBuf, ","))
> aPrn(n) = aPrn(n) & sOn & sPort
> Next
> 'Sort
> qSort aPrn
> 'Return the results
> If PrinterNr = -1 Then PrinterList = aPrn Else PrinterList = aPrn( _
> PrinterNr)
> End Function
>
>
> Public Sub qSort(v, Optional n& = True, Optional m& = True)
> Dim i&, j&, p, t
> If n = True Then n = LBound(v): If m = True Then m = UBound(v)
> i = n: j = m: p = v((n + m) \ 2)
> While (i <= j)
> While (v(i) < p And i < m): i = i + 1: Wend
> While (v(j) > p And j > n): j = j - 1: Wend
> If (i <= j) Then
> t = v(i): v(i) = v(j): v(j) = t
> i = i + 1: j = j - 1
> End If
> Wend
> If (n < j) Then qSort v, n, j
> If (i < m) Then qSort v, i, m
> End Sub
>
> '**********************************************************
> ' Optional Split function for xl97
> '**********************************************************
> #If VBA6 Then
> #Else
>
> Function Split(sText As String, _
> Optional sDelim As String = " ") As Variant
> Dim i%, sFml$, v0, v1
> Const sDQ$ = """"
>
> If sDelim = vbNullChar Then
> sDelim = Chr(7)
> sText = Replace(sText, vbNullChar, sDelim)
> End If
> sFml = "{""" & Application.Substitute(sText, sDelim, """,""") & """}"
> v1 = Evaluate(sFml)
> 'Return 0 based for compatibility
> ReDim v0(0 To UBound(v1) - 1)
> For i = 0 To UBound(v0): v0(i) = v1(i + 1): Next
>
> Split = v0
>
> End Function
> #End If
>
> keepITcool
>
> < email : keepitcool chello nl (with @ and .) >
> < homepage: http://members.chello.nl/keepitcool >
> ---------------------------------------------------
>
> He also recently stated that in the split function
>
> v1 = Evaluate(sFml)
> 'Return 0 based for compatibility
> ReDim v0(0 To UBound(v1) - 1)
> For i = 0 To UBound(v0): v0(i) = v1(i + 1): Next
>
> Split = v0
>
> could be replaced by
> v1 = Evaluate(sFml)
> ReDim Preserve v1(0 To UBound(v1) - 1)
> Split = v1
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Bill Kuunders" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks Tom
>> I've seen a lot of your answers over the years so I do believe you.
>> What can I say...................It is more than likely way above my
>> head.
>> I'm just disappointed that excel couldn't do the same.
>> I still like excel though.
>>
>> Regards
>> Bill K
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Yes, word is smarter. Printing is through the application, not VBA and
>> > excel needs the port.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> > "Bill Kuunders" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> Jezbel from the word vba newsgroup wrote...........
>> >>
>> >> The difference must lie elsewhere. VBA is exactly the same in both
>> >> > cases -- literally: it's the same library.
>> >>
>> >> My question remains................
>> >> >
>> >> > "Bill Kuunders" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> The code below is for an excel routine to find the port number for
>> >> >> a
>> >> >> network printer.
>> >> >> The portnumbers are selected at random when logging on to the
> network.
>> >> >> For us it is normally ne01 ne02 ne03 or ne04
>> >> >>
>> >> >>
>> >> >> Dim OldPname As String
>> >> >> Dim TempPname As String
>> >> >> OldPname = Application.ActivePrinter
>> >> >> For J = 0 To 99
>> >> >> On Error Resume Next
>> >> >> If J < 10 Then
>> >> >> TempPname = "Adobe PDF on Ne0" & J & ":"
>> >> >> Application.ActivePrinter = TempPname
>> >> >> ElseIf J >= 10 Then
>> >> >> TempPname = "Adobe PDF on Ne" & J & ":"
>> >> >> Application.ActivePrinter = TempPname
>> >> >> End If
>> >> >> If Application.ActivePrinter = TempPname Then
>> >> >> Exit For
>> >> >> End If
>> >> >> Next J
>> >> >> Application.ActivePrinter = "TempPname"
>> >> >> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
>> >> >> =TempPname, Collate:=True
>> >> >> Application.ActivePrinter = OldPname
>> >> >>
>> >> >>
>> >> >> The code works perfect.
>> >> >> My question is..............
>> >> >> Why does word not need to go through the loop of allocating numbers
>> > until
>> >> >> there is a match?
>> >> >>
>> >> >> In word these two lines return the right printer including the
>> >> >> right
>> > port
>> >> >>
>> >> >> TempPname = "Adobe PDF "
>> >> >> Application.ActivePrinter = TempPname
>> >> >>
>> >> >> Do I really have to admit that word is smarter than excel?
>> >> >>
>> >> >>
>> >> >> Regards
>> >> >> Bill Kuunders
>> >>
>> >>
>> >
>> >
>>
>>
>
>

Bill Kuunders · 02-07-2005, 04:06 PM

By the way the brute force testing code was written up by E Ehren , some
time last year.
Bill K

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> You don't have to take my word. You can test it yourself. Both Excel and
> Word support application.ActivePrinter
>
> In word
> ? application.ActivePrinter
> \\006-pm2-fs02\PRN4N3 on NE02:
> ' now change to another manually
> ? application.ActivePrinter
> \\006-pm2-fs02\prn4n1 on NE01:
>
> Now set it back with code
>
> application.ActivePrinter = "\\006-pm2-fs02\PRN4N3"
>
>
> In Excel
> ? application.ActivePrinter
> \\006-pm2-fs02\prn4n1 on Ne01:
> ' now change to another manually
> ? application.ActivePrinter
> \\006-pm2-fs02\PRN4N3 on Ne02:
>
> Now set it back with code:
> Application.ActivePrinter = "\\006-pm2-fs02\prn4n1"
> Raises an error
> but this works:
>
> Application.ActivePrinter = "\\006-pm2-fs02\prn4n1 on Ne01:"
>
> of course in word, adding the "on Ne01:" also works, so Word is more
> flexible.
>
> From what I have read in a discussion here, Access is different again.
>
> While I like your idea of brute force testing, KeepItCool has posted this
> code for Excel which seems to work.
>
>
> http://groups.google.com/groups?selm...210&output=gpl
>
>
> Newsgroups: microsoft.public.excel.programming
> Subject: Re: COMBOBOX WITH PRINTERS LIST
> From: keepitcool <[email protected]>
> References: <[email protected]>
> Organization: xlSupport.com
> Message-ID: <[email protected]>
> User-Agent: Xnews/5.04.25
> Lines: 119
> Date: Wed, 26 May 2004 06:45:47 GMT
> NNTP-Posting-Host: 24.132.231.213
> X-Complaints-To: [email protected]
> X-Trace: amsnews02.chello.com 1085553947 24.132.231.213 (Wed, 26 May 2004
> 08:45:47 MEST)
> NNTP-Posting-Date: Wed, 26 May 2004 08:45:47 MEST
>
>
> Christian..
>
> the function below returns an array of the installed printers
> ( in complete localized strings)
> Can be called when you're filling a combobox on a form or in a dropdown
> from worksheet as in demo (as in the demo)
>
> Sub Demo()
> Dim v As Variant
> Dim i As Long
> Workbooks.Add xlWBATWorksheet
> v = PrinterList
> For i = LBound(v) To UBound(v)
> Cells(i + 1, 1) = v(i)
> Cells(i + 1, 2).Formula = "=printerlist(" & i & ")"
> Next
> Cells(1, 3).Resize(i, 1).FormulaArray = "=transpose(printerlist())"
> Cells(1, 4).Resize(1, i).FormulaArray = "=printerlist()"
> Cells(1, 1).CurrentRegion.EntireColumn.AutoFit
> MsgBox Join(v, vbNewLine)
> End Sub
>
>
> Function PrinterList(Optional PrinterNr As Integer = -1)
> Dim i%, n%, lRet&, sBuf$, sOn$, sPort$, aPrn
> Const lSize& = 1024, sKey$ = "devices"
>
> '-----------------------------------------------------------
> 'Author: keepITcool 1st posted nl.office.excel 23/10/2003
> 'Function returns a zerobased array of installed printers
> 'include for xl97: supplemental functions split/join/replace
> '-----------------------------------------------------------
>
> 'Get localized Connection string
> aPrn = Split(Excel.ActivePrinter)
> sOn = " " & aPrn(UBound(aPrn) - 1) & " "
> 'Read Printers
> sBuf = Space(lSize)
> lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf, lSize)
> If lRet = 0 Then Exit Function
> 'Make Array from String
> aPrn = Split(Left(sBuf, lRet - 1), vbNullChar)
> 'Add Port for each Printer
> For n = LBound(aPrn) To UBound(aPrn)
> sBuf = Space(lSize)
> lRet = GetProfileString(sKey, aPrn(n), vbNullString, sBuf, lSize)
> sPort = Mid(sBuf, InStr(sBuf, ",") + 1, lRet - InStr(sBuf, ","))
> aPrn(n) = aPrn(n) & sOn & sPort
> Next
> 'Sort
> qSort aPrn
> 'Return the results
> If PrinterNr = -1 Then PrinterList = aPrn Else PrinterList = aPrn( _
> PrinterNr)
> End Function
>
>
> Public Sub qSort(v, Optional n& = True, Optional m& = True)
> Dim i&, j&, p, t
> If n = True Then n = LBound(v): If m = True Then m = UBound(v)
> i = n: j = m: p = v((n + m) \ 2)
> While (i <= j)
> While (v(i) < p And i < m): i = i + 1: Wend
> While (v(j) > p And j > n): j = j - 1: Wend
> If (i <= j) Then
> t = v(i): v(i) = v(j): v(j) = t
> i = i + 1: j = j - 1
> End If
> Wend
> If (n < j) Then qSort v, n, j
> If (i < m) Then qSort v, i, m
> End Sub
>
> '**********************************************************
> ' Optional Split function for xl97
> '**********************************************************
> #If VBA6 Then
> #Else
>
> Function Split(sText As String, _
> Optional sDelim As String = " ") As Variant
> Dim i%, sFml$, v0, v1
> Const sDQ$ = """"
>
> If sDelim = vbNullChar Then
> sDelim = Chr(7)
> sText = Replace(sText, vbNullChar, sDelim)
> End If
> sFml = "{""" & Application.Substitute(sText, sDelim, """,""") & """}"
> v1 = Evaluate(sFml)
> 'Return 0 based for compatibility
> ReDim v0(0 To UBound(v1) - 1)
> For i = 0 To UBound(v0): v0(i) = v1(i + 1): Next
>
> Split = v0
>
> End Function
> #End If
>
> keepITcool
>
> < email : keepitcool chello nl (with @ and .) >
> < homepage: http://members.chello.nl/keepitcool >
> ---------------------------------------------------
>
> He also recently stated that in the split function
>
> v1 = Evaluate(sFml)
> 'Return 0 based for compatibility
> ReDim v0(0 To UBound(v1) - 1)
> For i = 0 To UBound(v0): v0(i) = v1(i + 1): Next
>
> Split = v0
>
> could be replaced by
> v1 = Evaluate(sFml)
> ReDim Preserve v1(0 To UBound(v1) - 1)
> Split = v1
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Bill Kuunders" <[email protected]> wrote in message
> news:[email protected]...
>> Thanks Tom
>> I've seen a lot of your answers over the years so I do believe you.
>> What can I say...................It is more than likely way above my
>> head.
>> I'm just disappointed that excel couldn't do the same.
>> I still like excel though.
>>
>> Regards
>> Bill K
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > Yes, word is smarter. Printing is through the application, not VBA and
>> > excel needs the port.
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> > "Bill Kuunders" <[email protected]> wrote in message
>> > news:%[email protected]...
>> >> Jezbel from the word vba newsgroup wrote...........
>> >>
>> >> The difference must lie elsewhere. VBA is exactly the same in both
>> >> > cases -- literally: it's the same library.
>> >>
>> >> My question remains................
>> >> >
>> >> > "Bill Kuunders" <[email protected]> wrote in message
>> >> > news:[email protected]...
>> >> >> The code below is for an excel routine to find the port number for
>> >> >> a
>> >> >> network printer.
>> >> >> The portnumbers are selected at random when logging on to the
> network.
>> >> >> For us it is normally ne01 ne02 ne03 or ne04
>> >> >>
>> >> >>
>> >> >> Dim OldPname As String
>> >> >> Dim TempPname As String
>> >> >> OldPname = Application.ActivePrinter
>> >> >> For J = 0 To 99
>> >> >> On Error Resume Next
>> >> >> If J < 10 Then
>> >> >> TempPname = "Adobe PDF on Ne0" & J & ":"
>> >> >> Application.ActivePrinter = TempPname
>> >> >> ElseIf J >= 10 Then
>> >> >> TempPname = "Adobe PDF on Ne" & J & ":"
>> >> >> Application.ActivePrinter = TempPname
>> >> >> End If
>> >> >> If Application.ActivePrinter = TempPname Then
>> >> >> Exit For
>> >> >> End If
>> >> >> Next J
>> >> >> Application.ActivePrinter = "TempPname"
>> >> >> ThisWorkbook.Worksheets(1).PrintOut , Copies:=1,ActivePrinter: _
>> >> >> =TempPname, Collate:=True
>> >> >> Application.ActivePrinter = OldPname
>> >> >>
>> >> >>
>> >> >> The code works perfect.
>> >> >> My question is..............
>> >> >> Why does word not need to go through the loop of allocating numbers
>> > until
>> >> >> there is a match?
>> >> >>
>> >> >> In word these two lines return the right printer including the
>> >> >> right
>> > port
>> >> >>
>> >> >> TempPname = "Adobe PDF "
>> >> >> Application.ActivePrinter = TempPname
>> >> >>
>> >> >> Do I really have to admit that word is smarter than excel?
>> >> >>
>> >> >>
>> >> >> Regards
>> >> >> Bill Kuunders
>> >>
>> >>
>> >
>> >
>>
>>
>
>