Reading for column each row as reference then get values from every cell.

[_Ray]

Hello everyone,

 

I am new to autoit. I am working with a project and trying to learn by myself but I am now stuck and I don't really know where to start.

here's my problem - I need to input values to browser (IE) coming from excel. The first column will be my reference value to search to IE of it exists. If not I will add record using this reference and fill up the other data using the other columns in excel.

Another thing is I need to ignore the header as well to read data starting from 2nd row.

Here's my code below as starter. Forgive me if this is dirty as I'm still learning. I did not include yet the IE function because I need to understand first how to get the value from the excel the way I wanted it. Then putting values in IE will be my next step. 

I hoe someone can help me with this

#include <Excel.au3>
#include <File.au3>

Local $oExcel =_Excel_Open(False) ; Set to false if want to run in background

$datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Book1.xlsx")
$datawb.worksheets("Sheet1").Activate
$LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
$mydata = _Excel_RangeRead($datawb, Default, "A1:D"  & $LastRow)

For $x = 0 To UBound($mydata) - 1

    For $y = 0 To UBound($mydata,2) - 1
        MsgBox(0,"Test",$mydata[$x][$y])
    Next
    MsgBox(0, "row", $mydata[$x][0]) ; Thinking if this can be the reference
Next

I attached as well my sample reference file

Book1.xlsx

[Nine]

Just start $x at 1 instead of 0, that will skip the header

[Subz]

Look at 2 dimensional arrays as $aArray[$iRow][$iColumn] the arrays indexes for both $iRow and $iColumn start at 0

So for example, if you copied A1 to E10

$aArray[0][0] = A1
$aArray[0][1] = B1
$aArray[0][2] = C1
$aArray[0][3] = D1
$aArray[0][4] = D1

$aArray[1][0] = A2
$aArray[1][1] = B2
etc...

When used in a loop

$aExcel[$x][0] = Row x, Column A
$aExcel[$x][1] = Row x, Column B
etc....

Does that make sense?

[_Ray]

Thank you for response Nine and Subz. got it

But my struggle is on how to use the value as reference on what the program will do next.

For example. I got the fist reference in A1 field then I will search if exists. If not, then I will now get the values from B to D to input it in IE. I use notepad here for example

I hard coded '2B' as reference for example to write all the columns for 2B but it did not write what I expecting.

 

#include <Excel.au3>
#include <File.au3>
;~ #include <Array.au3>

Run("notepad.exe")

Local $oExcel =_Excel_Open(False) ; Set to false if want to run in backgroun

$datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Book1.xlsx")
$datawb.worksheets("Sheet1").Activate
$LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
$mydata = _Excel_RangeRead($datawb, Default, "A1:D"  & $LastRow)
;~ $mycell = _Excel_RangeRead($datawb, Default, "A:A")
;~ _ArraySort($mydata, 0, 1)
_Excel_BookClose($datawb, False)
;~ If IsArray($mydata) Then _ArrayDisplay($mydata)

;~ for $x = 0 to UBound($mydata) - 1
;~     local $Sample = StringSplit($mydata[$x][0], "|")
;~     ConsoleWrite($x)
;~ Next

;~ Dim $aArray[2][4] = [[0, 1, 2, 3], [5, 15, 25, 30]]

For $x = 1 To UBound($mydata) - 1

    For $y = 0 To UBound($mydata,2) - 1
;~         MsgBox(0,"Test",$mydata[$x][$y]);  & " Array " & $x & "-" & $y)
        if $mydata[$x][$y] = "2B" then
            WinActivate("untitled - Notepad")
            Send($mydata[$x][$y])
            Send("{TAB}")
        EndIf
    Next
    MsgBox(0, "row", $mydata[$x][0])
    send(@CRLF)
Next

 

[_Ray]

Please ignore the commented lines. I forgot to remove

[Subz]

Try something like:

#include <Array.au3>
#include <Excel.au3>

Local $oExcel =_Excel_Open(False) ; Set to false if want to run in backgroun

$datawb = _Excel_BookOpen($oExcel,@ScriptDir & "\Book1.xlsx")
$datawb.worksheets("Sheet1").Activate
$LastRow = $datawb.ActiveSheet.UsedRange.Rows.Count
$aMyData = _Excel_RangeRead($datawb, Default, "A1:D"  & $LastRow)
_Excel_BookClose($datawb, False)
If Not WinExists("[CLASS:Notepad]", "") Then Run("notepad.exe")
Local $hWnd = WinWait("[CLASS:Notepad]", "", 10)
WinActivate($hWnd, "")
For $x = 1 To UBound($aMyData) - 1
    If $aMyData[$x][0] = "2B" Then
        ;~ Example 1 - Loop through each column
        For $y = 0 To UBound($aMyData, 2) - 1
            ControlSend($hWnd, "", "Edit1", $aMyData[$x][$y] & ($y < UBound($aMyData, 2) - 1 ? @TAB : @CRLF))
        Next
        ;~ Example 2 - Using _ArrayToString for a single line item
        ControlSend($hWnd, "", "Edit1", "~~~~ _ArrayToString Example ~~~~" & @CRLF)
        ControlSend($hWnd, "", "Edit1", _ArrayToString($aMyData, @TAB, $x, $x) & @CRLF)
    EndIf
Next

 

Edited December 13, 2021 by Subz
Forgot to include array.au3

[_Ray]

It worked! Thanks a lot!