Jump to content

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


Go to solution Solved by Subz,

Recommended Posts

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

Link to post
Share on other sites

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?

Link to post
Share on other sites

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

 

Link to post
Share on other sites
  • Solution

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 by Subz
Forgot to include array.au3
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...