Excel Macro - change printer

[Guest]

I am trying to set up a print macro which automatically print to a
pre-selected printer (LABEL) instead of the default printer on the computer
(KYOCERA).

When complete, the macro must switch back to the default printer (KYOCERA).
This macro is used on several machines in the office and each machine has a
different Ne__ numbers for the the same printers.

ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
Application.ActivePrinter = "label on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"label on Ne05:", Collate:=True
Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"

The Ne__ number appears to be the printer's installation order. When you
change printers (as we have needed to do), the Ne number changes rendering
the macro useless. I have had to change these things SEVERAL TIMES on
SEVERAL machines.

I want to find a way to define the KYOCERA and LABEL printers without using
those Ne__ numbers so the same macro will work throughout the office and if
one of the available printers is uninstalled, order changes, the macro will
continue to work. All the machines in question use Windows XP Pro on a
network. This problem did not appear when we were on windows 98!

 

[Guest]

One possibility is to use API functions to return an array of printer names

http://www.word.mvps.org/FAQs/MacrosVBA/AvailablePrinters.htm

Then, loop through the array and see which one contains the word "label" or
"kyocera" (check VBA help for InStr function).

 

[Guest]

I just remembered Tom Ogilvy had posted a solution that does not involve API.

http://www.microsoft.com/office/com...&p=1&tid=d2527843-9097-4f73-bd1c-ccecf74fe362

 

[Guest]

I've looked through those responses (thank you for the references), but I do
not understand enough about them to implement, I am sorry to say! My macros
are basically written with record then I edit as needed.

I found a site that had a promising answer seen here:
http://www.erlandsendata.no/english/index.php?d=envbaprintchangeprinter

Would anyone be willing to coach me in combining my current macro with it?
Assistance on this would be very greatly appreciated.

My current macro (for one machine, changes from default Kyocera to Label
printer and back):

Range("A7:M18").Select
With Selection.Font
.Name = "10 CPI Utility"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
End With
Range("P8:Y11").Select
ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
Application.ActivePrinter = "label on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"label on Ne05:", Collate:=True
Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"
Range("A2").Select
End Sub

 

[Pete_UK]

I had to change printers in a macro which outputted an Excel file as a
PDF file - here's a fragment of the code, for what it's worth:

my_printer = Application.ActivePrinter
Application.ActivePrinter = "Adobe PDF on Ne02:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, _
ActivePrinter:="Adobe PDF on Ne02:", Collate:=True
Application.ActivePrinter = my_printer

Note the first and last line of the fragment, which you might want to
use in your code rather than explicitly setting the Kyocera printer.

Hope this helps.

Pete

 

[Guest]

I can see the value of that.
But unfortunately, it still leaves me with defining the "on Ne02" etc for
the printer I am asking to print. That's just the point. I am trying to get
away from using that Ne02, Ne03, etc as that changes on our network, breaking
the macro.

-Samantha

 

[Guest]

That may bring you back to using either API or Tom's suggestion. You have to
capture all of the network printer names, search through them to see which
one has "Label" in the name and set the active printer based on that. You
might check VBA help for a Printers collection. I know Excel 2000 does not
have this collection, but I don't know if it is included in later versions.

If no Printers collection, you have to create a printers collection. I've
reposted Tom Ogilvy's code with the changes I think you'll need (you'll have
to add the code to print what you need with the "Label" printer). I don't
have multiple printers, so I couldn't fully test it.


Sub Test()
Dim sConn As String
Dim WshNetwork As Object
Dim oPrinters As Object
Dim i As Long
Dim avTmp As Variant
Dim MyPrinter As String

#If VBA6 Then
avTmp = Split(Excel.ActivePrinter, " ")
#Else
avTmp = Split97(Excel.ActivePrinter, " ")
#End If

sConn = " " & avTmp(UBound(avTmp) - 1) & " "
Set WshNetwork = CreateObject("WScript.Network")
Set oPrinters = WshNetwork.EnumPrinterConnections
MyPrinter = ActivePrinter

For i = 0 To oPrinters.Count - 1 Step 2
If InStr(1, oPrinters.Item(i + 1), "Label", _
vbTextCompare) Then
ActivePrinter = oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)
Exit For
End If
Next

'Print using "Label"

