Excel 2010 - Change printer depending on choice 1

[dvirgint]

Hello,

I've made a macro which scrapes information from Extra! Attachmate. The information is then printed onto a form which is located in one of three trays in a printer. One of the pieces of information is the person's language (Ex. 'English', 'French', 'Spanish'), and this information is stored in abreviation form, such as 'EN', 'FR' and 'SP'.

From what I've read, the easiest way to have a macro print to a specific tray is to create a separate printer for each tray and then change the default printer depending on the tray needed.

I have 3 .INF files set up which contain network printer addresses and I use the following code to get the macro to select the printer depending on the language, so for the sake of time and space, I'll include the code for the English part:

The English .INF contains:\\SERVER1\Lexmark [highlight]on Ne03:[/highlight]

And the code goes like this:

'STDprinter = Application.ActivePrinter
'If tpLang = "EN" Then
'    ConfigFile$ = "T:\SERVER\\Macros\Printer1.inf"
'    If Dir$(ConfigFile$) <> "" Then
'        Open ConfigFile$ For Input As #1
'        Line Input #1, PrinterName$
'        Close #1'        Application.ActivePrinter = PrinterName$
'    Else
'        STDprinter
'    End If            
'    Worksheets("Print").Select
'    ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$28"
'    ActiveSheet.PrintOut  
'Elseif
'    [i]code for French and Spanish go here[/i]
'End If
'Application.ActivePrinter = STDprinter
'Workbooks("INNS.xls").Close SaveChanges:=False

My problem is with the printer name itself. You'll notice that the printer name has a "on Ne03:" at the end. This seems to change from time to time depending on the computer it is being used on. Does anyone know how to standardize this, or to simply remove it?

Thanks for your help.
dvirgint

 

[SkipVought]

oui!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Blizzard9999]

I'm having similar problems!

I've been getting the printer address for years with office 2003. Similar what you said: 1) turning on the macro recorder, 2) Office Button 3) Print 4) Print 5) Change the Printer Selection 6) CANCEL and repeat as necessary to get three printers.

I just received a new computer with Office 2010. I tried these steps and I only get:
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
IgnorePrintAreas:=False

I don't understand why I can't get the printer address anymore? Makes me wonder if something is messed up. I can guess what the Ne#'s are, but it is frustrating and takes longer.

 

[Blizzard9999]

In case anyone else is running into this. I came across another way to print with out the Ne#.

You would need to fill in your printer address on this part \\Sever\SHARP MX-2600N PCL6 on Ne , be sure to include (add) the "on Ne" part.


oldprinter = Application.ActivePrinter

For i = 0 To 99
curNePrint = Format(i, "00")
On Error Resume Next

Application.ActivePrinter = "\\Sever\SHARP MX-2600N PCL6 on Ne" & curNePrint & ":"
Next i


ActiveWindow.Selection.PrintOut Copies:=1
Application.ActivePrinter = oldprinter

 

[SkipVought]

What was "another way"?

Would you care to elaborate on the difference, for the benefit of other Tek-Tip members that might be browsing this thread?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Blizzard9999]

I included it. Same idea basically as your solution. I'm not an advanced user, so I found this one easier to understand once I had studied your posts.

Here it is again:

oldprinter = Application.ActivePrinter

For i = 0 To 99
curNePrint = Format(i, "00")
On Error Resume Next

Application.ActivePrinter = "\\Sever\SHARP MX-2600N PCL6 on Ne" & curNePrint & ":"
Next i


ActiveWindow.Selection.PrintOut Copies:=1
Application.ActivePrinter = oldprinter

 

[PHV]

A faster way:

oldprinter = Application.ActivePrinter
On Error Resume Next
For i = 0 To 99
  Err.Clear
  curNePrint = Format(i, "00")
  Application.ActivePrinter = "\\Sever\SHARP MX-2600N PCL6 on Ne" & curNePrint & ":"
  If Err.Number = 0 Then Exit For
Next i
ActiveWindow.Selection.PrintOut Copies:=1
Application.ActivePrinter = oldprinter

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Now THAT is better!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Blizzard9999]

Ok, now I see what you were talking about! I forgot code tags! Those are hard to find on this forum.