Help to load Txt or Xlsx information to I.E

[vidaLL]

Good Night all.

im trying to elaborate a script that load information from a notepad or a excel document. I really really have no experience in autoit so i need your help guys,

I have the following layout :

Information to imput

Importation number   Enterprise      Value     Date
1                               111111111     1.00   07/01/2013
2                               222222222     2.00   07/01/2013
3                               333333333     3.00   07/01/2013
4                               444444444     4.00   07/01/2013
5                               555555555     5.00   07/01/2013
6                               666666666     6.00   07/01/2013
7                               777777777     7.00   07/01/2013
8                               888888888     8.00   07/01/2013
9                               999999999     9.00   07/01/2013
10                            1111111110   10.00   07/01/2013
 

#include <IE.au3>
$oIE =_IECreate()
_IENavigate($oIE, www.fazenda.sp.gov.br/simp)
_IELoadWait($oIE)
$o_form = _IEFormGetObjByName($oIE,"form1")
$o_txtCpf = _IEFormElementGetObjByName($o_form, "txtCpf") 

$o_txtNumDoc = _IEFormElementGetObjByName($o_form, "txtNumDoc") 
$O_btnConsultar = _IEFormElementGetObjByName($o_form, "btnConsultar") 
_IEFormElementSetValue($o_txtcpf, "11111111111") ; txtCpf = Enterprise
_IEFormElementSetValue($o_txtNumDoc, "1"); txtNumDoc = Import number
$O_btnConsultar.click

_IELoadWait($oIE)
$o_form = _IEFormGetObjByName($oIE,"_ctl0")
$O_btnContinuar = _IEFormElementGetObjByName($o_form, "btnContinuar")
$O_btnContinuar.click

_IELoadWait($oIE)
$o_form = _IEFormGetObjByName($oIE,"Form1")
$O_btnGare = _IEFormElementGetObjByName($o_form, "btnGare")
$O_btnGare.click

_IELoadWait($oIE)
$o_form = _IEFormGetObjByName($oIE,"Form1")
$o_txtObs = _IEFormElementGetObjByName($o_form, "txtObs")
$o_txtDataPagto = _IEFormElementGetObjByName($o_form, "txtDataPagto")
$o_txtReceita = _IEFormElementGetObjByName($o_form, "txtReceita")
_IEFormElementSetValue($o_txtObs, "1"); txtObs = Import number
_IEFormElementSetValue($o_txtDataPagto,"01/08/2013")
_IEFormElementSetValue($o_txtReceita,""); txtReceita = value
$O_btnGerar.click

Imagine that i have almost 250 files to input and generate the collection on web page.

Is there a way to create a loop that the field (below)seek information in another document like a txt or excel document ?

_IEFormElementSetValue($o_txtcpf, "11111111111") ; txtCpf = Enterprise
_IEFormElementSetValue($o_txtNumDoc, "1"); txtNumDoc = Import number
 

_IEFormElementSetValue($o_txtObs, "1"); txtObs = Import number
_IEFormElementSetValue($o_txtReceita,""); txtReceita = value
 

Thanks for the attention.

Edited July 4, 2013 by vidaLL

[big_daddy]

Welcome to the AutoIt Forums!

Take a look at the _Excel* functions in the help file. Once you have an example at includes your Excel work post any additional questions. My suggestion would be to narrow your questions down to the smallest section of code possible. Feel free to also include any files needed to test and troubleshoot the code.

[vidaLL]

Welcome to the AutoIt Forums!

Take a look at the _Excel* functions in the help file. Once you have an example at includes your Excel work post any additional questions. My suggestion would be to narrow your questions down to the smallest section of code possible. Feel free to also include any files needed to test and troubleshoot the code.

 

Thaks Big_dady, but i just want to know if is possible to use a worksheet for example to load information in a web page.

 But "My suggestion would be to narrow your questions down to the smallest section of code possible." its impossible for me. Because i have just one day of experience ...

[big_daddy]

Yes, it is possible. 

[water]

Use the Excel UDF that comes with AutoIt. Function _ExcelReadSheetToArray reads the content of a worksheet into an array.

Then loop thought the array and write the data into the web page.

Edited July 5, 2013 by water

[vidaLL]

Use the Excel UDF that comes with AutoIt. Function _ExcelReadSheetToArray reads the content of a worksheet into an array.

Then loop thought the array and wirte the data into the web page.

 

Do you have a simple example like that ?

[water]

Every function comes with an example in the help file. So please scroll down the link I posted above.

From your OP it looks like you already managed the IE part.

[vidaLL]

Yes. I saw but there is no example of two functions references.  So sad.

[water]

Sorry, but what do you mean by "two functions references"?

[coryc1123]

Just went through the same thing. Take a look at my thread and see if it helps

[coryc1123]

Also it may help if we have the html source code for the website you are posting too. right click the site and click view source

[vidaLL]

Sorry, but what do you mean by "two functions references"?

 

I mean _Excel and _IE in the same script.

 

Also it may help if we have the html source code for the website you are posting too. right click the site and click view source

 

I don't know to put it here. so the site is www.fazenda.sp.gov.br/simp .

[water]

For sure you can have two UDFs in a single script:

#include <Excel.au3>
#include <IE.au3>
Global $sExcelFile = "xxxx" ; <== Add the name of the Excel file here
Global $oExcel = _ExcelBookOpen($sExcelFile)
Global $aExcelData = _ExcelReadSheetToArray($Excel)
For $iRow = 1 To $aExcelData ; Process all read Excel rows
    _Process($aExcelData[$iRow][1], $aExcelData[$iRow][2], $aExcelData[$iRow][3], $aExcelData[$iRow][4])
