Help Needed on looping through excel column

[vinyking]

Good Day,

I have a list of numbers in column B ranging from (B1:B1000).

I expect my script to return each cell value and enter that in a web form submit the form, then I perform a print of the loaded webpage and the next cell value is then processed in the same earlier web form. I want to loop untill the cell value is equal to nothing (<>"")

The following is an extract of what i have been able to achieve.

 

$oIE = _IECreate("http://www.Mywebsite")

$oForm = _IEFormGetObjByName($oIE, "fm1")

Local $oQuery = _IEFormElementGetObjByName($oForm, "login")

_IEFormElementSetValue($oQuery, "loging name")

Local $oQuery = _IEFormElementGetObjByName($oForm, "Password")

_IEFormElementSetValue($oQuery, "my password")

_IEFormSubmit($oForm)

Sleep(4000)

Local $sFilePath1 = "file pathMyexcelfile.xlsx" ;This file should already exist

Local $oExcel = _ExcelBookOpen($sFilePath1)

 

For $cell.Value in $oExcel."Client List".Range("B1:B1000") ; THIS IS WHERE I GET THE ERROR

 

 If $cell.Value <> "" Then

  Local $CellValue =  _ExcelReadCell($oExcel,$cell.Value)

 

     _IENavigate($oIE, "http://mywebsite.com")

 

$oForm = _IEFormGetObjByName($oIE, "GetHIAcctNo")

Local $oQuery = _IEFormElementGetObjByName($oForm, "AcctNo")

_IEFormElementSetValue($oQuery, $sCellValue)

Local $oSubmit = _IEGetObjByName($oIE, "Go")

_IEAction($oSubmit, "click")

_IELoadWait($oIE)

_IEaction($oIE, "selectall")

_IEaction($oIE, "copy")

_IEaction($oIE, "print")

Sleep (2000)

 Endif

Next

_IEaction($oIE, "quit")

 

 

Error is always at the stage of recognising the range and reading the cell value and of course how to do the looping 

I appreciate any help top resolve this issue

 

Edited December 11, 2013 by vinyking

[Wruck]

 

I have a list of numbers in column B ranging from (B1:B1000).

I expect my script to return each cell value and enter that in a web form submit the form, then I perform a print of the loaded webpage and the next cell value is then processed in the same earlier web form. I want to loop untill the cell value is equal to nothing (<>"")

Have you thought about using _ExcelReadArray()?

[vinyking]

Thanks let me read that up in the help file, I would then try it and revert to you.

Cheers.

[vinyking]

I have tried my hand with _ExcelReadArray

the problem now is how / what i can make the cellvalue reflect each cell value and set it into the form field?

[Wruck]

I would try a For loop through the Array you are given back?

[l3ill]

Hi vinyking,

  the forum is literally full of these.....

excel to webform site:www.autoitscript.com

[markyrocks]

What is the error? Only thing I c is your for statement is a Lil sketchy but I do see a variable $sCellValue that doesn't seem to be declared. If this is your error "(Array variable has incorrect number of subscripts or subscript dimension range exceeded)". Then you may need to use ubound

[markyrocks]

Ok this is what i've come up with.  Its basically untested except for the fact that i got past you error but i did change quite a few things but this is my best attempt to help.  Ok i read it like 10 times and had to edit it so it would technically work right after i originally posted.

expandcollapse popup

#include <Excel.au3>
; Script Start - Add your code below here

$oIE = _IECreate("http://www.Mywebsite")
$oForm = _IEFormGetObjByName($oIE, "fm1")
Local $oQuery = _IEFormElementGetObjByName($oForm, "login")
_IEFormElementSetValue($oQuery, "loging name")
Local $oQuery = _IEFormElementGetObjByName($oForm, "Password")
_IEFormElementSetValue($oQuery, "my password")
_IEFormSubmit($oForm)
Sleep(4000)
Local $sFilePath1 = "file path\Myexcelfile.xlsx" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath1)

If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf
dim $cell[1001]


