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

    • anusha
      By anusha
      Hi I have jus started using auto-it . Please correct me if I'm wrong.
      I need to read data from an input in text box and search in excel file and return value in next column of matched cell on GUI.
      I have written below code but i cannot use variable which has data stored. it works only when search string is hard coded.
      Please help out.
       
      Example()
      Func Example()
      Local $GuiMain = GUICreate("EXCEL TEST", 399, 180) ;creates main GUI
      ;~ Local $idOK = GUISetOnEvent($GUI_EVENT_CLOSE, "Close")
      Local $iWidthCell = 70
      Local $idLabel = GUICtrlCreateLabel("PART NUMBER", 10, 30, $iWidthCell,50)
      Local $RUN_1 = GUICtrlCreateButton("OK", 70, 70, 85, 25)
      Local $Input_1 = GUICtrlCreateInput("PART NUMBER", 100, 20, 120, 20)
      Local $sMenutext = GUICtrlRead($Input_1, 1)
      GUISetState(@SW_SHOW, $GuiMain)

          While 1
          $MSG = GUIGetMsg()
          Select
              Case $MSG = $GUI_EVENT_CLOSE
                  Exit
              Case $MSG = $RUN_1
                  Local $oAppl = _Excel_Open()

      Local $sFilePath1 = "D:\Anu_WorkFolder\Components.xlsx"
      Local $oWorkbook = _Excel_BookOpen($oAppl, $sFilePath1, Default, Default, True)
      Local $aResult = _Excel_RangeFind($oWorkbook, $sMenutext , Default, Default, $xlWhole)
    • Nareshm
      By Nareshm
      How to Activate Opened Excel Windows Using Class not Tittle, Because Some time opened defferent excel that have different name.
      I Tried with
      Winactivate ("[CLASS:XLMAIN]") but not working
    • willichan
      By willichan
      Here is another one from my archives that filled a specific need.
       
      Here is the back story if you are interested.
       
      Keep in mind that I wrote this script over 3 years ago, so it may not compile or run directly without some minor tweaks.  It also requires the use of GraphViz to build the graph.
      #cs ---------------------------------------------------------------------------- Project Name: ExcelLinksMapper Description: Analyse an Excel file's links and map them out. Creation Date: 9/26/2014 AutoIt Version: Author: willichan Requires: Graphviz (http://graphviz.org/) #ce ---------------------------------------------------------------------------- Opt("MustDeclareVars", 1) ;0=no, 1=require pre-declare Opt("TrayAutoPause", 0) ;0=no pause, 1=Pause Opt("TrayMenuMode", 0) ;0=append, 1=no default menu, 2=no automatic check, 4=menuitemID not return Opt("TrayIconHide", 0) ;0=show, 1=hide tray icon Global Const $MyName=StringLeft(@ScriptName, StringInStr(@ScriptName,".", 0, -1)-1) ;get just the name portion of the script/exe name Global Const $MyMutex=$MyName & "-82243BEBC30533A3" ;name the mutex for this app Global $SQLloaded = False Global $sDbName = @ScriptDir & "\db2gv.db" ConsoleWrite($sDbName & @crlf) If _MutexExists($MyMutex) Then Exit #include <SQLite.au3> #include <SQLite.dll.au3> #include <file.au3> #include <array.au3> #include <excel.au3> _ConfigInitialize() _Main() Func _ConfigInitialize() OnAutoItExitRegister("_ConfigDestroy") ;initializers here Global $sSQliteDll = _SQLite_Startup() If @error Then MsgBox(0, "SQLite Error", "could not load the DLL") Global $sSQLiteDB = _SQLite_Open($sDbName) If $sSQLiteDB = 0 Then MsgBox(0, "SQLite Error", "could not open the database") $SQLloaded =True __CreateTables() EndFunc ;==>_ConfigInitialize Func _ConfigDestroy() ;destructors here If $SQLloaded Then _SQLite_Close() _SQLite_Shutdown() EndIf EndFunc ;==>_ConfigDestroy Func _MutexExists($sOccurenceName) Local $ERROR_ALREADY_EXISTS = 183, $handle, $lastError $sOccurenceName = StringReplace($sOccurenceName, "\", "") $handle = DllCall("kernel32.dll", "int", "CreateMutex", "int", 0, "long", 1, "str", $sOccurenceName) $lastError = DllCall("kernel32.dll", "int", "GetLastError") Return $lastError[0] = $ERROR_ALREADY_EXISTS EndFunc ;==>_MutexExists Func __CreateTables() _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS nodes;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS nodes( name TEXT PRIMARY KEY, fileexists INTEGER);") _SQLite_Exec($sSQLiteDB, "DROP TABLE IF EXISTS links;") _SQLite_Exec($sSQLiteDB, "CREATE TABLE IF NOT EXISTS links( id INTEGER PRIMARY KEY, name1 TEXT, name2 TEXT, weight INTEGER);") EndFunc Func _Main() Local $sInfile, $vResult, $iErrLoop $sInfile = FileOpenDialog("Source File", @WorkingDir, "Excel files (*.xl*)", 1 + 2) If Not FileExists($sInfile) Then MsgBox(0, "Excel Links Mapper Error", "Unable to locate source file") Exit EndIf $vResult = $SQLITE_IOERR $iErrLoop = 5 While $vResult = $SQLITE_IOERR $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sInfile) & ", 1);") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then ConsoleWrite($iErrLoop & " tries" & @CRLF & $sInfile & @CRLF) $vResult = $SQLITE_OK EndIf WEnd _GetExcelLinks($sInfile) Global $hOutfile = FileOpen(@ScriptDir & "\" & $MyName & ".gv", 2) If $hOutfile = -1 Then MsgBox(0, $MyName & " ERROR", "Unable to upen file for output") Exit EndIf _WriteHeader() _WriteNodes() _WriteLinks() _WriteFooter() FileClose($hOutfile) _GenerateGraph() ShellExecute(@ScriptDir & '\ExcelLinksMapper.png') EndFunc ;==>_Main Func _GetExcelLinks($strFileName) Local $hQuery, $aCount, $iErrLoop, $vResult ConsoleWrite($strFileName & @CRLF) Local $iLoop, $iExists Local $aLinks Local Const $xlExcelLinks = 1 Local $oExcel = _Excel_Open() Local $ret = _Excel_BookOpen_NoUpdate($oExcel, $strFileName, True, True) Local $err = @error If $err Then If Not IsObj($oExcel) Then ConsoleWrite($ret & " - " & $err & @CRLF) Exit EndIf EndIf $aLinks = $oExcel.ActiveWorkbook.LinkSources($xlExcelLinks) _Excel_BookClose($oExcel, False) _Excel_Close($oExcel, False, True) If IsArray($aLinks) Then If UBound($aLinks) > 0 Then For $iLoop = 0 To UBound($aLinks) - 1 If $aLinks[$iLoop] <> $strFileName Then $iExists = FileExists($aLinks[$iLoop]) ConsoleWrite("DEBUG - Calling WriteNode()") __WriteNode($aLinks[$iLoop], $iExists) ConsoleWrite("DEBUG - Calling WriteLink()") __WriteLink($strFileName, $aLinks[$iLoop]) If $iExists And ($aLinks[$iLoop] <> $strFileName) Then _GetExcelLinks($aLinks[$iLoop]) EndIf Next EndIf EndIf EndFunc ;==>_GetExcelLinks Func __WriteNode($sName, $iExists) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult Do ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Exec(INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");) - create node entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT OR IGNORE INTO nodes ('name', 'fileexists') VALUES (" & _SQLite_FastEscape($sName) & ", " & $iExists & ");") If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteNode Func __WriteLink($sName1, $sName2) Local $iErrLoop = 5 ;Number of attempts to make Local $vResult, $hQuery, $vCount ConsoleWrite("DEBUG - WriteNode()" & @CRLF & " _SQLite_Query(SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";) - lookup link entry" & @CRLF) _SQLite_Query($sSQLiteDB, "SELECT weight FROM links WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2'=" & _SQLite_FastEscape($sName2) & ";", $hQuery) ConsoleWrite("DEBUG - _SQLite_FetchData()" & @CRLF) _SQLite_FetchData($hQuery, $vCount) If UBound($vCount) > 1 Then _ArrayDisplay($vCount) If $SQLITE_OK And UBound($vCount) > 1 Then $vCount = $vCount[1] + 1 Else $vCount = 1 EndIf Do If $vCount = 1 Then ConsoleWrite("DEBUG - _SQLite_Exec() - create link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "INSERT INTO links ('name1', 'name2', 'weight') VALUES (" & _SQLite_FastEscape($sName1) & ", " & _SQLite_FastEscape($sName2) & ", " & $vCount & ");") Else ConsoleWrite("DEBUG - _SQLite_Exec() - update link entry" & @CRLF) $vResult = _SQLite_Exec($sSQLiteDB, "UPDATE links SET 'weight'=" & $vCount & " WHERE 'name1'=" & _SQLite_FastEscape($sName1) & " AND 'name2=" & _SQLite_FastEscape($sName2) & ";") EndIf If Not $vResult = $SQLITE_OK Then Sleep(100) $iErrLoop -= 1 If $iErrLoop = 0 Then $vResult = $SQLITE_OK ;Used up all our attempts, so simulate a success Until $vResult = $SQLITE_OK EndFunc ;==>__WriteLink ; #FUNCTION# ==================================================================================================================== ; Author ........: SEO <locodarwin at yahoo dot com> ; Modified.......: litlmike, water, GMK, willichan ; =============================================================================================================================== Func _Excel_BookOpen_NoUpdate($oExcel, $sFilePath, $bReadOnly = Default, $bVisible = Default, $sPassword = Default, $sWritePassword = Default) If Not IsObj($oExcel) Or ObjName($oExcel, 1) <> "_Application" Then Return SetError(1, @error, 0) If Not FileExists($sFilePath) Then Return SetError(2, 0, 0) If $bReadOnly = Default Then $bReadOnly = False If $bVisible = Default Then $bVisible = True ;; changing the second parameter on the following line to a 0 tells Excel not to update any links. Local $oWorkbook = $oExcel.Workbooks.Open($sFilePath, 0, $bReadOnly, Default, $sPassword, $sWritePassword) If @error Then Return SetError(3, @error, 0) $oExcel.Windows($oWorkbook.Name).Visible = $bVisible ; If a read-write workbook was opened read-only then return an error If $bReadOnly = False And $oWorkbook.Readonly = True Then Return SetError(4, 0, $oWorkbook) Return $oWorkbook EndFunc ;==>_Excel_BookOpen_NoUpdate Func _GenerateGraph() RunWait(@ScriptDir & '\GraphViz238\bin\dot.exe -Tpng "' & @ScriptDir & '\' & $MyName & '.gv" -o "' & @ScriptDir & '\ExcelLinksMapper.png"') EndFunc ;==>_GenerateGraph Func _WriteHeader() __OutLine(0, "digraph main {") EndFunc ;==>_WriteHeader Func _WriteNodes() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle __OutLine(1, "// Nodes") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name, fileexists FROM nodes;", $aResult, $iRows, $iColumns) If @error Then ConsoleWrite("_WriteNodes()" & @CRLF & "_SQLite_GetTable") If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows If $aResult[($iLoop * 2) + 2] > 0 Then $sStyle = "normal" Else $sStyle = "missing" EndIf __OutNode($aResult[1 + (2 * $iLoop)], $sStyle) ;; Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg() & @CRLF & "in _WriteNotes() calling _SQLite_GetTable()") Exit EndIf EndFunc ;==>_WriteNodes Func __OutNode($sName, $sStyle = Default) If $sStyle = Default Then $sStyle = "Normal" Switch StringLower($sStyle) Case "missing" __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=red,fontcolor=red,shape=octagon];') Case Else ;"normal", Default __OutLine(1, __MakeName($sName) & ' [label="' & StringReplace($sName, "\", "\\") & '",color=black,fontcolor=black,shape=box];') EndSwitch EndFunc ;==>__OutNode Func _WriteLinks() Local $aResult, $iRows, $iColumns, $iRval Local $iLoop, $sStyle, $aCount __OutLine(1, "// Links") $iRval = _SQLite_GetTable($sSQLiteDB, "SELECT name1, name2 FROM links ORDER BY name1 ASC, name2 ASC;", $aResult, $iRows, $iColumns) If $iRval = $SQLITE_OK Then For $iLoop = 1 To $iRows __OutLink($aResult[1 + (2 * $iLoop)], $aResult[2 + (2 * $iLoop)]) Next Else MsgBox($MB_SYSTEMMODAL, "SQLite Error: " & $iRval, _SQLite_ErrMsg()) Exit EndIf EndFunc ;==>_WriteLinks Func __OutLink($sName1, $sName2, $iWeight = 1) __OutLine(1, __MakeName($sName1) & ' -> ' & __MakeName($sName2) & ';') EndFunc ;==>__OutLink Func _WriteFooter() __OutLine(0, "}") EndFunc ;==>_WriteFooter Func __OutLine($iTabs, $sText) Local $iLoop If $iTabs > 0 Then For $iLoop = 1 To $iTabs FileWrite($hOutfile, " ") ;ConsoleWrite(" ") Next EndIf FileWriteLine($hOutfile, $sText) EndFunc ;==>__OutLine Func __MakeName($sText) Local $sNewName = StringReplace($sText, "\", " ") $sNewName = StringReplace($sNewName, "/", " ") $sNewName = StringReplace($sNewName, "'", " ") $sNewName = StringReplace($sNewName, '"', " ") $sNewName = StringReplace($sNewName, ':', " ") $sNewName = StringReplace($sNewName, '.', " ") $sNewName = StringReplace($sNewName, '-', " ") $sNewName = StringReplace($sNewName, '$', " ") $sNewName = StringStripWS($sNewName, 8) Return StringLower($sNewName) EndFunc ;==>__MakeName  
    • Dimmae
      By Dimmae
      Hello,
      at first: i'm new here, so please forgive me my mistakes, and show them to me, just that i can learn to do better in the future.
      Now to my Problem: i have an excel sheet, where i just need some columns for further actions, but i have no idea how to add single columns to a new array.
      I found the following code(the one i just added as a file) from 'water' in this forum, but i wont get how i could add multiple columns into a new array.
      The biggest problem in my situation is that i dont know the count of the rows i need for the array, i just got a fix number of rows, which is 4.
       
      Hope you can help me, and sry again for this 'unlucky illustration'.
       
      btw: how can i add code shown as code here, instead of posting it as a attached file?.
       
       
       
      autoit-select-column.au3
      defects.xlsx
    • LoneWolf_2106
      By LoneWolf_2106
      Hi,
      i have an error:
      ==> The requested action with this object has failed.: $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count $iRowCount = .Range(^ ERROR  
      My code is:
      $oRangeLast = .UsedRange.SpecialCells($xlCellTypeLastCell) Sleep(1000) $iRowCount = .Range(.Cells(1, 1), .Cells($oRangeLast.Row, $oRangeLast.Column)).Rows.Count Sleep(1000) _Excel_RangeWrite($oWorkbook_1, $oWorkbook_1.ActiveSheet,$aFileList[$i][2] , "AB3:AB"&$iRowCount) I have added some sleep because the application was crashing more often before, so i thought to slow down the code execution.
      But i didn't solve the issue.
      Has anyone an idea of what the problem might be?
      Thanks in advance.