Excel Macro to Autoit... Selecting multiple Rows?

[Chromwell]

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]

Are you using the Excel.au3 bolt-on?

[Chromwell]

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]

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]

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]

why not delete the last one first

u just need to compare all the row no. u need to delete

[Blue_Drache]

why not delete the last one first

u just need to compare all the row no. u need to delete

This. Run your loop in reverse order and the rows don't change.

[Chromwell]

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]

Currently, yes. Try bugging the developer of the Excel.au3

[Chromwell]

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]

What does ,in a limited fashion, work is:

.Sheets("Sheet3").Select

.Rows("6:8").Select

.Selection.Delete

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

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

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

EndWith

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

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

[Reg2Post]

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