Sign in to follow this  
Followers 0
UEZ

How to adapt VPageBreak.DragOff Method (Excel)

13 posts in this topic

#1 ·  Posted (edited)

I want to remove the vertical page breaks so that I have only "page 1" available. Here the link to MSDN how to do it in VBA which works properly: https://msdn.microsoft.com/EN-US/library/office/ff836174.aspx

What I did and doesn't work:

With $oExcel_Export2Excel.Worksheets(1)
    .VPageBreaks(1).DragOff.Direction = -4161 ;$xlToRight = -4161
    .VPageBreaks(1).DragOff.RegionIndex = 1
EndWith


Local $aParam[2] = [-4161, 1]
$oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff($aParam)


$oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff(-4161, 1)


$oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff("Direction").Value = -4161
$oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff("RegionIndex").Value = 1


$oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff.Direction = -4161
$oExcel_Export2Excel.Worksheets(1).VPageBreaks(1).DragOff.RegionIndex = 1

 

Any further idea?

 

I'm using Office 2013 only!

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites



What do you get in VPageBreaks.Count ?

Share this post


Link to post
Share on other sites

How it looks:

2n0q7ma.png

 

How the result should be:

aw9l2.png

 

What do you get in VPageBreaks.Count ?

The result is 1.


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

You could switch to page break preview, run the next vba  for Excel 2007, then switch back to normal view.

If ActiveSheet.VPageBreaks.Count > 0 Then
    ActiveWindow.View = xlPageBreakPreview
    ActiveSheet.VPageBreaks(1).DragOff Direction:=xlToRight, RegionIndex:=1
    ActiveWindow.View = xlNormalView
End If

Share this post


Link to post
Share on other sites

VBA works but my script is in AutoIt. ;)


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

I did not manage to use VPageBreak.DragOff .

This is what worked for me ( W7, Excel 2010 ) : open an excel file in normal view and force the removal of all manually set page breaks

Local $xlPageBreakNone = -4142
Local $xlNormalView = 0x1
Local $XL = ObjCreate("Excel.Application")
$XL.Visible = True
$XL.DisplayAlerts = False
Local $objWorkbook = $XL.Workbooks.Open("L:\Items_2003.xlsx")
Local $objWorksheet = $objWorkbook.Worksheets(1)
$XL.Activewindow.View = $xlNormalView
Sleep(2222)
$objWorksheet.Cells.PageBreak = $xlPageBreakNone

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

Doesn't work with Excel 2013. :(

Error: -2147352567  80020009

 

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

Show code to reproduce the problem.

Explain when this error occurs

Because this error is most commonly caused by the use of looping to apply ".format"(s) to multiple cells in a worksheet .

Share this post


Link to post
Share on other sites

I used your code:

Local $xlPageBreakNone = -4142
Local $xlNormalView = 0x1
Local $xlPageBreakPreview = 0x2
Local $XL = ObjCreate("Excel.Application")
$XL.Visible = True
$XL.DisplayAlerts = False
Local $objWorkbook = $XL.Workbooks.Open("Output.xlsx")
Local $objWorksheet = $objWorkbook.Worksheets(1)
$XL.Activewindow.View = $xlPageBreakPreview
Sleep(1000)
$objWorksheet.Cells.PageBreak = $xlPageBreakNone
ConsoleWrite("Error: " & @error & @TAB & Hex(@error, 8) & @CRLF)
Sleep(2222)
$objWorkbook.Close()
$objWorkbook = 0
$XL.Quit()
$XL = 0

 


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

I see. Your requirements are such that I can not help you anymore. I am out.

Share this post


Link to post
Share on other sites

I see. Your requirements are such that I can not help you anymore. I am out.

Thanks for your help!


Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

#12 ·  Posted

Okay, it is a very old topic, but I did't find a solution elsewhere.

This is what I do and that works for me.

 

With $oExcel.Activesheet.PageSetup
    ; Kopfzeile definiern
    .LeftHeader = 'Stand ' & _NowDate()
    .CenterHeader = "Minutenfahrplan"
    .RightHeader = $aResult[2][1]
    ; Fußzeile definiern
    .LeftFooter = 'Stand ' & _NowDate()
    .CenterFooter = ''
    .RightFooter = "Seite &S von &A"
    .PrintPreview
    ; Seitenränder definieren ink. Kopf- & Fußzeile
    .LeftMargin = $oExcel.InchesToPoints(0.15748031496063)
    .RightMargin = $oExcel.InchesToPoints(0.15748031496063)
    .TopMargin = $oExcel.InchesToPoints(0.78740157480315)
    .BottomMargin = $oExcel.InchesToPoints(0.78740157480315)
    .HeaderMargin = $oExcel.InchesToPoints(0.511811023622047)
    .FooterMargin = $oExcel.InchesToPoints(0.511811023622047)
    ; Querformat
    .Orientation = 2
    ; Seiteneinstellungen 1 Seite breit
    .FitToPagesWide = 1
    ; Seiteneinstellungen 3 Seiten hoch
    .FitToPagesTall = 3
    .Zoom = False ;~    .Zoom = 30
    ; DIN A3
    .PaperSize = 8 ; A3
