Chromwell Posted January 24, 2012 Posted January 24, 2012 Hi, i want to delete multiple rows from a given excel file, i have all start/end row numbers i want to delete... In Excel VBA a macro records the following selection: Range("9:20,22:33,35:46").Select I have generated the string in an variable with all start/end row numbers in the same format... $Rows2Delete = "9:20,22:33,35:46" $oExcel.Application.Range("9:20,22:33,35:46").Select or $oExcel.Range("9:20,22:33,35:46").Select or what i need for my application: $oExcel.Application.Range('"'& $Rows2Delete & '"').Select This does not select anything, it only generates a "==> The requested action with this object has failed." Do i oversee any little syntax problem or is it not possible to select multiple rows in different places via autoit to delete them at the same time? thnx Chromwell
Blue_Drache Posted January 24, 2012 Posted January 24, 2012 Are you using the Excel.au3 bolt-on? Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache
Chromwell Posted January 24, 2012 Author Posted January 24, 2012 Yep i use the included Excel.au3 Attached the Excel File with $oExcel = _ExcelBookAttach("FILENAMEPATH") The connection works normally, i can write Cells/Read whole Tables and so on...
Blinky Posted January 24, 2012 Posted January 24, 2012 u can read, cells, delete cells, u cant visual select cells from my experience u cant do anything like that with exel object i dont think u can remove entire rows u can only clear their content
Chromwell Posted January 24, 2012 Author Posted January 24, 2012 Well it works to select a single row "range" and delete it. Example code that works: #include <Excel.au3> $oExcel = _ExcelBookNew() _ExcelWriteCell($oExcel, "XXX", "A1:H20") $oExcel.Application.Rows("5:10").Delete But my problem is i have a big excel file with many similar looking tables where i need to delete a few specific ones. I have all the code written to check each start/end row of this tables i need to delete. The problem is i need to delete all tables i want to purge at once because if i delete the first one, the start/end row for the next changed with this purge... Excel VBA as i wrote it above just does select multiple rows (seperate with spaces etc.) with just that simple line of code: Range("9:20,22:33,35:46").Select This selects Row 9 to 20, 22 to 33 and 35 to 46. Is it really possible that i can´t send this via Autoit?
Blinky Posted January 24, 2012 Posted January 24, 2012 why not delete the last one first u just need to compare all the row no. u need to delete
Blue_Drache Posted January 24, 2012 Posted January 24, 2012 why not delete the last one firstu just need to compare all the row no. u need to deleteThis. Run your loop in reverse order and the rows don't change. Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache
Chromwell Posted January 24, 2012 Author Posted January 24, 2012 Yeah, thats a workaround i have not thought of *plonk*... thanks.. But anyway, is it really impossible to transform that Excel VBA Code to Autoit?
Blue_Drache Posted January 24, 2012 Posted January 24, 2012 Currently, yes. Try bugging the developer of the Excel.au3 Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache
Chromwell Posted January 25, 2012 Author Posted January 25, 2012 I really don´t understand how the UDF could change anything with the error selecting multiple rows via COM... #include <IE.au3> _IEErrorHandlerRegister() $oExcel = ObjCreate("Excel.Application") $oExcel.Visible = 1 $oExcel.WorkBooks.Add $oExcel.Application.Range("1:1").Select ; this works $oExcel.Application.Range("1:1,3:3").Select ; this does not This is completly without the excel UDF and the syntax of the second selection throws an COM Error that does not tell my anything --> COM Error Encountered in test.au3 ----> $IEComErrorScriptline = 10 ----> $IEComErrorNumberHex = 80020009 ----> $IEComErrorNumber = -2147352567 ----> $IEComErrorWinDescription = ----> $IEComErrorDescription = ----> $IEComErrorSource = ----> $IEComErrorHelpFile = ----> $IEComErrorHelpContext = 0 ----> $IEComErrorLastDllError = 0
lorenkinzel Posted January 25, 2012 Posted January 25, 2012 (edited) What does ,in a limited fashion, work is:.Sheets("Sheet3").Select .Rows("6:8").Select .Selection.DeleteDeletes rows 6, 7&8. It only works on consecutive rows.I got it by using the Excel macro recorder, copy the code into scite & 'kick it around' 'till it looks like au3.The Excel macro recorder will not let (me) select multiple rows unless they are consecutive. (Excel 07)I tried: .Rows("6").Select .Selection.Delete Sleep(50) .Rows("9).Select .Selection.DeleteIt worked, but unfortunately after row6 was deleted, row9 wasn't the same row9 that I was after anymore expandcollapse popup#include <GUIConstantsEx.au3> #include <Excel.au3> $oExcel = ObjGet("", "Excel.Application") If @error Then MsgBox(0, "", "You need to have Excel running for this to work", 4) Exit EndIf GUICreate("x", 80, @DesktopHeight * .925, @DesktopWidth * .75, 10) WinSetOnTop("x", "", 1) $label3 = GUICtrlCreateLabel("", 4, @DesktopHeight * .875 - 70, 72, 25) $copy = GUICtrlCreateButton("copy", 10, (@DesktopHeight * .165 + 10), 60, 25) GUICtrlSetState($copy, $GUI_SHOW) GUISetState() While 1 $nMsg = GUIGetMsg() Select Case $nMsg = $GUI_EVENT_CLOSE Exit Case $nMsg = $copy $sheetname = _ExcelSheetNameGet($oExcel) GUICtrlSetData($label3, $sheetname) $goback = GUICtrlRead($label3) With $oExcel .ActiveSheet.Unprotect;sheet protection in place to save me from boo-boos during data entry .Sheets("Sheet3").Visible = True;unhide sheet that I wish to copy from Sleep(50) .Sheets("Sheet3").Select;open the sheet ;.Rows("6:8").Select ;.Selection.Delete .Rows("8:8").Select .Selection.Delete Sleep(50) .Rows("6:6").Select .Selection.Delete _ExcelSheetActivate($oExcel, $goback);back to sheet in use Sleep(50) .ActiveSheet.Protect;re-protect the active sheet EndWith EndSelect WEndEdit: had a real stupid boo-boo in there. Fixed it.Edit2: got it ! .Sheets("Sheet3").Select Sleep(50) .Range("10:10,12:12,14:14").Select .Range("A14").Activate ;>>>>>>>>>>>>>>this one turns the trick. Much like you'd do it with the mouse. .Selection.Delete EndWithWorks with Excel & AI. The reason Excel macro recorder would not let me select non-consecutive rows is that I also had an individual cell selected (on several tries). Edited January 25, 2012 by lorenkinzel
Jfish Posted April 11, 2012 Posted April 11, 2012 I have a question about all this ... I am trying to do something very similar. I read a long sheet into an array and look for certain values in a column that identify the rows I want to delete. You can see what I am doing here (note: the autoit code tag button is not working for some reason): dim $range="" for $a=0 to ubound($dataArray)-1 if $dataArray[$a][1] == "State Province" Then $range &= $a&":"&$a&"," EndIf next $finalRange=StringTrimRight($range,1) MsgBox("","This is the range: ",$finalRange) The message box appears to show the results I want based on your example above: 5:5,50:50,90:90,... etc. Then I use the following to try to select the range: .Range($finalRange).Select This action, however, does not work. Instead, I get the following message: "C:UsersRC01712DocumentsScriptsScrub2.au3 (51) : ==> The requested action with this object has failed.:" Is it permissible to use a variable for the range as an argument in the selection? What am I doing wrong? Any help would be greatly appreciated. Regards, JFish Build your own poker game with AutoIt: pokerlogic.au3 | Learn To Program Using FREE Tools with AutoIt
Reg2Post Posted April 12, 2012 Posted April 12, 2012 (edited) I was interested in the answer to this topic so I did some testing and found a working property to delete multiple non-contiguous rows/columns.To anyone interested, try the following:.ActiveSheet.Range("Range").EntireRow.Delete [For Rows].ActiveSheet.Range("Range").EntireColumn.Delete [For Columns]"Range" can be a correctly formatted range for non-contiguous rows or columns separated by a comma. "Range" can also be replaced by a variable.@JFish: Try putting your $range value in quotes EDIT: Try $finalRange value in quotes not $range, after you remove the last comma. Edited April 12, 2012 by Reg2Post
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now