Using table from excel as AutoIT array

[Atrax27]

Yes it's another array topic! Our favorite. 

Looking for a way to transfer an excel spreadsheet into some sort of array that AutoIT can run. I’ve tried some different arrays that have been posted earlier in my threads (thanks guys!), but the importance here is that it is a dynamic and changing number of rows each time. One time it may be a single row, another time it may be 30 rows.

And each time it needs to perform a certain function between all the rows, which changes ever three rows.

Here is an working example which does what I want, but changing this is very difficult as it requires flattening the array first within excel each time I want to run it.

expandcollapse popup

#include <AutoItConstants.au3>
Opt("sendkeydelay", 25)


$i = 0
$s = 35
Dim $array[35] = ["Yellow","5","Large","7","Cookies","Orange","6","Small","7","Cakes","Blue","1","Medium","7","Breads","Purple","45","Huge","7","Oysters","Grey","2","Tall","7","Lemons","Green","3","Slow","7","Chocolates","Red","99","Furry","7","Ice Creams"]


Run("notepad.exe")
WinWaitActive("Untitled - Notepad")


Do
send($array[$i])
Send(" puppies have over ")
$i = $i + 1
send($array[$i])
Send(" very very ")
$i = $i + 1
send($array[$i])
Send(" copies of a cool new book. After they eat ")
$i = $i + 1
send($array[$i])
Send(" tasty ")
$i = $i + 1
send($array[$i])
Send(" they are happy.")
Send("{ENTER}")
$i = $i + 1
Until $i = 15

 

Send("{ENTER 5}")

 

Do
send($array[$i])
Send(" puppies have over ")
$i = $i + 1
send($array[$i])
Send(" very very ")
$i = $i + 1
send($array[$i])
Send(" copies of a cool new book. After they eat ")
$i = $i + 1
send($array[$i])
Send(" tasty ")
$i = $i + 1
send($array[$i])
Send(" they are happy.")
Send("{ENTER}")
$i = $i + 1
Until $i = 30

 

Send("{ENTER 5}")

 

Do
send($array[$i])
Send(" puppies have over ")
$i = $i + 1
send($array[$i])
Send(" very very ")
$i = $i + 1
send($array[$i])
Send(" copies of a cool new book. After they eat ")
$i = $i + 1
send($array[$i])
Send(" tasty ")
$i = $i + 1
send($array[$i])
Send(" they are happy.")
Send("{ENTER}")
$i = $i + 1
Until $i = $
Until $i = $s

 

 

Is there a way to possibly just copy-paste an excel table into some sort of “Array processor” which would make my life easier? I know it wouldn’t work in my test configuration, but perhaps what I have is as easy as it’s going to get?

[GokAy]

The range you want is:

Say, worksheet name is: Sheet1 and table starts at cell "A1" and a header

rRange = Worksheets("Sheet1").range("A1").currentregion.offset(1,0).resize(Worksheets("Sheet1").range("A1").currentregion.rows.count - 1,Worksheets("Sheet1").range("A1").currentregion.columns.count)

I am not familiar with excel.udf, maybe a function for getting this into a range exists.

Edited December 4, 2020 by GokAy

[water]

Yes, it does: _Excel_RangeRead 😃

[GokAy]

@water Heh, actually a typo there. I know about range exists from reading posts here, however, can you get the range into an array with any simple function?

In VBA it would be:

Dim arrRange() as variant

arrRange = whatever_range

[Nine]

 

@GokAy

_Excel_RangeRead

Success:

the data from the specified cell(s). A string for a cell, a zero-based array for a range of cells.

[water]

@GokAy Which typo?

[GokAy]

@water 

14 hours ago, GokAy said:

maybe a function for getting this into a range exists.

I meant to ask,

"maybe a function for getting this into 'an array' exists"

[water]

I see

[Atrax27]

EDIT - see next post. 

 

Edited December 6, 2020 by Atrax27
post was overcome by events

[Atrax27]

It's hilarious, if I copy 3x5 table from excel, it spits out weird formatted stuff based on the fact that there is a pipe (|) between array items, but not at the end of the array. But if I copy 4x5 table from excel, it recognizes the pipes and thus does not spit out hot formatted garbage. The results are still reversed (upside down) and I don't know how to fix that.

 

expandcollapse popup

#include <Array.au3>
#include <AutoItConstants.au3>
Opt("sendkeydelay", 25)

Local $sExcelData = ClipGet()
    $sExcelData = StringTrimRight(StringReplace($sExcelData, @TAB, "|"), 2)
Local $aExcel1D = StringSplit($sExcelData, @LF, 2)
If IsArray($aExcel1D) Then
    If StringInStr($sExcelData, "|") Then
        Local $aExcel2D[0][0]
        For $i = (UBound($aExcel1D) - 1) To 0 Step - 1
            If StringStripWS($aExcel1D[$i], 8) = "" Then ContinueLoop
            $aExcelLine = StringSplit($aExcel1D[$i], "|", 2)
            ReDim $aExcel2D[UBound($aExcel2D)][UBound($aExcelLine)]
            _ArrayAdd($aExcel2D, _ArrayToString($aExcelLine))
        Next
    Else
        Local $aExcel2D = $aExcel1D
    EndIf
EndIf
_ArrayDisplay($aExcel2D)

Run("notepad.exe")
WinWaitActive("Untitled - Notepad")
$i = 0
Do
send($aExcel2D[0][$i])
Send(" color. ")
send($aExcel2D[1][$i])
Send(" quantity.  ")
send($aExcel2D[2][$i])
Send(" size.  ")
send($aExcel2D[3][$i])
Send(" number. ")
send($aExcel2D[4][$i])
Send(" treat.")
Sleep(500)
Send("{ENTER}")
$i = $i + 1
Until $i = 3

