Welcome To The Home Of The Visual FoxPro Experts  
home. signup. forum. archives. search. google. articles. downloads. faq. members. weblogs. file info. rss.
 From: Derrick Bruce
  Where is Derrick Bruce?
 Charlestown
 Indiana - United States
 Derrick Bruce
 To: Khurram Tahir
  Where is Khurram Tahir?
 Karachi
 Pakistan
 Khurram Tahir
 Tags
Subject: RE: Fit to (1) pages wide by 1 tall
Thread ID: 228478 Message ID: 228593 # Views: 1 # Ratings: 0
Version: Visual FoxPro 9 Category: Microsoft Office Automation
Date: Wednesday, May 20, 2009 6:15:26 AM         
   


> Q. I want to set things for printing by automation
>
> 1)--Adjust to any size Automatically if Fit to page selected
>
> 2)--Fit to 1 Page wide by 1 tall (want to Select) how can i do this ?

Here is one that will do most of the stuff you'll run into with automation in Excel - the page setup stuff is the last big group near the end.

Just record the macro as already suggested and then figure the constants out, the code here would be equal to 1 page wide and empty in the page tall.

PARAMETERS cpath1,cpage,csheet,cpath2


&&& automate Excel here - figure out Macro
OleApp=CreateObject([Excel.Application])
OleApp.Visible = .T.
oleapp.DisplayAlerts = .F.
OleApp.WorkBooks.open(cpath1)

oleapp.sheets.add()
olepivot = oleapp.activeworkbook.pivotcaches.create(1,cpage,3)
olepivot.createpivottable('Sheet1!R3C1','PivotTable1',3)

WITH oleapp.activesheet.pivottables('PivotTable1').PivotFields('branch')
.orientation = 1
.position = 1
.layoutpagebreak = .t.
ENDWITH

WITH oleapp.activesheet.pivottables('PivotTable1').PivotFields('corporate')
.orientation = 1
.position = 2
ENDWITH

oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('mtd09_sls'),'Sum of mtd09_sls',-4157)
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('mtd09_gmd'),'Sum of mtd09_gmd',-4157)
oleapp.activesheet.pivottables('PivotTable1').calculatedfields.add('mtd09_gpp','=if(iserror(SUM(mtd09_gmd/mtd09_sls)),0,SUM(mtd09_gmd/mtd09_sls))',.t.)
oleapp.activesheet.pivottables('PivotTable1').pivotfields('mtd09_gpp').orientation = 4
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('no_inv'),'Sum of no_inv',-4157)
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd09_sls'),'Sum of ytd09_sls',-4157)
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd09_gmd'),'Sum of ytd09_gmd',-4157)
oleapp.activesheet.pivottables('PivotTable1').calculatedfields.add('ytd09_gpp','=if(iserror(SUM(ytd09_gmd/ytd09_sls)),0,SUM(ytd09_gmd/ytd09_sls))',.t.)
oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd09_gpp').orientation = 4

oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd_sls_var'),'Sum of ytd_sls_var',-4157)
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd_gmd_var'),'Sum of ytd_gmd_var',-4157)

oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('mtd08_sls'),'Sum of mtd08_sls',-4157)
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('mtd08_gmd'),'Sum of mtd08_gmd',-4157)
oleapp.activesheet.pivottables('PivotTable1').calculatedfields.add('mtd08_gpp','=if(iserror(SUM(mtd08_gmd/mtd08_sls)),0,SUM(mtd08_gmd/mtd08_sls))',.t.)
oleapp.activesheet.pivottables('PivotTable1').pivotfields('mtd08_gpp').orientation = 4
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd08_sls'),'Sum of ytd08_sls',-4157)
oleapp.activesheet.pivottables('PivotTable1').adddatafield(oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd08_gmd'),'Sum of ytd08_gmd',-4157)
oleapp.activesheet.pivottables('PivotTable1').calculatedfields.add('ytd08_gpp','=if(iserror(SUM(ytd08_gmd/ytd08_sls)),0,SUM(ytd08_gmd/ytd08_sls))',.t.)
oleapp.activesheet.pivottables('PivotTable1').pivotfields('ytd08_gpp').orientation = 4


oleapp.activeworkbook.showpivottablefieldlist = .f.

oleapp.range('A4').select()
oleapp.activesheet.pivottables('PivotTable1').compactlayoutrowheader = 'Branch/Corporate Name/Salesrep'

oleapp.range('B4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of mtd09_sls').caption = 'MTD 09 SLS'

oleapp.range('C4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of mtd09_gmd').caption = 'MTD 09 GP$'

oleapp.range('D4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of mtd09_gpp').caption = 'MTD 09 GP%'

oleapp.range('E4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of no_inv').caption = 'MTD Lines'

oleapp.range('F4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd09_sls').caption = 'YTD 09 SLS'

oleapp.range('G4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd09_gmd').caption = 'YTD 09 GP$'

oleapp.range('H4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd09_gpp').caption = 'YTD 09 GP%'

oleapp.range('I4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd_sls_var').caption = 'SLS$'

oleapp.range('J4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd_gmd_var').caption = 'GP$'

oleapp.range('K4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of mtd08_sls').caption = 'MTD 08 SLS'

oleapp.range('L4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of mtd08_gmd').caption = 'MTD 08 GP$'

oleapp.range('M4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of mtd08_gpp').caption = 'MTD 08 GP%'

oleapp.range('N4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd08_sls').caption = 'YTD 08 SLS'

oleapp.range('O4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd08_gmd').caption = 'YTD 08 GP$'

oleapp.range('P4').select()
oleapp.activesheet.pivottables('PivotTable1').datapivotfield.pivotitems('Sum of ytd08_gpp').caption = 'YTD 08 GP%'



oleapp.columns('B:B').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('C:C').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('D:D').select()
oleapp.selection.numberformat = '0.0%'
oleapp.columns('F:F').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('G:G').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('H:H').select()
oleapp.selection.numberformat = '0.0%'
oleapp.columns('I:I').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('J:J').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('K:K').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('L:L').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('M:M').select()
oleapp.selection.numberformat = '0.0%'
oleapp.columns('N:N').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('O:O').select()
oleapp.selection.numberformat = '_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)'
oleapp.columns('P:P').select()
oleapp.selection.numberformat = '0.0%'


&&& format headers
oleapp.range('I2:J2').select
WITH oleapp.selection
.horizontalalignment = -4108
.verticalalignment = -4107
.wraptext = .f.
.orientation = 0
.addindent = .f.
.indentlevel = 0
.shrinktofit = .f.
.readingorder = -5002
.mergecells = .f.
ENDWITH

oleapp.selection.merge
oleapp.selection.font.bold = .t.
oleapp.activecell.formular1c1 = 'YTD Variances'

oleapp.range('B2:H2').select
WITH oleapp.selection
.horizontalalignment = -4108
.verticalalignment = -4107
.wraptext = .f.
.orientation = 0
.addindent = .f.
.indentlevel = 0
.shrinktofit = .f.
.readingorder = -5002
.mergecells = .f.
ENDWITH

oleapp.selection.merge
oleapp.selection.font.bold = .t.
oleapp.selection.numberformat = 'General'
oleapp.activecell.formular1c1 = '2009'

oleapp.range('K2:P2').select
WITH oleapp.selection
.horizontalalignment = -4108
.verticalalignment = -4107
.wraptext = .f.
.orientation = 0
.addindent = .f.
.indentlevel = 0
.shrinktofit = .f.
.readingorder = -5002
.mergecells = .f.
ENDWITH

oleapp.selection.merge
oleapp.selection.font.bold = .t.
oleapp.selection.numberformat = 'General'
oleapp.activecell.formular1c1 = '2008'

&&& color top row to match
oleapp.range('A2:P2').select
WITH oleapp.selection.interior
.pattern = 1
.patterncolorindex = -4105
.themecolor = 5
.tintandshade = 0.799981688894314
.patterntintandshade = 0
ENDWITH

&&&& sort data
oleapp.range('N6').select
oleapp.activesheet.pivottables('PivotTable1').pivotfields('corporate').autosort(2,'YTD 08 SLS', oleapp.activesheet.pivottables('PivotTable1').pivotcolumnaxis.pivotlines(13),1)
&&

&&& freeze
oleapp.range('A5').select
oleapp.activewindow.freezepanes = .t.
&&

&&& add divider lines
oleapp.columns('I:J').select
WITH oleapp.selection.borders(7)
.linestyle = 1
.colorindex = 0
.tintandshade = 0
.weight = 2
ENDWITH
WITH oleapp.selection.borders(10)
.linestyle = 1
.colorindex = 0
.tintandshade = 0
.weight = 2
ENDWITH


oleapp.sheets(csheet).select()
oleapp.activewindow.selectedsheets.delete()

oleapp.activeworkbook.saveas(cpath2,51)

WITH oleapp.activesheet.pagesetup
.printtitlerows = '$2:$4'
.printtitlecolumns = ""
ENDWITH

oleapp.activesheet.pagesetup.printarea = ""

WITH oleapp.activesheet.pagesetup
.leftheader = ""
.centerheader = '&F'
.rightheader = ""
.leftfooter = ""
.centerfooter = ""
.rightfooter = ""
.leftmargin = 0
.rightmargin = 0
.topmargin = 0
.bottommargin = 0
.headermargin = 0
.footermargin = 0
.printheadings = .f.
.printgridlines = .f.
.printcomments = -4142
.printquality = 600
.centerhorizontally = .f.
.centervertically = .f.
.orientation = 2
.draft = .f.
.papersize = 1
.firstpagenumber = -4105
.order = 1
.blackandwhite = .f.
.zoom = .f.
.fittopageswide = 1
.fittopagestall = .f.
.printerrors = 0
.oddandevenpagesheaderfooter = .f.
.differentfirstpageheaderfooter = .f.
.scalewithdocheaderfooter = .t.
.alignmarginsheaderfooter = .t.
ENDWITH

oleapp.range('A1').select()

oleapp.activeworkbook.saveas(cpath2,51)

oleapp.quit


ENTIRE THREAD

Fit to (1) pages wide by 1 tall Posted by Khurram Tahir @ 5/19/2009 9:52:28 AM
RE: Fit to (1) pages wide by 1 tall Posted by Russell Hill @ 5/19/2009 2:11:41 PM
RE: Fit to (1) pages wide by 1 tall Posted by Khurram Tahir @ 5/19/2009 2:50:00 PM
RE: Fit to (1) pages wide by 1 tall Posted by tushar @ 5/19/2009 7:25:01 PM
RE: Fit to (1) pages wide by 1 tall Posted by Russell Hill @ 5/20/2009 6:53:32 AM
RE: Fit to (1) pages wide by 1 tall Posted by Khurram Tahir @ 5/22/2009 12:30:05 PM
RE: Fit to (1) pages wide by 1 tall Posted by Russell Hill @ 5/22/2009 1:45:33 PM
RE: Fit to (1) pages wide by 1 tall Posted by Derrick Bruce @ 5/20/2009 6:15:26 AM