Print code

V

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)"
 
D

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
 
D

Dave Peterson

I refered to the printer's name incorrectly.

It should have been:

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

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


Dave Peterson said:
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
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top