Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel Macro Application.ActivePrinter problem 2

Status
Not open for further replies.

netgui68

MIS
Feb 12, 2009
1
US
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



 
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.


Paul Rigby
VBA, C#, Delphi
 



Paul,

Great tip! ==>*

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

Part and Inventory Search

Sponsor

Back
Top