C
Claus
Hi
I'm using the ps4 pagesetup with Application.ExecuteExcel4Macro.
I got a problem regarding pscale. Some of my sheets are more than 1 page,
and i would like to set the pscale to "fit to with" and "not fit to height".
The help in Excel4macro says:
Scale is a number representing the percentage to increase or decrease the
size of the sheet. All scaling retains the aspect ratio of the original.
a.. To specify a percentage of reduction or enlargement, set scale to the
percentage.
b.. For worksheets and macros, you can specify the number of pages that
the printout should be scaled to fit. Set scale to a two-item horizontal
array, with the first item equal to the width and the second item equal to
the height. If no constraint is necessary in one direction, you can set the
corresponding value to #N/A.
c.. Scale can also be a logical value. To fit the print area on a single
page, set scale to TRUE.
So i would like to use the second option, with an array with the values 1
and #N/A.
I've created the array, but i can't put in at string, so it can be used with
the ExecuteExcel4Macro.
ANY SUGGESTIONS TO WHAT I SHOULD DO ?
My code (It works with pscale set to True):
Sub PS3(Head As String, Foot As String)
Dim pSetUp As String
Dim pSetUp1 As String
Dim pSetUp2 As String
ReDim ScaleArr(0, 1) As Variant
ScaleArr(0, 0) = 1
ScaleArr(0, 1) = 0
pLeft = "0.8"
pRight = "1.2"
Top = "2"
bot = "1.2"
head_margin = "1.2"
foot_margin = "1.2"
hdng = False
grid = False
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 1
Draft = False
paper_size = 9
pg_num = """Auto"""
pg_order = 1
bw_cells = False
'pscale = ScaleArr
pscale = True
pSetUp1 = "PAGE.SETUP(" & Head & "," & Foot & "," & pLeft & "," & pRight &
","
pSetUp1 = pSetUp1 & Top & "," & bot & "," & hdng & "," & grid & "," &
h_cntr & ","
pSetUp1 = pSetUp1 & v_cntr & "," & orient & "," & paper_size & ","
pSetUp2 = pSetUp2 & "," & pg_num & "," & pg_order & "," & bw_cells & "," &
quality & ","
pSetUp2 = pSetUp2 & head_margin & "," & foot_margin & "," & notes & "," &
Draft & ")"
pSetUp = pSetUp1 & pscale & pSetUp2
Application.ExecuteExcel4Macro pSetUp
End Sub
I'm using the ps4 pagesetup with Application.ExecuteExcel4Macro.
I got a problem regarding pscale. Some of my sheets are more than 1 page,
and i would like to set the pscale to "fit to with" and "not fit to height".
The help in Excel4macro says:
Scale is a number representing the percentage to increase or decrease the
size of the sheet. All scaling retains the aspect ratio of the original.
a.. To specify a percentage of reduction or enlargement, set scale to the
percentage.
b.. For worksheets and macros, you can specify the number of pages that
the printout should be scaled to fit. Set scale to a two-item horizontal
array, with the first item equal to the width and the second item equal to
the height. If no constraint is necessary in one direction, you can set the
corresponding value to #N/A.
c.. Scale can also be a logical value. To fit the print area on a single
page, set scale to TRUE.
So i would like to use the second option, with an array with the values 1
and #N/A.
I've created the array, but i can't put in at string, so it can be used with
the ExecuteExcel4Macro.
ANY SUGGESTIONS TO WHAT I SHOULD DO ?
My code (It works with pscale set to True):
Sub PS3(Head As String, Foot As String)
Dim pSetUp As String
Dim pSetUp1 As String
Dim pSetUp2 As String
ReDim ScaleArr(0, 1) As Variant
ScaleArr(0, 0) = 1
ScaleArr(0, 1) = 0
pLeft = "0.8"
pRight = "1.2"
Top = "2"
bot = "1.2"
head_margin = "1.2"
foot_margin = "1.2"
hdng = False
grid = False
notes = False
quality = ""
h_cntr = False
v_cntr = False
orient = 1
Draft = False
paper_size = 9
pg_num = """Auto"""
pg_order = 1
bw_cells = False
'pscale = ScaleArr
pscale = True
pSetUp1 = "PAGE.SETUP(" & Head & "," & Foot & "," & pLeft & "," & pRight &
","
pSetUp1 = pSetUp1 & Top & "," & bot & "," & hdng & "," & grid & "," &
h_cntr & ","
pSetUp1 = pSetUp1 & v_cntr & "," & orient & "," & paper_size & ","
pSetUp2 = pSetUp2 & "," & pg_num & "," & pg_order & "," & bw_cells & "," &
quality & ","
pSetUp2 = pSetUp2 & head_margin & "," & foot_margin & "," & notes & "," &
Draft & ")"
pSetUp = pSetUp1 & pscale & pSetUp2
Application.ExecuteExcel4Macro pSetUp
End Sub