Jump to content
Sign in to follow this  
UEZ

How to adapt VPageBreak.DragOff Method (Excel)

Recommended Posts

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 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
PACaleala

What do you get in VPageBreaks.Count ?

Share this post


Link to post
Share on other sites
UEZ

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
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

Share this post


Link to post
Share on other sites
UEZ

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
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

 

Share this post


Link to post
Share on other sites
UEZ

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
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 .

Share this post


Link to post
Share on other sites
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

 


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
PACaleala

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
UEZ

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
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.

 

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
UEZ

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  

  • Similar Content

    • Ahmed101
      By Ahmed101
      I have more than 12 workbooks opened together, if i wanted to attach to the last workbook opened it will take more than 1 minute !
      Is there any solution for that ?
    • Daniza
      By Daniza
      Hello! where should I start, if I want to have a Progress Bar while waiting for my File to be open, can I use WinWaitActive? Thanks,
    • Evolutionnext
      By Evolutionnext
      I am still a noob and not a programmer, would greatly appreciate your help.
       
      Task:
      Open Excel file with file path and name: C:\Users\GENOBEAUTYPC1\Desktop\ACTIVE BEAUTY LABELS\BeautyMe Label 200ml ACTIVE VERSION.xlsx
      This file path and name is saved in the variable: $sAnswer
      Go to Excel Tab called "formular"
      Go to Cell A1
      Insert the text saved in the variable: $sAnswer2
      ATTENTION!!! This has 2 problems.
      Problem number 1: This text contains special characters that need to be interpreted as raw text. (content is: Gemischt für#30 ml#Mindestens haltbar bis#Maria Wallerstorfer#Anwendung: Täglich 1x morgens auf das gereinigte Gesicht auftragen. Augenkontakt vermeiden.#Über 0 C° und unter 25 C° lagern.#Lot:N8A1028/D30/V2.1#Genome Plus GmbH#Georg-Wrede-St. 13, D-83395 Freilassing#GEN SERUM#DAY)
      Problem number 2: This textis longer than 255 characters.
       
      Can anyone help me?
       
      I try to do it really primitively by opening the excel, waiting until it is open, clicking where the tab is, clicking where the cell is and inserting the content of the variable, but I am stuck at the point where I am limited by 255 characters.
       

      ; Opening the right excel FileChangeDir
                  tooltip("File exists and is called:"&$sAnswer ,300,300)
                  ShellExecute($sAnswer ,"" ,"" ,"" , @SW_MAXIMIZE)
                  sleep(7000)
                  
                  tooltip("Now lets insert the right content into the excel",300,300)
                  MouseClick("left",226,1004)
                  MouseClick("left",52,179)
                  sleep(500)
                  Send("A1")
                  sleep(500)
                  send("{enter}")
                              tooltip("inserting label content",300,300)
                  sleep(500)
                  Send($sAnswer2,  1)
                                          tooltip("inserting INCIS",300,300)
                  sleep(5000)
                  Send($sAnswer3, 1)
                  sleep(5000)
       
       
       
    • AzgarD
      By AzgarD
      Hi guys. I know this is a newbie topic, very newbie, but i've read a lot of stuff and still don't get it. I just need to copy something from Excel cell, paste this in other program, copy something in this program and paste in other Excel cell. Something like...
      Copy A2 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C2 Then Copy A3 Use some WindowActivate and MouseMove stuff and CTRL+C (not a problem) Go back to the Excel sheet Paste that content in C3 ... And it goes on The problem is, how can i "communicate" with Excel and do this row change? Like A2 to C2 and A3 to C3 ... In a efficient way that can be done like hundreds of times.
      Very newbie question but still not understanding this.
       
      Ty guys.
    • Gowrisankar
      By Gowrisankar
      Dear members of the forum,
      I need to open excel files that may or may not need a password and finally move the files that needs password to manual queue.
      Is there a fastest way to do this?
       
      PS: I have a huge respect for the rules of this forum. I am not asking assistance to override any security measure. I just need to segregate the files that needs passwords.
×