Print code

[Very Basic User]

Hello,
I have a marco recorded to print a selection of sheets. The printer being
used is on a network and not my default printer. When I create the code on
my PC, it works fine, but as soon as another user tries to use the code from
their PC it won't work. Code is attached below, when I tried to create the
code on another PC, the section Ne04 in both lines is the only thing that
changed. Is there a common statement that could make this work regardless of
the PC from which I'm opening the file?

Application.ActivePrinter = "\\mrafp1\MRA-ADMINCP1 on Ne04:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""\\mrafp1\MRA-ADMINCP1 on
Ne04:"",,TRUE,,FALSE)"

 

[Dave Peterson]

First, I didn't go back to the xlm help to find out what those parms mean in the
xl4 macro line. In fact, I'd record a new macro that gave me the code in
"normal" VBA.

But if you don't want to try that, you could try:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundIt As Boolean
Dim CurPrinter As String

CurPrinter = Application.ActivePrinter

FoundIt = False
For iCtr = 0 To 99
On Error Resume Next
Application.ActivePrinter = "\\mrafp1\MRA-ADMINCP1 on Ne" _
& Format(iCtr, "00") & ":"
If Err.Number = 0 Then
FoundIt = True
Exit For
Else
'keep looking
Err.Clear
End If
Next iCtr
On Error GoTo 0

If FoundIt = False Then
MsgBox "No printer close to that name"
Else
'do the real work
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""" & ActivePrinter.Name _
& """,,TRUE,,FALSE)"
'and change it back
Application.ActivePrinter = CurPrinter
End If
End Sub

=======
If you're just printing the activesheet, the code could be as simple as:


If FoundIt = False Then
MsgBox "No printer close to that name"
Else
'do the real work
activesheet.printout
'and change it back
Application.ActivePrinter = CurPrinter
End If

 

[Dave Peterson]

I refered to the printer's name incorrectly.

It should have been:

ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""" & Application.ActivePrinter _
& """,,TRUE,,FALSE)"

 

[Very Basic User]

Thank you Dave,

Actually the full code is...

If I understand you correctly, I would add the long code in place of the two
lines that contain the NE04? Is that correct?

Thanks for your time!

Sub PrintAll()
'
' PrintAll Macro
'

'
Sheets("Line 1 Graph ").Select
Range("A1").Select
Application.ActivePrinter = "\\mrafp1\MRA-ADMINCP1 on Ne04:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""\\mrafp1\MRA-ADMINCP1 on
Ne04:"",,TRUE,,FALSE)"
Sheets("Line 3 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 6 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 9 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 10 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Line 2 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 5 Graph ").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 7 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 8 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Navigation").Select
End Sub

--
Thank you for your time!
John

First, I didn't go back to the xlm help to find out what those parms mean in the
xl4 macro line. In fact, I'd record a new macro that gave me the code in
"normal" VBA.

But if you don't want to try that, you could try:

Option Explicit
Sub testme()
Dim iCtr As Long
Dim FoundIt As Boolean
Dim CurPrinter As String

CurPrinter = Application.ActivePrinter

FoundIt = False
For iCtr = 0 To 99
On Error Resume Next
Application.ActivePrinter = "\\mrafp1\MRA-ADMINCP1 on Ne" _
& Format(iCtr, "00") & ":"
If Err.Number = 0 Then
FoundIt = True
Exit For
Else
'keep looking
Err.Clear
End If
Next iCtr
On Error GoTo 0

If FoundIt = False Then
MsgBox "No printer close to that name"
Else
'do the real work
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""" & ActivePrinter.Name _
& """,,TRUE,,FALSE)"
'and change it back
Application.ActivePrinter = CurPrinter
End If
End Sub

=======
If you're just printing the activesheet, the code could be as simple as:


If FoundIt = False Then
MsgBox "No printer close to that name"
Else
'do the real work
activesheet.printout
'and change it back
Application.ActivePrinter = CurPrinter
End If

 

[Dave Peterson]

Not quite all the code--but most of it.

Don't forget to read my followup with the correction.

Thank you Dave,

Actually the full code is...

If I understand you correctly, I would add the long code in place of the two
lines that contain the NE04? Is that correct?

Thanks for your time!

Sub PrintAll()
'
' PrintAll Macro
'

'
Sheets("Line 1 Graph ").Select
Range("A1").Select
Application.ActivePrinter = "\\mrafp1\MRA-ADMINCP1 on Ne04:"
ExecuteExcel4Macro _
"PRINT(1,,,1,,,,,,,,2,""\\mrafp1\MRA-ADMINCP1 on
Ne04:"",,TRUE,,FALSE)"
Sheets("Line 3 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 6 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 9 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 10 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWindow.ScrollWorkbookTabs Sheets:=1
Sheets("Line 2 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 5 Graph ").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 7 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
Sheets("Line 8 Graph").Select
Range("A1").Select
ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Navigation").Select
End Sub