How to adapt VPageBreak.DragOff Method (Excel)

[UEZ]

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 December 18, 2015 by UEZ

[PACaleala]

What do you get in VPageBreaks.Count ?

[UEZ]

How it looks:

 

How the result should be:

 

What do you get in VPageBreaks.Count ?

The result is 1.

[PACaleala]

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

[UEZ]

VBA works but my script is in AutoIt.

[PACaleala]

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

 

[UEZ]

Doesn't work with Excel 2013.

Error: -2147352567  80020009

 

Edited December 18, 2015 by UEZ

[PACaleala]

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 .

[UEZ]

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

 

[PACaleala]

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

[UEZ]

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

Thanks for your help!

[Xenobiologist]

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.

 

expandcollapse popup

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

 

[UEZ]

Thanks @Xenobiologist, seems to work. 

Edited March 9, 2017 by UEZ