Hi everyone

Here I am again looking for help.....

I have a reasonable well functioning code that does almost everything I
need, with one exception.

5 different people access this file and print the same report, the glitch is
that they each are assigned a different Printer Port (Via the Citrix Server)
to the printer in our office.

I was toying / hoping I could use a pup up with the 5 options for each to
select their printer ports and have that inserted into the module into the
section of code so that the Macro would run it's course.

Really looking forward to your suggestions and Ideas.

Cheers

TIA
Mick

Vacuum Sealed brought next idea :
Hi everyone

Here I am again looking for help.....

I have a reasonable well functioning code that does almost everything I
need, with one exception.

5 different people access this file and print the same report, the glitch
is that they each are assigned a different Printer Port (Via the Citrix
Server) to the printer in our office.

I was toying / hoping I could use a pup up with the 5 options for each to
select their printer ports and have that inserted into the module into the
section of code so that the Macro would run it's course.

Really looking forward to your suggestions and Ideas.

Cheers

TIA
Mick

Normally, Excel defaults to whatever printer the user has set to be
their default printer. Would this then not be their respective printer
port location?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Vacuum Sealed brought next idea :
Hi everyone

Here I am again looking for help.....

I have a reasonable well functioning code that does almost everything I need,
with one exception.

5 different people access this file and print the same report, the glitch is
that they each are assigned a different Printer Port (Via the Citrix Server)
to the printer in our office.

I was toying / hoping I could use a pup up with the 5 options for each to
select their printer ports and have that inserted into the module into the
section of code so that the Macro would run it's course.

Really looking forward to your suggestions and Ideas.

Cheers

TIA
Mick

Normally, Excel defaults to whatever printer the user has set to be
their default printer. Would this then not be their respective printer
port location?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry and thx,

As a general rule over a standard network yes, but the company uses a
Web-Based Citrix server cluster (which I hate intensely)...

Essentially, it is a bank of 5 servers, 4 slave and 1 master controller, the
Master controller, will allocate a different server depending on the load
each server is under, each of those servers has a copy of MSOffice,
conversely, each server has it's own printer port allocation, it is a
roullette wheel to get the same port address.

I can have 5 different people log in using my computer and have a different
port setting for each.

Not to worry, will have to persist with manually changing the settings in
the VBE....

Thx again...
Mick

Vacuum Sealed was thinking very hard :
Hi Garry and thx,

As a general rule over a standard network yes, but the company uses a
Web-Based Citrix server cluster (which I hate intensely)...

Essentially, it is a bank of 5 servers, 4 slave and 1 master controller, the
Master controller, will allocate a different server depending on the load
each server is under, each of those servers has a copy of MSOffice,
conversely, each server has it's own printer port allocation, it is a
roullette wheel to get the same port address.

I can have 5 different people log in using my computer and have a different
port setting for each.

Not to worry, will have to persist with manually changing the settings in the
VBE....

Thx again...
Mick

In this case, if 5 people 'log in' using your computer to run MSO from
whatever server they happen to 'catch' at login, would that MSO's
default printer not be the one assigned to that server?

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry

I would have thought so too.

