Sign in to follow this  
Followers 0
vinyking

Help Needed on looping through excel column

9 posts in this topic

#1 ·  Posted (edited)

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  :sweating:
I appreciate any help top resolve this issue :thumbsup:
 
Edited by vinyking

Share this post


Link to post
Share on other sites



 

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()?

Share this post


Link to post
Share on other sites

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

Cheers.

Share this post


Link to post
Share on other sites

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?

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

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.

#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 by markyrocks

Share this post


Link to post
Share on other sites

#9 ·  Posted (edited)

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.

#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 by vinyking

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
Sign in to follow this  
Followers 0