Sign in to follow this  
Followers 0
vidaLL

Help to load Txt or Xlsx information to I.E

17 posts in this topic

#1 ·  Posted (edited)

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 by vidaLL

Share this post


Link to post
Share on other sites



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.

Share this post


Link to post
Share on other sites

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 ... :thumbsup:

Share this post


Link to post
Share on other sites

Yes, it is possible. 

Share this post


Link to post
Share on other sites

#5 ·  Posted (edited)

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 by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 ?

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 .

Share this post


Link to post
Share on other sites

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

1 person likes this

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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.

#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).

Share this post


Link to post
Share on other sites

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?

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