excel
in [VbScript]
|
From: SammyL on 6 May 2010 15:54 I've tried many things here- one person insisted that window should be activewindow but i tried it both as activewindow and activesheet and i cannot set the hpagebreaks. if i just set the first pagebreak it doesn't return an error but does nothing- if i run 2 or more hpagebreaks it errors out with subscript out of range Dim xlapp Dim objWorkBook DIM XLWB Set xlapp = CreateObject("EXCEL.APPLICATION") Set objWorkBook = xlapp.Workbooks.Open("C:\test22.xls") Const xlLandScape = 2 xlapp.ActiveSheet.PageSetup.Orientation = xlLandScape xlapp.ActiveSheet.PageSetup.FitToPagesWide = 1 xlapp.ActiveSheet.PageSetup.FitToPagesTall = 99 xlapp.ActiveSheet.ResetAllPageBreaks xlapp.ActiveSheet.Pagesetup.Zoom = False xlapp.ActiveWindow.Zoom = False xlapp.ActiveSheet.HPageBreaks(1).Location = xlapp.ActiveSheet.Range("A82") xlapp.ActiveSheet.HPageBreaks(2).Location = xlapp.ActiveSheet.Range("A183") 'xlapp.ActiveSheet.HPageBreaks(3).Location = xlapp.ActiveSheet.Range("A272") 'xlapp.ActiveSheet.HPageBreaks(4).Location = xlapp.ActiveSheet.Range("A324") objWorkBook.save Set objWorkBook = Nothing xlapp.Quit Set xlaPP= Nothing Tom- Thanks for all of your time and help. I tried replying to your verizon account but it doesnt seem to be reaching you.
From: Tom Lavedas on 6 May 2010 16:40 On May 6, 3:54 pm, SammyL <sam...(a)gmail.com> wrote: > I've tried many things here- one person insisted that window should be > activewindow but i tried it both as activewindow and activesheet and i > cannot set the hpagebreaks. if i just set the first pagebreak it > doesn't return an error but does nothing- if i run 2 or more > hpagebreaks it errors out with subscript out of range > > Dim xlapp > Dim objWorkBook > DIM XLWB > Set xlapp = CreateObject("EXCEL.APPLICATION") > Set objWorkBook = xlapp.Workbooks.Open("C:\test22.xls") > Const xlLandScape = 2 > xlapp.ActiveSheet.PageSetup.Orientation = xlLandScape > xlapp.ActiveSheet.PageSetup.FitToPagesWide = 1 > xlapp.ActiveSheet.PageSetup.FitToPagesTall = 99 > xlapp.ActiveSheet.ResetAllPageBreaks > xlapp.ActiveSheet.Pagesetup.Zoom = False > xlapp.ActiveWindow.Zoom = False > xlapp.ActiveSheet.HPageBreaks(1).Location = > xlapp.ActiveSheet.Range("A82") > xlapp.ActiveSheet.HPageBreaks(2).Location = > xlapp.ActiveSheet.Range("A183") > 'xlapp.ActiveSheet.HPageBreaks(3).Location = > xlapp.ActiveSheet.Range("A272") > 'xlapp.ActiveSheet.HPageBreaks(4).Location = > xlapp.ActiveSheet.Range("A324") > > objWorkBook.save > Set objWorkBook = Nothing > xlapp.Quit > Set xlaPP= Nothing > > Tom- Thanks for all of your time and help. I tried replying to your > verizon account but it doesnt seem to be reaching you. Follow-ups should ALWAYS go to the group. Because of very low volume these days, I almost never check that mailbox anymore. I looked in Excel's VBA (macro) help and found that the HPageBreaks collection requires that they be ADDed, before they can be moved, something like this ... Dim xlapp Dim objWorkBook DIM XLWB Set xlapp = CreateObject("EXCEL.APPLICATION") Set objWorkBook = xlapp.Workbooks.Open("C:\test22.xls") Const xlLandScape = 2 xlapp.visible = true With xlapp.ActiveSheet with .PageSetup .Orientation = xlLandScape .FitToPagesWide = 1 .FitToPagesTall = 99 .Zoom = False end with .ResetAllPageBreaks for i = 83 to 324 step 82 .HPageBreaks.Add .Range("A" & i) next end with wsh.echo "Done" Once they have been created, the Location property can be used to move them. But, before they are added, there is no array - that's why you get the "out of range" error. _____________________ Tom Lavedas
|
Pages: 1 Prev: Repost to avoid confusion Next: Using Multidimensional Array |