Jump to content

Help to load Txt or Xlsx information to I.E


Go to solution Solved by water,

Recommended Posts

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
Link to comment
Share on other sites

  • Moderators

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.

Link to comment
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:

Link to comment
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 write the data into the web page.

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
Share on other sites

  • Solution

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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.

Link to comment
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 2024-07-28 - Version 1.6.3.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
OutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - Download
Outlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - Wiki
PowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - Wiki
Task Scheduler (2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki

Standard UDFs:
Excel - Example Scripts - Wiki
Word - Wiki

Tutorials:
ADO - Wiki
WebDriver - Wiki

 

Link to comment
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).

Link to comment
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?

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...