Next

Now you just have to pack the code from your first post into a function named _Process. The 4 parameters passed to the function are the first 4 fields of the record.

E.g: 1, 111111111, 1.00 and 07/01/2013

[vidaLL]

For sure you can have two UDFs in a single script:

#include <Excel.au3>
#include <IE.au3>
Global $sExcelFile = "xxxx" ; <== Add the name of the Excel file here
Global $oExcel = _ExcelBookOpen($sExcelFile)
Global $aExcelData = _ExcelReadSheetToArray($Excel)
For $iRow = 1 To $aExcelData ; Process all read Excel rows
    _Process($aExcelData[$iRow][1], $aExcelData[$iRow][2], $aExcelData[$iRow][3], $aExcelData[$iRow][4])
Next

Now you just have to pack the code from your first post into a function named _Process. The 4 parameters passed to the function are the first 4 fields of the record.

E.g: 1, 111111111, 1.00 and 07/01/2013

 

Great... I did it ...  but no enough as usual 

How can i make a loop in the process. I am Attaching my Excel file and the script ... I appreciate your useful help.

Pasta1.xls

TEST.au3

Sorry to keep asking all the time, but this process would help a lot in my work.

Thanks again.

[water]

That's how I understand your problem so far:

To process one record you do the following steps:

• _IENavigate to the website
• Get the elements of the form
• Use _IEFormElementSetValue to fill in the data (this happens 3 times)
• Click the submit button (this happens 3 times)
• Wait until the page has finished re-loading

After having submitted a record. Do you get the same page to enter the next record?

[vidaLL]

That's how I understand your problem so far:

To process one record you do the following steps:

• _IENavigate to the website
• Get the elements of the form
• Use _IEFormElementSetValue to fill in the data (this happens 3 times)
• Click the submit button (this happens 3 times)
• Wait until the page has finished re-loading

After having submitted a record. Do you get the same page to enter the next record?

 

Exactly. It's already done but i im trying to put a save with the ID from the webpage but i have no sucess.

expandcollapse popup

#include <Excel.au3>
#include <IE.au3>
Global $sExcelFile = "C:\Users\e8760255\Desktop\Pasta1.xls" ; <== Add the name of the Excel file here and your User
Global $oExcel = _ExcelBookOpen($sExcelFile)
Global $aExcelData = _ExcelReadSheetToArray($oExcel);$oExcel

$oIE = _IECreate()
_IENavigate($oIE, "www.fazenda.sp.gov.br/simp"); https://webhomolog.fazenda.sp.gov.br/simp
$o_form = _IEFormGetObjByName($oIE, "form1")
$o_txtCpf = _IEFormElementGetObjByName($o_form, "txtCpf")
$o_txtNumDoc = _IEFormElementGetObjByName($o_form, "txtNumDoc")
$O_btnConsultar = _IEFormElementGetObjByName($o_form, "btnConsultar")

For $iRow = 1 To $aExcelData ; Process all read Excel rows
    _IEFormElementSetValue($o_txtCpf, $aExcelData[$iRow][1])
    _IEFormElementSetValue($o_txtNumDoc, $aExcelData[$iRow][2])
Next
$O_btnConsultar.click
_IELoadWait($oIE)

;I'LL NOT HAVE THIS SPED ALL TIMES (IN MY EXAMPLE ILL NOT HAVE THIS STEP)
$o_form = _IEFormGetObjByName($oIE, "_ctl0")
$o_btnContinuar = _IEFormElementGetObjByName($o_form, "btnContinuar")
If not isObj($o_btnContinuar) Then
$o_form = _IEFormGetObjByName($oIE, "Form1")
$O_btnGare = _IEFormElementGetObjByName($o_form, "btnGare")
$O_btnGare.click
_IELoadWait($oIE)
EndIf


; Doc informations
$o_form = _IEFormGetObjByName($oIE, "Form1")
$o_txtObs = _IEFormElementGetObjByName($o_form, "txtObs")
$o_txtDataPagto = _IEFormElementGetObjByName($o_form, "txtDataPagto")
$o_txtReceita = _IEFormElementGetObjByName($o_form, "txtReceita")


For $iRow = 1 To $aExcelData ; Process all read Excel rows
    _IEFormElementSetValue($o_txtObs, $aExcelData[$iRow][3])
    _IEFormElementSetValue($o_txtDataPagto, $aExcelData[$iRow][4])
    _IEFormElementSetValue($o_txtReceita, $aExcelData[$iRow][5])
Next
$o_form = _IEFormGetObjByName($oIE, "Form1")
$o_btnCalculoProd = _IEFormElementGetObjByName($o_form, "btnCalculoProd")
$o_btnCalculoProd.click
_IELoadWait($oIE)

$o_form = _IEFormGetObjByName($oIE, "Form1")
$o_btnGera = _IEFormElementGetObjByName($o_form, "btnGera")
$o_btnGera.click
_IELoadWait($oIE)

$o_form = _IEFormGetObjByName($oIE, "Form1");_ctl0
$o_gareLink = _IEFormElementGetObjByName($o_form, "gareLink")
$o_gareLink.click
_IELoadWait($oIE)

The ID $o_garelink is the button that will generate a PDF. Is there a way to save it in my Desktop/Test ?

I tryied something with _IEaction and InetGet(i found the problem that each time that i create a PDF the webpage will change the name).

[vidaLL]

After much effort I finally got to make the script do almost every step, except one .... Loop

Do not know how to do each time you run the whole process, it begins to read the next line of excel. Any suggestions?