Copy this

Yellow	Orange	Blue
5	6	1
Large	Small	Medium
7	7	7
Cookie	Cake	Bread

 

gives you this

Quote

Cookie color. 7 quantity.  Large size.  5 number. Yellow treat.
Cake color. 7 quantity.  Small size.  6 number. Orange treat.
Bread color. 7
 quantity.  Medium
 size.  1
 number. Blue
 treat.
 

But copy this

Yellow	Orange	Blue	Purple
5	6	1	45
Large	Small	Medium	Huge
7	7	7	7
Cookie	Cake	Bread	Oysters

 

Gives you this

Quote

Cookie color. 7 quantity.  Large size.  5 number. Yellow treat.
Cake color. 7 quantity.  Small size.  6 number. Orange treat.
Bread color. 7 quantity.  Medium size.  1 number. Blue treat.
 

 

[Nine]

Here :

#include <Array.au3>
#include <AutoItConstants.au3>

Opt("sendkeydelay", 25)

Local $sExcelData = ClipGet()
MsgBox ($MB_SYSTEMMODAL,"",$sExcelData)
StringReplace(StringMid($sExcelData, 1, StringInStr($sExcelData,@CRLF)), @TAB, @TAB)
Local $aExcel2D[0][@extended+1]
_ArrayAdd($aExcel2D, $sExcelData, 0, @TAB, @CRLF)
_ArrayDisplay($aExcel2D)

Run("notepad.exe")
WinWaitActive("[CLASS:Notepad]")

For $i = 0 To UBound($aExcel2D, 2)
  Send($aExcel2D[0][$i])
  Send(" color. ")
  send($aExcel2D[1][$i])
  Send(" quantity.  ")
  send($aExcel2D[2][$i])
  Send(" size.  ")
  send($aExcel2D[3][$i])
  Send(" number. ")
  send($aExcel2D[4][$i])
  Send(" treat.")
  Send("{ENTER}")
Next

That works for both of your examples

[Atrax27]

@Nine

Wow. Really nice 🤤. One last question I think on this one

expandcollapse popup

#include <Array.au3>
#include <AutoItConstants.au3>

Opt("sendkeydelay", 20)

Local $sExcelData = ClipGet()
MsgBox ($MB_SYSTEMMODAL,"",$sExcelData)
StringReplace(StringMid($sExcelData, 1, StringInStr($sExcelData,@CRLF)), @TAB, @TAB)
Local $aExcel2D[0][@extended+1]
_ArrayAdd($aExcel2D, $sExcelData, 0, @TAB, @CRLF)
_ArrayDisplay($aExcel2D)

Run("notepad.exe")
WinWaitActive("[CLASS:Notepad]")

;For $i = 0 To UBound($aExcel2D, 2)   === THIS is amazing
For $i = 0 To 4
  Send($aExcel2D[$i][0])
  Send(" color. ")
  send($aExcel2D[$i][1])
  Send(" quantity.  ")
  send($aExcel2D[$i][2])
  Send(" size.  ")
  send($aExcel2D[$i][3])
  Send(" number. ")
  send($aExcel2D[$i][4])
  Send(" treat.")
  Send("{ENTER}")
Next
  Send("{ENTER 4}")

  For $i = 5 To 10
  Send($aExcel2D[$i][0])
  Send(" color. ")
  send($aExcel2D[$i][1])
  Send(" quantity.  ")
  send($aExcel2D[$i][2])
  Send(" size.  ")
  send($aExcel2D[$i][3])
  Send(" number. ")
  send($aExcel2D[$i][4])
  Send(" treat.")
  Send("{ENTER}")
  Next
    Send("{ENTER 4}")

  For $i = 11 to 16
  Send($aExcel2D[$i][0])
  Send(" color. ")
  send($aExcel2D[$i][1])
  Send(" quantity.  ")
  send($aExcel2D[$i][2])
  Send(" size.  ")
  send($aExcel2D[$i][3])
  Send(" number. ")
  send($aExcel2D[$i][4])
  Send(" treat.")
  Send("{ENTER}")
Next

Using this table

Yellow	5	Large	7	Cookie
Orange	6	Small	7	Cake
Blue	1	Medium	7	Bread
Purple	45	Huge	7	Oysters
Grey	2	Tiny	7	Lemons
Green	3	Massive	7	Chocolate
Red	99	Big	7	Ice Cream
Yellow	5	Large	7	Cookie
Orange	6	Small	7	Cake
Blue	1	Medium	7	Bread
Purple	45	Huge	7	Oysters
Grey	2	Tiny	7	Lemons
Green	3	Massive	7	Chocolate
Red	99	Big	7	Ice Cream

Leaves you with a single blank at the very end. Any idea why or how to get rid of that last item? 

Quote

 color.  quantity.   size.   number.  treat.
 

 

[Nine]

Oh, I did not copy last @CRLF.  In your original you were stripping last 2 chars.

[Atrax27]

Sorry about that, I changed it around a bit I know. How would you fix the code it to accommodate the newer format? 

[Nine]

Like I said just strip last 2 chars :

Local $sExcelData = StringTrimRight(ClipGet(),2)

 

[Atrax27]

That worked PERFECT thank you. But now I'm curious about what two characters are getting stripped from the array? Is it a ghost carriage return or "end of array" character? 

[Nine]

It is a @CRLF (@Cr & @LF)   

[Atrax27]

Learn something new everyday. Seems so trivial and obvious but never thought of it before, kinda like "oh, the air I'm breathing actually has O2 in it??" 

 

Thanks again. 

[Nine]