ActivePrinter = MyPrinter

End Sub

Function Split97(sStr As String, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

 

[Guest]

Hit a bug when I tried to test it.
Method "Active Printer" of object "_Global" failed.
Debug highlighted these lines and I don't understand it so can not
resolve it.

ActivePrinter = oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)


I hate to take your time on a headache of mine.
I usually do not give up easily, but must say that if I knew where to go to
pay someone to write this particular macro completely and safely, I would as
I feel this project is over my head. The time saved would justify an
expenditure to get it done.

-Samantha

 

[Guest]

That's okay. I think I noticed an error in my post in the following line
(added ">0).

If InStr(1, oPrinters.Item(i + 1), "Label", _
vbTextCompare) > 0 Then

Here some additional information on WScript (Enumprinters is near the end)
http://cma.zdnet.com/book/win98prfref/ch33/ch33.htm

I'll try to explain the macro some (to the best of my understanding)- maybe
that will help you debug it.

oprinters is a paired collection of printer names. The first item is the
local name for the first printer, second item is the network name for the
first printer, and so on for each printer connection.

When the macro finds a network printer with the word "Label" in it, it
rebuilds the complete printer name.

On my machine, my printer is
HP DeskJet 930C/932C/935C on LPT1:

oprinters looks like:
Item 1 - LPT1:
Item 2 - HP DeskJet 930C/932C/935C

sConn is a variable that contains the word " on " on my machine, so the
complete printer name becomes:
ActivePrinter = oPrinters.Item(i + 1) & _
sConn & oPrinters.Item(i)

What do your printer names actually look like? Try setting up a watch for
oprinters (Debug/Add Watch - oprinters) and step through the code (F8 key)
and see what is actually in the oprinters collection. Is it possible to
build the correct printer name with the data in the variables sConn and
oprinters?

 

[Guest]

If nothing else, you can always have the user select the printer with

Application.Dialogs(xlDialogPrinterSetup).Show

 

[Guest]

Some of the folks who post to this site also do consulting work, if that is a
route you are considering. Here are Chip Pearson and Debra Dagleish's web
sites. MVPS.org also has links to Excel MVP's web sites, which you could
look through to see who else does consulting work.

http://www.cpearson.com/
http://www.contextures.com/

 

[keepITcool]

Samantha:

no need for consulting. this wheel has already been invented.

goto http://groups.google.com
search on keepitcool+list+printers

you should see a post (from me) dated Jan 22, 2005
(near the top 3rd post or so... subject "List of Printers")

it contains a function to retrieve a list of printer strings that excel
vba will readily accept as input for application.activeprinter, also in
non english environments.

 

[Samrcat]

Thank you JMB. I may end up going that route!

keepITcool,
I went to that link and saw your code. I copied it into a module and
Tried it and got hung up when I tried to run the demo. Compile error.
Sub or Function not defined. This line is highlighted yellow in
debugger
Function PrinterList(Optional PrinterNr As Integer = -1)

and GetProfileString on this line is highlighted blue:
lRet = GetProfileString(sKey, vbNullString, vbNullString, sBuf,
lSize)

I was surprised to find my topic appeared under Google Groups!

My Excel skills are quite advanced and am comfortable with macros, but
these macros are way beyond my understanding for debugging and for
modifying to get the results I need. Before investigating this I
thought that I could just use wild card letters (like **, etc) in the
printer name to replace the Ne02, Ne03, etc. But that concept was
wrong. I have a reference book at home for Excel Macros that I have
used for reference many times, but this situtation is not covered that
I saw.

I have spent so much time chasing printer settings on several
computers. Having the ability to avoid all of that would be wonderful.

 

[keepITcool]

I think you only copied the functions not the declarations.
from the code example:

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


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Samrcat wrote in

 

[Guest]

I had to be out of the office and then lost the link when I got back. Took
me a while to find this post again to reply.

Unfortunately, I am in the same place I was when I started. I take that
back, more confused than I was before, but still unable to print without
defining each individual printer using NE... I was not able to use the code
you provided in the last post. I am not sure where to put it and just got
error after error. The code that I have in one module is below, and I
believe it came from KeepItCool. It will not print so something is still
missing or I have done something wrong. Any further advice or am I just out
of luck? I really do appreciate your efforts!


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