I tried removing th specific end section of the path (:NE##) and just used
\\Network\DefaultPrinter but the routine haulted on it, seems I need to
specify the extra bit at the end..

eg

:NE00
:NE01
:NE02
:NE03
:NE04

I am open to alternatives

Cheers
Mick


If I don't have this included, the routine stops

Vacuum Sealed presented the following explanation :
Hi Garry

I would have thought so too.

I tried removing th specific end section of the path (:NE##) and just used
\\Network\DefaultPrinter but the routine haulted on it, seems I need to
specify the extra bit at the end..

eg

:NE00
:NE01
:NE02
:NE03
:NE04

I am open to alternatives

Cheers
Mick


If I don't have this included, the routine stops

Ok, I guess I still haven't made my point clear. Sorry about that! Let
me try once again...

When a MSO app starts up it establishes a connection to its default
printer. There should be no need to ever specify a printer for any
printout unless it's not to go to the default printer. IOW, Excel knows
where the printer is because it already established connection to it at
startup, so just using PrintOut should work without issue.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Gary

Sorry it has taken a while to respond...

Can you help put the following <Snippiet into context so that it will work
regardless of who uses it please....

With ActiveSheet.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = "HDC"
.RightHeader = myDate
.FitToPagesWide = 1
End With

Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
ActiveWindow.SelectedSheets.PrintPreview

With ActiveSheet.PageSetup
.CenterHeader = ""
.PrintArea = ""
End With

Thx
Mick

Whilst I have your attention Garry

You may be able to shed some light into why it is that the

With ActiveSheet.PageSetup does not work properly.

I have 4 report and each of them is almost identical say for a couple of
minor differences.

This is the 1st:

With ActiveSheet.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = "HDC"
.RightHeader = myDate
.FitToPagesWide = 1
End With

Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
ActiveWindow.SelectedSheets.PrintPreview

With ActiveSheet.PageSetup
.CenterHeader = ""
.PrintArea = ""
End With

This is the second:

With ActiveSheet.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = "NDC"
.RightHeader = myDate
.FitToPagesWide = 1
End With

Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
ActiveWindow.SelectedSheets.PrintPreview

With ActiveSheet.PageSetup
.CenterHeader = ""
.PrintArea = ""
End With

Problem is, when I run the 2nd report the custom header does not change it's
value, it still shows the 1st report header.

Any thoughts to why this is happening please....

TIA
Mick

After serious thinking Vacuum Sealed wrote :
Whilst I have your attention Garry

You may be able to shed some light into why it is that the

With ActiveSheet.PageSetup does not work properly.

I have 4 report and each of them is almost identical say for a couple of
minor differences.

This is the 1st:

With ActiveSheet.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = "HDC"
.RightHeader = myDate
.FitToPagesWide = 1
End With

Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
ActiveWindow.SelectedSheets.PrintPreview

With ActiveSheet.PageSetup
.CenterHeader = ""
.PrintArea = ""
End With

This is the second:

With ActiveSheet.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = "NDC"
.RightHeader = myDate
.FitToPagesWide = 1
End With

Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
ActiveWindow.SelectedSheets.PrintPreview

With ActiveSheet.PageSetup
.CenterHeader = ""
.PrintArea = ""
End With

Problem is, when I run the 2nd report the custom header does not change it's
value, it still shows the 1st report header.

Any thoughts to why this is happening please....

TIA
Mick

Since the code refers to 'ActiveSheet' then it's being applied to that
sheet only. You might be better off making the code generic so you can
pass a ref to the target sheet, AND a parameter (arg) for LeftHeader.

If this code you posted is in a single procedure then you'd have to be
activating ach sheet in turn before each part of the code executes.

Not sure why you force PrintPreview before all sheets are setup. I
suspect it's so you can do one report at a time and preview it before
committing it to print. This can be handled slightly differently, as in
my example.

<aircode
Function Set_PageSetup(Target As Worksheet, _
LHdrText As String) As Boolean
On Error GoTo ErrExit
With Target.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = LHdrText
.RightHeader = myDate
.FitToPagesWide = 1
End With

ErrExit:
Set_PageSetup = (Err = 0)
If Not Set_PageSetup Then wks.CenterHeader = "": wks.PrintArea = ""
End Function

Example usage:

Sub PrintReports()
Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
For Each wks In ActiveWindow.SelectedSheets
If Set_PageSetup(wks, wks.Range("LHdrText")) Then
.PrintOut Preview:=True
wks.CenterHeader = "": wks.PrintArea = ""
Else
MsgBox "An error occured doing PageSetup for sheet '" _
& wks.Name & "'!"
End If
End Sub

The code above will only execute if Set_PageSetup was successful (ergo
no errors), and notify if any failures. It also implies that the text
for LeftHeader is stored in the same location on each sheet, AND that
cell is named "LHdrText" with local scope. (In the namebox to the left
of the FormulaBar, type an apostrophe, the sheetname, another
apostrophe, the exclamation character, then "LHdrText". Do this for
each sheet.)

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

GS brought next idea :
Sub PrintReports()
Dim wks As Worksheet
Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
For Each wks In ActiveWindow.SelectedSheets
If Set_PageSetup(wks, wks.Range("LHdrText")) Then
.PrintOut Preview:=True
wks.CenterHeader = "": wks.PrintArea = ""
Else
MsgBox "An error occured doing PageSetup for sheet '" _
& wks.Name & "'!"
End If
End Sub

In the above sub I forgot to Dim the var 'wks', so revise it as per the
line I inserted above. -Sorry about that...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Garry

That is awesome, thank you.

One last question relating to the ActivePrinter=....

Will your code stop the macro halting on the:

Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"

Is there an alternative command line I can use for section, say something
like:

Application.ActivePrinter = "\\SPRN01\WoW \DefaultPrinter"

As you have commented previously, I should be able to direct the code to the
Default Printer that is setup via MSO..

Thx heaps again

Mick

Vacuum Sealed formulated the question :
Garry

That is awesome, thank you.

One last question relating to the ActivePrinter=....

Will your code stop the macro halting on the:

Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"

Is there an alternative command line I can use for section, say something
like:

Application.ActivePrinter = "\\SPRN01\WoW \DefaultPrinter"

As you have commented previously, I should be able to direct the code to the
Default Printer that is setup via MSO..

Thx heaps again

Mick

If THAT printer IS the default OS printer then you shouldn't have to
set ActivePrinter. It's likely that the code is not halting but rather
taking more time to connect to the remote printer. That happens to me
whenever I change to a local PDF printer and then return to my default
printer on the print server, via the Print dialog. VBA takes a bit
longer because it's less efficient than OS code (C++).

So.., if THAT printer IS the default printer when Excel starts up then
using the PrintOut method of the worksheet object will work. (Excel and
all the other MSO apps always establish connection to the OS default
printer at startup) Hovering your moue over the printer icon on the
Standard toolbar should display a tooltip as to what printer Excel is
currently using. If things are what I suspect then the line setting
ActivePrinter isn't necessary and so could be deleted but I suggest
just comment it out so it's there if you need it later on.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Sweet

Thx you so much for all your help

Mick

Hello again Garry

Sorry to bake your noodle, had some issues with what you gave me.

I tried entering the NameRange for each sheet the way you explained it...

'HDC'!"LHdrText" but it rejected it stating it was an invalid name.

Instead I just entered HDC, LDC, and it seems to work, for the moment......


One other thing, although the Print_Report routine works to a point, there
are 2 problems:

1. If have to manually select the Print_Range I want.

2. It does not allow for, nor does it display the RHdr = myDate string
on the report.

Thx again
Mick

Vacuum Sealed submitted this idea :
Hello again Garry

Sorry to bake your noodle, had some issues with what you gave me.

I tried entering the NameRange for each sheet the way you explained it...

'HDC'!"LHdrText" but it rejected it stating it was an invalid name.

Instead I just entered HDC, LDC, and it seems to work, for the moment......


One other thing, although the Print_Report routine works to a point, there
are 2 problems:

1. If have to manually select the Print_Range I want.

2. It does not allow for, nor does it display the RHdr = myDate string on
the report.

Thx again
Mick

Don't include the quotes around the name. This works...

'HDC'!LHdrText

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Vacuum Sealed brought next idea :
Hello again Garry

Sorry to bake your noodle, had some issues with what you gave me.

I tried entering the NameRange for each sheet the way you explained it...

'HDC'!"LHdrText" but it rejected it stating it was an invalid name.

Instead I just entered HDC, LDC, and it seems to work, for the moment......


One other thing, although the Print_Report routine works to a point, there
are 2 problems:

1. If have to manually select the Print_Range I want.

2. It does not allow for, nor does it display the RHdr = myDate string on
the report.

Thx again
Mick

Also, the single quotes around the sheetname are only necessary if the
sheetname has spaces. So...

HDC!LHdrText works...

AND

'H D C'!LHdrText also works.

You should also know that Excel has naming rules that we must follow.
As you found out, Excel will notify you whenever you try to create an
invalid name (whether it be for a range, formula, or stored value).

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

BTW, the names you've created have global scope and so can't be reused
on every sheet. You should delete these and rename the cells with local
scope as it's considered 'best practice' to only use global scope when
absolutely necessary.

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Hi Garry

Dunno why, but I was having trouble with the:

..PrintOut Preview:=True

So I ended up finding a work-around, which is probably not as short or as
pretty as your code, but it works very well, plus it executes alot
faster....

With the exception of the Function, I isolated each sheet with it's own sub
which look like this:

With this line of code introduced here **, it now displays myDate on the
Report

Function Set_PageSetup(Target As Worksheet, LHdrText As String) As Boolean

**myDate = Format(Date, "Ddd, dd-Mmm-yy")

On Error GoTo ErrExit

With Target.PageSetup
.PrintArea = Selection.Address
.PrintTitleRows = "$1:$1"
.LeftHeader = LHdrText
.RightHeader = myDate
.FitToPagesWide = 1
End With

ErrExit:
Set_PageSetup = (Err = 0)
If Not Set_PageSetup Then wks.CenterHeader = "": wks.PrintArea = ""

End Function

** I included the myWkS to the front of the .Printout
^^ Plus I included the range Across & Down to auto select the range
## As I have some format tweaking, I included a call command so it did it
just prior to the PrintPreview.

Sub PrintHDCReports()

Dim myWkS As Worksheet


Set myWkS = ActiveSheet

^^ Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select


If Set_PageSetup(myWkS, myWkS.Range("LHdrText")) Then

##Call HDC_Report_Setup
**myWkS.PrintOut Preview:=True

Else
MsgBox "An error occured doing PageSetup for sheet '" _
& wks.Name & "'!"

End If


End Sub

Sub HDC_Report_Setup()

Columns("L:L").Select
Selection.ColumnWidth = 30

Cells.Select

With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

Rows("2:200").Select
Selection.Rows.AutoFit

Selection.Sort Key1:=Range("D2"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

I really appreciate your help in steering me in the right direction.

Cheers
Mick.

Sorry Mick, my bad! I left out the ref to wks on that line. Here's the
revised sub:

Sub PrintReports()
Application.ActivePrinter = "\\SPRN01\WoW HDC on Ne02:"
For Each wks In ActiveWindow.SelectedSheets
If Set_PageSetup(wks, wks.Range("LHdrText")) Then
With wks
.PrintOut Preview:=True: .CenterHeader = "": .PrintArea = ""
End With
Else
MsgBox "An error occured doing PageSetup for sheet '" _
& wks.Name & "'!"
End If
End Sub

It should work as expected now...

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc

Some of the properties in PageSetup can take a while. It's just their
nature! Setting header text is high on the list and so some folks use
XLM4 macros to do things because it's much faster being that it's not
VBA. You can get code sample for using this alternate approach from
John McGimpsey's website. The original code was by John Green but JM
has reworked it to be quite a bit more friendly. I highly recommend you
use this for all pagesetup needs as it can set all possible properties
in a few seconds. Since it's XLM4 it's fairly old so any missing
properties we have today will have to be done in the usual way.

Here's a link...

http://www.mcgimpsey.com/excel/udfs/pagesetup.html

--
Garry

Free usenet access at http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc