Consulting

Results 1 to 11 of 11

Thread: Sleeper: Print to different printer based on cell value

  1. 06-11-2005, 07:34 AM #1
    Anne Troy
    Anne Troy is offline
    Site Admin
    The Princess     VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location

    Sleeper: Print to different printer based on cell value

    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 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet 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 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet 9100 on Ne03:", Collate:=True
Sheets("print2").Select
Application.ActivePrinter = "\\MYRACING-LA\OffieJet 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet 9100 on Ne03:", Collate:=True
Sheets("print 1").Select
Application.ActivePrinter = "\\MYRACING-LA\OffieJet 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet 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 9100 on Ne03:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\MYRACING-LA\OffieJet 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
    ~Anne Troy
    Reply With Quote Reply With Quote
  2. 06-11-2005, 08:37 AM #2
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Hi Dreamboat.
    Do you have the Ne?? value for your other printer?
    On which sheet are your cell located?
    Reply With Quote Reply With Quote
  3. 06-11-2005, 08:38 AM #3
    Anne Troy
    Anne Troy is offline
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Sorry, no. Can we fake it for now?
    ~Anne Troy
    Reply With Quote Reply With Quote
  4. 06-11-2005, 08:42 AM #4
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Yes, no problem. On which sheets are your cells located.
    Reply With Quote Reply With Quote
  5. 06-11-2005, 08:47 AM #5
    Anne Troy
    Anne Troy is offline
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Thanks, MD... they're on a sheet named Data_Entry
    ~Anne Troy
    Reply With Quote Reply With Quote
  6. 06-11-2005, 08:58 AM #6
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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 Color Laser on Ne02:"
    Else
        MyPrinter = "\\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
    Reply With Quote Reply With Quote
  7. 06-11-2005, 09:01 AM #7
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Last minute thought. Will this routine be used on different computers, because the "Ne" numbers may be different?
    Reply With Quote Reply With Quote
  8. 06-11-2005, 09:01 AM #8
    Anne Troy
    Anne Troy is offline
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    Thanks, MD. We'll give it a shot.
    ~Anne Troy
    Reply With Quote Reply With Quote
  9. 06-11-2005, 09:02 AM #9
    Anne Troy
    Anne Troy is offline
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    I don't think it will matter. They're using the first procedure I posted already.
    ~Anne Troy
    Reply With Quote Reply With Quote
  10. 06-11-2005, 10:15 AM #10
    mdmackillop
    mdmackillop is offline
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Your question prompted me to send in a KB for excel printing, which might be of use in such circumstances. Its in For Approval.
    Reply With Quote Reply With Quote
  11. 06-11-2005, 10:20 AM #11
    Anne Troy
    Anne Troy is offline
    Site Admin
    The Princess VBAX Guru Anne Troy's Avatar
    Joined
    May 2004
    Location
    Arlington Heights, IL
    Posts
    2,530
    Location
    I see that.

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

    www.officearticles.com

    ~Anne Troy
    Reply With Quote Reply With Quote
« Previous Thread | Next Thread »

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules