Set VBA for default printer.

[mrattana]

To All Experts,

I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?

Thank you.

 

[melodramatic]

I'm not sure - when I initially wrote the macro, I did a new macro and sent the print command - that's where I got it from.

To solve my problem, I've just manually pushed one print to that docuPrinter each time, then my Excel will continue to default to that while I run through the various files to print via the macro. It's just frustrating that it's not working for me.

How do I find the full network address of the printer?

 

[Joe Was]

It will be listed on that printers Properties.

 

[michellet]

To All Experts,

I need an assistance in setting up a VBA for the default printer in my office. The issue is I want my spreadsheet to be set up to wherever I open the spreadsheet at any computers in the facility. I want the sheet to be printing out directly at the printer in my office only. Is there a way to do this?

Thank you.

Using KISS, I finally solved this problem by using the Workbook_Open code below and creating a Printer Control sheet where a chart looks up the computer name to select the correct printer for that computer. The Printer Control tab is brightly highlighted to help users remember to update the chart when a new computer and/or printer is added or replaced. Standard macros for Get Computer Name and Get Printer Name are used to power those buttons. To get started, Type 1 into cell AP1. Formulas are: For Z1 =NOW() For A27 =IF(Z1>0, GetComputerName(), " ") For B27 =IFERROR(VLOOKUP(A27,A19:B25,2,FALSE), Z2) For B33 =LEFT(B27,LEN(B27)-2) For B34 and fill down =CONCATENATE(B$33,C34) For C34 and type next number thru C39 '0: Sheet1, cell A1 is where user input will begin. The end part of the code is just a belt and suspenders type of thing for shared computers/files to prevent novice users from getting sidetracked.

Private Sub Workbook_Open()

On Error Resume Next
If Sheet3.Range("AP1") = 1 Then
Application.ActivePrinter = Sheet3.Range("B27").Value
If Error Then
Application.ActivePrinter = Sheet3.Range("B34").Value
End If
If Error Then
Application.ActivePrinter = Sheet3.Range("B35").Value
End If
If Error Then
Application.ActivePrinter = Sheet3.Range("B36").Value
End If
If Error Then
Application.ActivePrinter = Sheet3.Range("B37").Value
End If
If Error Then
Application.ActivePrinter = Sheet3.Range("B38").Value
End If
If Error Then
Application.ActivePrinter = Sheet3.Range("B39").Value
Application.GoTo Sheet1.Range("A1")
End If
End If

ActiveWindow.View = xlPageLayoutView
ActiveWindow.LargeScroll up:=1
Application.SendKeys "{NUMLOCK}", True
ActiveWorkbook.Save

End Sub