Sign in to follow this  
Followers 0
Chromwell

Excel Macro to Autoit... Selecting multiple Rows?

13 posts in this topic

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

Share this post


Link to post
Share on other sites



Are you using the Excel.au3 bolt-on?


Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

why not delete the last one first

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

Share this post


Link to post
Share on other sites

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.


Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache

Share this post


Link to post
Share on other sites

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? :)

Share this post


Link to post
Share on other sites

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


Lofting the cyberwinds on teknoleather wings, I am...The Blue Drache

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#11 ·  Posted (edited)

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

#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 by lorenkinzel

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#13 ·  Posted (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 by Reg2Post

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