Excel Macro Application.ActivePrinter problem 2

[netgui68]

I have a macro that formats reports and prints to a shared network printer, many users use this macro. The problem is that the NE port is different from machine to machine and occasionally, a machine already configured changes the assigned NE port value. I have spent all day long with various snippets of code trying to make this dynamic so we do not have to go to each workstation run some code to find out the correct NE value and edit the macro code and correct it. I would appreciate any help you could give me with this. Thank you in advance.

Here is my code with just a static entry:

Sub MacroPrint()
'Macro Print & Format
'Macro programmed by Ken Hamaker MIS Dept 2/12/2009
'Intentions are to streamline code and resolve dynamic NE port allocation where
'Application.ActivePrinter would fail its assignment when the port changed


'Unprotect the worksheet and Select all the Cells

ActiveSheet.Unprotect
Cells.Select

'Set the Active Printer and run the routine to make sure it is defined correctly
Application.ActivePrinter = "\\PrintServer\Ken's Office on Ne02:"


'Define the print area and print options

ActiveSheet.PageSetup.PrintArea = Selection.Address


With ActiveSheet.PageSetup

.PrintTitleRows = "$1:$1"
.PrintTitleColumns = ""
.LeftHeader = ""
.CenterHeader = "&""MS Sans Serif,Bold""&16&F"
.RightHeader = ""
.LeftFooter = "&D &T"
.CenterFooter = "&F"
.RightFooter = "&P of &N"
.LeftMargin = Application.InchesToPoints(0)
.RightMargin = Application.InchesToPoints(0)
.TopMargin = Application.InchesToPoints(0.5)
.BottomMargin = Application.InchesToPoints(0.5)
.HeaderMargin = Application.InchesToPoints(0)
.FooterMargin = Application.InchesToPoints(0)
.PrintHeadings = True
.PrintGridlines = True
.PrintComments = xlPrintInPlace
.CenterHorizontally = True
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperTabloid
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 95
.PrintErrors = xlPrintErrorsDisplayed

End With

'Hide Column A
Range("A1").Select
Selection.EntireColumn.Hidden = True

'Give the user a print preview
ActiveWindow.SelectedSheets.PrintPreview


End Sub

 

[paulmatt]

You could loop through the active printers and look for one with "ken's" in the name.

Or if the expected printer name isn't there in the available printers, pop up a form which allows the user to select the printer from a drop down list. The result could be saved in the registry or a text file so they don't get asked again next time.

http://word.mvps.org/FAQs/MacrosVBA/AvailablePrinters.htm

Paul Rigby

http://www.awgarstone.com

VBA, C#, Delphi

 

[SkipVought]

Paul,

Great tip! ==>*

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]