For $range = 1 to Ubound($cell) -1 ; THIS IS WHERE I GET THE ERROR
 $cell[$range] = _ExcelReadCell($oExcel,"Client List",$range)
 If $cell[$range] = "" Then
      ExitLoop
endif
 
     _IENavigate($oIE, "http://mywebsite.com")
 
$oForm = _IEFormGetObjByName($oIE, "GetHIAcctNo")
Local $oQuery = _IEFormElementGetObjByName($oForm, "AcctNo")
_IEFormElementSetValue($oQuery, $cell[$range])
Local $oSubmit = _IEGetObjByName($oIE, "Go")
_IEAction($oSubmit, "click")
_IELoadWait($oIE)
_IEaction($oIE, "selectall")
_IEaction($oIE, "copy")
_IEaction($oIE, "print")

Sleep (2000)
 
Next
_IEaction($oIE, "quit")

Edited December 12, 2013 by markyrocks

[vinyking]

Thanks to Wruck, Markyrocks, and every body,

I followed your lead read through the examples, tried out my hands on a couple of examples and finally here is the code that works for me.

expandcollapse popup

#cs ----------------------------------------------------------------------------

    AutoIt Version: 3.3.8.1
    Author:         myName

    Script Function:
    Template AutoIt script.

#ce ----------------------------------------------------------------------------

; Script Start - Add your code below here

#include <IE.au3>
#include <Excel.au3>
#include <Array.au3>
;-----------------------------------------------------------------------------
;trying to open web page login form
;-----------------------------------------------------------------------------
$oIE = _IECreate("http://www.mywebpage")
$oForm = _IEFormGetObjByName($oIE, "form name")
Local $oQuery = _IEFormElementGetObjByName($oForm, "login")
_IEFormElementSetValue($oQuery, "log in name")
Local $oQuery = _IEFormElementGetObjByName($oForm, "password")
_IEFormElementSetValue($oQuery, "my password")
_IEFormSubmit($oForm)
Sleep(4000)
;-----------------------------------------------------------------------------
;web page login complteted
;-----------------------------------------------------------------------------


;-----------------------------------------------------------------------------
;Open Excel file and capture/read Array
;-----------------------------------------------------------------------------
Local $sFilePath1 = "file path\Myexcell file.xlsx" ;This file should already exist
Local $oExcel = _ExcelBookOpen($sFilePath1)


If @error = 1 Then
    MsgBox(0, "Error!", "Unable to Create the Excel Object")
    Exit
ElseIf @error = 2 Then
    MsgBox(0, "Error!", "File does not exist - Shame on you!")
    Exit
EndIf
Dim $cell[1001]

Local $aArray1 = _ExcelReadArray($oExcel, 1, 2, 4, 1) ;Direction is Vertical
_ArrayDisplay($aArray1, "Vertical")
_ExcelBookSaveAs($oExcel,  "file path\Myexcell file.xlsx") ; file saved
_ExcelBookClose($oExcel) ; And finally we close out
;-----------------------------------------------------------------------------
;Excel file opened Array reading completed and Excel file closed
;-----------------------------------------------------------------------------

;-----------------------------------------------------------------------------
;Lopping through Array and executing Web form
;-----------------------------------------------------------------------------

For $i = 0 To UBound($aArray1) - 1


    _IENavigate($oIE, "http://www.mywebpage")

    $oForm = _IEFormGetObjByName($oIE, "form name")
    Local $oQuery = _IEFormElementGetObjByName($oForm, "form id")
    _IEFormElementSetValue($oQuery, $aArray1[$i])
    Local $oSubmit = _IEGetObjByName($oIE, "button")
    _IEAction($oSubmit, "click")
    _IELoadWait($oIE)
    _IEAction($oIE, "print")
    Sleep(9000)

Next

;-----------------------------------------------------------------------------
;end of Loop through Array and executing Web form
;-----------------------------------------------------------------------------
_IEAction($oIE, "quit")

Now I am moving on to other challenges as regard this project.

Cheers

Edited December 13, 2013 by vinyking