EndWith

Global $width = 110

;Druckbereich festlegen
With $oExcel.Activesheet
;~  .PageSetup.PrintArea = .UsedRange.Address
;~  .PageSetup.PrintTitleRows = "$1:$1"
    .PageSetup.PrintTitleRows = "A1:J3"
    .ResetAllPageBreaks
    .UsedRange.Columns.AutoFit
    .UsedRange.Rows.AutoFit
    .Columns('F').ColumnWidth = $width
    .Columns('G').ColumnWidth = $width
EndWith

;~ $oExcel.Activesheet.Columns('F').ColumnWidth = $width
;~ $oExcel.Activesheet.Columns('G').ColumnWidth = $width

$oExcel.ActiveSheet.Cells(4, 1).Select ; Zeile 4 Spalte 1 selektieren
$oExcel.ActiveWindow.FreezePanes = True ; Zeile fixieren

$oWorkbook.ActiveSheet.Range("A1:J1").Autofilter
;~ $oExcel.Activesheet.UsedRange.Columns.AutoFit
;~ $oExcel.Activesheet.UsedRange.Rows.AutoFit

$oExcel.Activesheet.ResetAllPageBreaks

 


Scripts & functions Organize Includes Let Scite organize the include files

Yahtzee The game "Yahtzee" (Kniffel, DiceLion)

LoginWrapper Secure scripts by adding a query (authentication)

_RunOnlyOnThis UDF Make sure that a script can only be executed on ... (Windows / HD / ...)

Internet-Café Server/Client Application Open CD, Start Browser, Lock remote client, etc.

MultipleFuncsWithOneHotkey Start different funcs by hitting one hotkey different times

Share this post


Link to post
Share on other sites

#13 ·  Posted (edited)

Thanks @Xenobiologist, seems to work. :thumbsup:

Edited by UEZ

Please don't send me any personal message and ask for support! I will not reply!

Selection of finest graphical examples at Codepen.io

The own fart smells best!
Her 'sikim hıyar' diyene bir avuç tuz alıp koşma!
¯\_(ツ)_/¯

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0

  • Similar Content

    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to store an entire row of a Excel workbook into an array.  The row index is stored in a variable.
      How can i do it?
      Thanks in advance for your support.
    • LoneWolf_2106
      By LoneWolf_2106
      Hi everybody,
      i have to write a value into an excel column.
      I know where it starts from, but i don't know what the end is, last non-empty cell.
      How can i get the number of last non-empty cell?
      Thanks in advance.
      Regards 
    • Nareshm
      By Nareshm
      Hi All,
      I have excel file like this
      and i want to cut cell/text from excel to other software.

       
      I have to cut the cell of B column one by one and past into other software
      If Winexists("No Data Found")
      then restore cuted cell and goto next/down side cell
      How to do it ?
    • water
      By water
      Extensive library to control and manipulate Microsoft Excel charts.
      Theads: General Help & Support - Example Scripts
      BTW: If you like this UDF please click the "I like this" button. This tells me where to next put my development effort

      KNOWN BUGS (last changed: 2017-07-21)
      None. The COM error handling related bugs have been fixed.
       
    • Mag91
      By Mag91
      Hey Community,
      cause im too new in the Auto it world i will try it with the your help. hopefully.
      I woud like to know how i can handle my Problem.
      ----
      I have a Excel Data with 362 random numbers.
      For Example:
      1166642335374 1172899897343
      .....
      this numbers are a part of the filepath ...example
      D:\Projekte\1166_64233_5374
      as u can see its the first number of the Excel data. After the first 4 numbers it shoud make a "_" than another 5 "_"
      This is my first question. How can i handle this to make it Shell execute.
       
      --------
      Second question:
      If i am in the path.
      For Example:
      D:\Projekte\1166_64233_5374
      the code shoud search for specific PDF Files.
      They are named like: 0050569E364B1ED79B900F73E62660EC.pdf
      the first 15 letters are always the same
      0050569E364B1ED
      when he found this data he has to copy it on a Folder on the Desktop.
      (There can also be 2 or 3 pdfs in one Folder with this letters)
      ----
      Please give me some help :-)