PDA

View Full Version : Sleeper: Print to different printer based on cell value



Anne Troy
06-11-2005, 07:34 AM
That's it.

How can I change the following code to print \\ MYRACING-LA\HP Color Laser if I28 or K28 are NOT blank?


Sub DataOutput()
' DataOutput Macro'
' Keyboard Shortcut: Ctrl+q
ChDrive "C"
ChDir "C:\MY"
' Sheets("Job_Sheet").Select
Application.ActivePrinter = "\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:", Collate:=True
Sheets("Data_Entry").Select
Range("B10").Select
FORGE = ActiveCell.Text
Sheets("Pro-Engineer").Select
Sheets("Pro-Engineer").Copy
ChDrive "A"
ActiveWorkbook.SaveAs Filename:=FORGE, FileFormat:=xlText, _
CreateBackup:=False
ChDrive "C"
ActiveWorkbook.Close
Sheets("Data_Entry").Select
Range("M28").Select
' If ActiveCell = "Y" Then
ChDir "C:\MY"
Sheets("Data_Entry").Select
Sheets("Job_Sheet").Select
Application.ActivePrinter = "\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:", Collate:=True
Sheets("print2").Select
Application.ActivePrinter = "\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:", Collate:=True
Sheets("print 1").Select
Application.ActivePrinter = "\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:", Collate:=True
Sheets("Data_Entry").Select
Range("L6").Select
If ActiveCell = "WIR" Then
Sheets("print 3").Select
Application.ActivePrinter = "\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet (file:///MYRACING-LAOffieJet) 9100 on Ne03:", Collate:=True
End If
Sheets("Data_Entry").Select
Range("A609").Select
If ActiveCell = "MANUAL VP'S." Then
myvar = Range("Data_Entry!C3").Value & ".MCL"
Sheets("SmartCAM2").Select
ChDrive "C"
ChDir "C:\MY\SMARTCAM"
Sheets("smartCAM2").Copy
ActiveWorkbook.SaveAs Filename:=myvar, FileFormat:=xlText, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
myvar = Range("Data_Entry!C3").Value & ".MCL"
Sheets("SmartCAM").Select
ChDir "C:\MY\SMARTCAM"
Sheets("smartCAM").Copy
ActiveWorkbook.SaveAs Filename:=myvar, FileFormat:=xlText, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
ActiveWorkbook.Close
Sheets("Data_Entry").Select
ChDir "C:\MY"
MYSUM = Application.Run("CONVERTT2!CONVERTT2")
MsgBox "MACRO RESULT: " & MYSUM
Range("C3").Select
myvar = ActiveCell.Value
ChDrive "O:"
ActiveWorkbook.SaveAs Filename:=myvar, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False
ChDrive "C:"
ChDir "C:\MY"
ActiveWorkbook.Close
ActiveWorkbook.Close Filename:="MYAVE2.XLS"
End Sub

mdmackillop
06-11-2005, 08:37 AM
Hi Dreamboat.
Do you have the Ne?? value for your other printer?
On which sheet are your cell located?

Anne Troy
06-11-2005, 08:38 AM
Sorry, no. Can we fake it for now?

mdmackillop
06-11-2005, 08:42 AM
Yes, no problem. On which sheets are your cells located.

Anne Troy
06-11-2005, 08:47 AM
Thanks, MD... they're on a sheet named Data_Entry

mdmackillop
06-11-2005, 08:58 AM
Try the following. As you are using the same printer all the way through, I've removed the multiple references to it, and the initial setting should suffice.



Sub DataOutput()
' DataOutput Macro'
' Keyboard Shortcut: Ctrl+q
Dim MyPrinter As String
With Sheets("Data_Entry")
If [I28] <> "" Or [K28] <> "" Then
MyPrinter = "\\MYRACING-LA\HP (file://MYRACING-LA/HP) Color Laser on Ne02:"
Else
MyPrinter = "\\MYRACING-LA\OffieJet (file://MYRACING-LA/OffieJet) 9100 on Ne03:"
End If
End With
'Check and exit - Remove after testing
MsgBox MyPrinter
Exit Sub
ChDrive "C"
ChDir "C:\MY"
' Sheets("Job_Sheet").Select
Application.ActivePrinter = MyPrinter
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Data_Entry").Select
Range("B10").Select
FORGE = ActiveCell.Text
Sheets("Pro-Engineer").Select
Sheets("Pro-Engineer").Copy
ChDrive "A"
ActiveWorkbook.SaveAs Filename:=FORGE, FileFormat:=xlText, _
CreateBackup:=False
ChDrive "C"
ActiveWorkbook.Close
Sheets("Data_Entry").Select
Range("M28").Select
' If ActiveCell = "Y" Then
ChDir "C:\MY"
Sheets("Data_Entry").Select
Sheets("Job_Sheet").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("print2").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("print 1").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Data_Entry").Select
Range("L6").Select
If ActiveCell = "WIR" Then
Sheets("print 3").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End If
Sheets("Data_Entry").Select
Range("A609").Select
If ActiveCell = "MANUAL VP'S." Then
myvar = Range("Data_Entry!C3").Value & ".MCL"
Sheets("SmartCAM2").Select
ChDrive "C"
ChDir "C:\MY\SMARTCAM"
Sheets("smartCAM2").Copy
ActiveWorkbook.SaveAs Filename:=myvar, FileFormat:=xlText, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Else
myvar = Range("Data_Entry!C3").Value & ".MCL"
Sheets("SmartCAM").Select
ChDir "C:\MY\SMARTCAM"
Sheets("smartCAM").Copy
ActiveWorkbook.SaveAs Filename:=myvar, FileFormat:=xlText, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
End If
ActiveWorkbook.Close
Sheets("Data_Entry").Select
ChDir "C:\MY"
MYSUM = Application.Run("CONVERTT2!CONVERTT2")
MsgBox "MACRO RESULT: " & MYSUM
Range("C3").Select
myvar = ActiveCell.Value
ChDrive "O:"
ActiveWorkbook.SaveAs Filename:=myvar, FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=True, _
CreateBackup:=False
ChDrive "C:"
ChDir "C:\MY"
ActiveWorkbook.Close
ActiveWorkbook.Close Filename:="MYAVE2.XLS"
End Sub

mdmackillop
06-11-2005, 09:01 AM
Last minute thought. Will this routine be used on different computers, because the "Ne" numbers may be different?

Anne Troy
06-11-2005, 09:01 AM
Thanks, MD. We'll give it a shot.

Anne Troy
06-11-2005, 09:02 AM
I don't think it will matter. They're using the first procedure I posted already.

mdmackillop
06-11-2005, 10:15 AM
Your question prompted me to send in a KB for excel printing, which might be of use in such circumstances. Its in For Approval.

Anne Troy
06-11-2005, 10:20 AM
I see that. :)

I've been trying to do the same every time I answer a question on a forum:

www.officearticles.com (http://www.officearticles.com)

:)