Chromwell Posted January 24, 2012 Share 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 Link to comment Share on other sites More sharing options...
Blue_Drache Posted January 24, 2012 Share Posted January 24, 2012 Are you using the Excel.au3 bolt-on? Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache Link to comment Share on other sites More sharing options...
Chromwell Posted January 24, 2012 Author Share 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... Link to comment Share on other sites More sharing options...
Blinky Posted January 24, 2012 Share 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 Link to comment Share on other sites More sharing options...
Chromwell Posted January 24, 2012 Author Share 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? Link to comment Share on other sites More sharing options...
Blinky Posted January 24, 2012 Share Posted January 24, 2012 why not delete the last one first u just need to compare all the row no. u need to delete Link to comment Share on other sites More sharing options...
Blue_Drache Posted January 24, 2012 Share 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 Link to comment Share on other sites More sharing options...
Chromwell Posted January 24, 2012 Author Share 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? Link to comment Share on other sites More sharing options...
Blue_Drache Posted January 24, 2012 Share 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 Link to comment Share on other sites More sharing options...
Chromwell Posted January 25, 2012 Author Share 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 Link to comment Share on other sites More sharing options...
lorenkinzel Posted January 25, 2012 Share 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 Link to comment Share on other sites More sharing options...
Jfish Posted April 11, 2012 Share 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 Link to comment Share on other sites More sharing options...
Reg2Post Posted April 12, 2012 Share 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 Link to comment Share on other sites More sharing options...
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