Reading row cells sequentially?

[Alex037]

Hello everyone,

I have been searching through General Help and Support subforum trying to figure out the possible solution. I have been introduced to AutoIT years ago, and did performed some relatively basic tasks. Now, I'd like to automate one process that will eventually fall on someone else, but if it can be done, it's worth it.

I have 3 computers on a separate location, without internet access. They will be filling two excel files on a daily basis. I'm guessing there will be 5-8 columns (depending on the information entered), and maybe 50-100 rows per operater.  Due to the fact that the computers are without internet access, workers will have to go every day to central location and feed the info into database via browser (after work). Basically, it is a list of names, social ID's, doctors, prescriptions, etc. Now, it would be great to just import the data into MySql, but I am not allowed to do that (and I don't have access to it).  "Simplest" solution so far is pointing me to AutoIT. Combine daily excel files into a single one, and let the machine enters the data via browser. For example, read 1st row (cell by cell), and then paste the content in a web form. Once completed, go to 2nd row, and so on. One more problem is that I have different number of columns per row (in this case, per patient).

I see that the Excel UDF is playing a big part of it, and I have a hunch that is pointing me to arrays, but I'm not quite sure how to handle it. Furthermore, there are few radio buttons on a form that has to be delt with (but those are always the same, so this should not be a problem).

I'd appreciate any help / hint.

Thanks.

 

[water]

Welcome to AutoIt and the forum!

Correct, the Excel UDF and some array processing should do what you need.
Function _Excel_rangeRead allows to read single cells, rows or columns as a whole and even all used cell in a worksheet.

Based on the kind of data you handle security is an issue? Means if something goes wrong you would need to be able to restart where the processes was interrupted. There should be a log of all activity etc.

Depending on this requirements a different sign should be used. But it shouldn't be too hard

 

[Alex037]

Many thanks for your welcome and fast reply.

Idea is that one of the operator monitors the process of data entry. There are three operators, spending additional half hour every few days is still much better than spending few additional hours daily. This way, it should be done a lot quicker (and if something goes wrong, he/she will be able to respond). This problem will be dealt with or by gaining internet access, or a local copy of a database, but both solutions will take approximately 2-3 months. If it can be done with AutoIT, I'm willing to try to resolve it (and to gain additional knowledge, of course).

I will look into Excel UDF tonight, and I'll see how it goes. I will get back to sometime tomorrow :-)

Thank you for your time and help.

[water]

Will post an example on monday when i return to my WIndows PC.

[Alex037]

That would be great. I spent some time learning and playing with Excel UDF, and so far I can perform some basic tasks. I have looked all examples, and it would be neat if there is a possibility to extract the content of a single cell (once the row, or entire sheet has been copied), and then paste that content. Or, copy row, paste cell content sequentially... 

[water]

Here is a first try:

#include <Excel.au3>

Global $oExcel = _Excel_Open(True) ; Set this to False to let Excel run in the background
Global $oWorkbook, $aUsedRange

_ProcessWorkbook("C:\temp\test1.xlsx") ; Process workbooks 1 to 3
_ProcessWorkbook("C:\temp\test2.xlsx")
_ProcessWorkbook("C:\temp\test3.xlsx")

Func _ProcessWorkbook($sWorkbook)

    $oWorkbook = _Excel_BookOpen($oExcel, $sWorkbook) ; Open the Workbook
    $aUsedRange = _Excel_RangeRead($oWorkBook) ; Read all used cells
    For $i = 0 To UBound($aUsedRange, 1) - 1 ; Process all rows of the Workbook
        For $j = 0 To UBound($aUsedRange, 2) - 1
            ; Should the input be checked for valid data (empty cells ...)?
            ; Send the content to the browser here
        Next
    Next
    _Excel_BookClose($oWorkbook, False)

EndFunc

Which Browser do you want to use?

[Alex037]

Thank you very much for answering,

Target machine is a default Windows 10 OS, with almost nothing on it. IE browser is used. Each row should be checked for first empty cell, only as a signal to move to another row (if it will work that way). I'm guessing that each row will have 5-8 columns (no more), so 1st empty cell in each row will indicate the end of that row. That may serve as a signal to the rest of the AutoIt script to complete entry, and start again.

[water]

Next step is to identify the fields in IE where the data should be sent to. You can access them by Name (function _IEFormElementGetObjByName) or by ID (function _IEGetObjById).
The example in _IEFormElementGetObjByName shows how to write data to a field in a browser form.

[Alex037]

Thank you. I will be playing with it and let you know about it tomorrow. Looks like I have a lot of learning to do.

[water]

Correct

[Alex037]

I have managed to identify the fields, and I am still playing with it. Actually, I am still struggling with it. I'd give it a few more days to spend on it (in my free time), and will let you know about any progress. I cannot figure ubound. How to paste one cell value at a time, or how to paste them in appropriate boxes.

[water]

UBound($aUsedRange, 1)

Simply returns the size of a dimension of an array. Parameter 2 defines the dimension to check: 1: Returns the number of rows, 2: Returns the number of columns

[Alex037]

I have been fighting with it last week, without success...

UBound is killing me. I will try to do something about it in the next day or two, before I come back here, crying for help :-)

[water]

UBound isn't that complex.
It simply returns the number of elements in a specified dimension of an array.

Imagine an array with 4 rows and 3 columns. UBound returns the values 4 (# of rows) and 3 (# of columns).
BUT as the INDEX to access the elements of an array start with 0 you need to use values 0, 1 , 2 and 3 to access all rows of the Array and values 0, 1 and 2 to access each column in a row.
Example:

Global $aArray[4][3] = _
   [[11, 12, 13], _ ; Row 1
    [21, 22, 23], _ ; Row 2
    [31, 32, 33], _ ; Row 3
    [41, 42, 43]]   ; Row 4
$iNumberOfRows = UBound($aArray, 1) ; returns the number of rows in $aArray = 4
$iNumberOfCols = UBound($aArray, 2) ; returns the number of columns in $aArray = 3
ConsoleWrite("Number of rows   : " & $iNumberOfRows & @CRLF)
ConsoleWrite("Number of columns: " & $iNumberOfCols & @CRLF)
For $iRowIndex = 0 To $iNumberOfRows - 1
    For $iColIndex = 0 To $iNumberOfCols - 1
        ConsoleWrite($aArray[$iRowIndex][$iColIndex] & @CRLF)
    Next
Next