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]

hi,

Turn on your macro recorder an record selecting another printer.

Post back with your recorded code if you need further help.

Skip,

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

 

[dvirgint]

Hi Skip,

This macro will be used by a team of people, and each of their printer configurations will differ with regards to the printer names' endings(on Ne03.

 

[SkipVought]

I only asked you to post your recorded macro.

It is a PROCESS.

We will also need the similar network names of each of the other two printers. If you do not know them, use your macro recprder.

POST BACK WITH YOUR RECORDED CODE et al.

Skip,

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

 

[dvirgint]

Sorry Skip, here it is.

Sub Macro5()
'
' Macro5 Macro
'

'
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
End Sub

I started recording the macro, and then I went to the printer settings and chose a different printer than the default. This is all the macro gave me.

The other two printers names are:
Lexmark_FR
Lexmark_SP

 

[SkipVought]

I did this

    Application.ActivePrinter = "\\txbafphur087\DFWPO970108748 on Ne06:"
    Application.ActivePrinter = "\\txbafphur087\DFWPO940TT1M on Ne05:"
    Application.ActivePrinter = "\\txbafphur087\DFWPO552005976 on Ne04:"

by 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.

Ball's in your court!

Skip,

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

 

[dvirgint]

Hi,

Here is what I came up with:

Application.ActivePrinter = "\\server\Lexmark_FR on Ne03:"
Application.ActivePrinter = "\\server\Lexmark_EN on Ne02:"
Application.ActivePrinter = "\\server\Lexmark_SP on Ne03:"

Thanks

 

[SkipVought]

Use these values is a Select Case structure

dim sPrinter as string

Select Case tpLang 
  Case "FR"
    sPrinter = "\\server\Lexmark_FR on Ne03:"
  Case "EN"
    sPrinter = "\\server\Lexmark_EN on Ne02:"
  Case "SP"
    sPrinter  = "\\server\Lexmark_SP on Ne03:"
End Select

Application.ActivePrinter = sPrinter

Skip,

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

 

[dvirgint]

Thanks Skip, I see what you did, however I still have a problem when the users' printer settings are not identical.

What I mean is that the address and printer name are the same (ex. \\server\Lexmark_EN), however the "on Ne03" changes on some of the computers (ex. \\server\Lexmark_EN on Ne04 instead of \\server\Lexmark_EN on Ne03). This of course means that on the computers where the address isn't exactly the same, will not work. Is there a way to have the macro ignore the excess on the end?

Thanks again Skip for your patience and help. It's much appreciated!!

 

[SkipVought]

I'd do something like this...

dim i as integer, sPrinter as string
on error resume next
sPrinter = "\\server\Lexmark_FR on Ne"    'notice no NUMBER
do 
   err.clear
   i = i + 1
   Application.ActivePrinter = sPrinter & format(i,"00")
loop while err.number <> 0

Skip,

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

 

[SkipVought]

BTW, to TEST this, I'd STEP thru this code, statement by statement, before I would RUN.

Skip,

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

 

[dvirgint]

You amaze me!! Thanks for your help. I'll test this and let you know the outcome.

 

[SkipVought]

you might want to add this stopgap criteria

'.....
   if i > 99 then Exit do
loop while err.number <> 0

Skip,

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

 

[dvirgint]

Hi,

I pieced together the two previous coding answers you gave me. I'm getting an error with the Application.Printer line - the error message says "Run-time error '1004': Method 'ActivePrinter' of object '_Application' failed".

Here is the code I came up with:

Dim sPrinter As String, i As Integer

Select Case tpLang
  Case "FR"
  
    On Error Resume Next
    sPrinter = "\\server\Lexmark_FR on Ne"    'notice no NUMBER
    Do
        Err.Clear
        i = i + 1
        Application.ActivePrinter = sPrinter & Format(i, "00")
    Loop While Err.Number <> 0

  Case "EN"
  
    'On Error Resume Next
    sPrinter = "\\server\Lexmark_EN on Ne"    'notice no NUMBER
    Do
        Err.Clear
        i = i + 1
        Application.ActivePrinter = sPrinter & Format(i, "00")
    Loop While Err.Number <> 0
    
  Case "SP"
  
    On Error Resume Next
    sPrinter = "\\server\Lexmark_SP on Ne"    'notice no NUMBER
    Do
        Err.Clear
        i = i + 1
        Application.ActivePrinter = sPrinter & Format(i, "00")
    Loop While Err.Number <> 0
    
End Select
MsgBox sPrinter
Worksheets("Imprimé").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$28"
ActiveSheet.PrintOut
MsgBox ActivePrinter

Please take note that this error occurs when I remove the "On Error Resume Next" line. Otherwise, it prints to my default printer.

Can you tell me what's going on?

 

[SkipVought]

    Select Case tpLang
      Case "FR"
      
        sPrinter = "\\server\Lexmark_FR on Ne"    'notice no NUMBER
      Case "EN"
      
        sPrinter = "\\server\Lexmark_EN on Ne"    'notice no NUMBER
      Case "SP"
        sPrinter = "\\server\Lexmark_SP on Ne"    'notice no NUMBER
    End Select
    
    On Error Resume Next
    Do
        Err.Clear
        i = i + 1
        Application.ActivePrinter = sPrinter & Format(i, "00") [highlight]& ":"[/highlight]
    Loop While Err.Number <> 0
    
    
    MsgBox sPrinter
    Worksheets("Imprimé").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$AL$28"
    ActiveSheet.PrintOut
    MsgBox ActivePrinter

Skip,

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

 

[SkipVought]

Sorry.

Skip,

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

 

[dvirgint]

No problem... as you can guess, it wasn't working and I saw your previous post before I asked another question.

Details really are important!!

Thanks so much for your help.

 

[SkipVought]

Wonderful!

Hope you achieve convergence! ;-)

Skip,

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

 

[dvirgint]

I've tested this code many times and it works very well... on my workstation only. Whenever I give the macro to someone else, the macro keeps looping when it gets to determining the "NE:" number - I have installed the printers on the other workstations as well - it's as though I've never installed these printers.

I thought maybe there is a permissions problem? Any other suggestions?

Thanks

 

[dvirgint]

I've found the problem: I have computers here with an English version of Windows installed and I have some with the French version of Windows installed. My Windows is English, so the "Lexmark_EN on Ne01:" format worked perfectly, however most of the computers are in French here, meaning that the same printer on that computer needs to be "Lexmark_EN sur Ne01:"

Make sure your computer speaks the same language as you do!!