Jump to content
Atrax27

Using table from excel as AutoIT array

Recommended Posts

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.

#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?

Share this post


Link to post
Share on other sites

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 by GokAy

Share this post


Link to post
Share on other sites

Yes, it does: _Excel_RangeRead 😃


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@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

Share this post


Link to post
Share on other sites

 

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

Share this post


Link to post
Share on other sites

@GokAy Which typo?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

@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"

Share this post


Link to post
Share on other sites

I see ;)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2020-10-10 - Version 1.5.2.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2020-12-15 - Version 1.6.3.1) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2020-06-27 - Version 1.3.2.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
Task Scheduler (2019-12-03 - Version 1.5.1.0) - Download - General Help & Support - Wiki

Tutorials:
ADO - Wiki, WebDriver - Wiki

 

Share this post


Link to post
Share on other sites

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.

 

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

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

@Nine

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

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

 

Share this post


Link to post
Share on other sites

Share this post


Link to post
Share on other sites

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? 

Share this post


Link to post
Share on other sites

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. 

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

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...