Jump to content

How to adapt VPageBreak.DragOff Method (Excel)


UEZ
 Share

Recommended Posts

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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
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

Link to comment
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

 

Link to comment
Share on other sites

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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
Share on other sites

  • 1 year later...

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

Link to comment
Share on other sites

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!
¯\_(ツ)_/¯  ٩(●̮̮̃•̃)۶ ٩(-̮̮̃-̃)۶ૐ

Link to comment
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
 Share

×
×
  • Create New...