Jump to content
Sign in to follow this  
hanum

Excel to webform

Recommended Posts

hanum

hi ,

Can anyone help me to rectify the problem?

i'm new to auto it

i have a problem with my program. i want to add to loop for this program

my program is

#include <Excel.au3>
#include <Array.au3>
$pos = MouseGetPos()
Local $sFilePath1 = @ScriptDir & "123.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
 sleep(100)
 Send("^c")
sleep(30)
Local $fname = ClipGet()
sleep(30)
$fname = StringStripCR($fname)
$fname = StringStripWS($fname, 1)
$fname = StringStripWS($fname, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $lname = ClipGet()
sleep(30)
$lname = StringStripCR($lname)
$lname = StringStripWS($lname, 1)
$lname = StringStripWS($lname, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $email = ClipGet()
sleep(30)
$email = StringStripCR($email)
$email = StringStripWS($email, 1)
$email = StringStripWS($email, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $OtherNames = ClipGet()
sleep(30)
$OtherNames = StringStripCR($OtherNames)
$OtherNames = StringStripWS($OtherNames, 1)
$OtherNames = StringStripWS($OtherNames, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $DateofBirth = ClipGet()
sleep(30)
$DateofBirth = StringStripCR($DateofBirth)
$DateofBirth = StringStripWS($DateofBirth, 1)
$DateofBirth = StringStripWS($DateofBirth, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $username = ClipGet()
sleep(30)
$username = StringStripCR($username)
$username = StringStripWS($username, 1)
$username = StringStripWS($username, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $Telephone = ClipGet()
sleep(30)
$Telephone = StringStripCR($Telephone)
$Telephone = StringStripWS($Telephone, 1)
$Telephone = StringStripWS($Telephone, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $Address = ClipGet()
sleep(30)
$Address = StringStripCR($Address)
$Address = StringStripWS($Address, 1)
$Address = StringStripWS($Address, 2)
sleep(30)
send("{TAB}")
sleep(30)
send("^c")
sleep(30)
Local $PostCode = ClipGet()
sleep(30)
$PostCode = StringStripCR($PostCode)
$PostCode = StringStripWS($PostCode, 1)
$PostCode = StringStripWS($PostCode, 2)
sleep(30)
;----------------------------------
;COPY PROCESS COMPLETED / paste process starts here
;----------------------------------
;send("^{PGDN}")
WinActivate("1")
sleep(130)
send("{TAB}")
sleep(130)
send($fname)
sleep(130)
send("{TAB}")
sleep(130)
send($lname)
sleep(130)
send("{TAB}")
sleep(130)
send($email)
sleep(130)
send("{TAB}")
send($OtherNames)
sleep(130)
send("{TAB}")
send($DateofBirth)
sleep(130)
send("{TAB}")
send($username)
sleep(130)
send("{TAB}")
send($Telephone)
sleep(130)
send("{TAB}")
send($Address)
sleep(130)
send("{TAB}")
send($PostCode)
sleep(130)
send("{TAB}")
MouseClick("left",685,313,1)
sleep(50)
MouseClick("left",673,84,1)
Edited by hanum

Share this post


Link to post
Share on other sites
water

Welcome to AutoIt and the forum!

Can you please enclose your code in Autoit tags? There is a blue "A" icon in the editor for that.

That greatly enhances readability :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
l3ill

Hi hanum,

It looks like you are trying to read info from an excelsheet - change it and - write it to a form of some kind.

Loads of fun...

Few tips:

$fname = StringStripWS($fname, 3)
is the same as this:
$fname = StringStripWS($fname, 1)
$fname = StringStripWS($fname, 2)

And I have a snippet you can play with that sort of does what it looks like you are trying to do.

You will have to change it to your environment (it is not working code as is)

For $iCount = 1 To 25 ;position counter
        Send($iCount)
        Send("{TAB}")
        Sleep(700)
        $sCellValue = _ExcelReadCell($oExcel, $iCount, 1);reads from selected Excel file 1 pos @ a time
        If $sCellValue = "" Then ExitLoop
        Send($sCellValue)
        Sleep(700)
        Send("{SPACE}")
        Sleep(700)
        $sCellValue2 = _ExcelReadCell($oExcel, $iCount, 2);reads from selected Excel file 1 pos @ a time
        Send($sCellValue2)
        Sleep(700)
        Send("{TAB 4}")
        Sleep(700)
        Send("1")
        Sleep(700)
        Send("{TAB}")
    Next
    ;end loop

Share this post


Link to post
Share on other sites
water

Please don't mix Excel COM functions (_ExcelReadCell ...) and GUI automation (Send).

Everything you do with the Send commands can be done using the Excel UDF.

If you have questions I will be happy to assist :)


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
l3ill

Hi Water,

  this is part of a script we use everyday to fill out a PDF lieferschein  form from an excel file with barcodes

Is there a better way to transfer excelcells and strings in a loop like this?   

  I built this program 2 years ago so its probably ready for an update :ermm:

Bill 

Share this post


Link to post
Share on other sites
hanum

hi bill and Water,

thanks for your reply's.

but i'm new to auto it. i don't have programming knowledge. Can you help me of my program.

my task is

i want copy data from excel and paste already opened form.

1. open excel(given my excel format)

i want to copy data from 2 row(adarsh, raj, tel123@gmail.com.......... 500028 submit form

next submit data from 3 rd row copy data (sam, chait, ............500028 submit form

fname lname email Other Names DateofBirth username Telephone Address  Post Code adarsh raj Tel123@gmail.com ragnges 10012013 raja 9445684 ahflsf 500028 sam chait Tel123@gmail.com ronault 10012013 chaitu 5664644 ragvh 500028 satish day Tel123@gmail.com days 10012013 stats s236544 varna.mai 500028

 

please give in details of code.

 

my form fields are      

                        fname: 

                        lname:

                        email:

            Other Names:

                DateofBirth:

                  username:

                   Telephone:

                      Address:

                      Post Code

                                          submit button

Share this post


Link to post
Share on other sites
water
but i'm new to auto it. i don't have programming knowledge. Can you help me of my program.

Being new to AutoIt isn't the problem but being new to programming might be. We don't spoon feed new users here. We can only give you a few hints but you have to write the code yourself.

If you have questions we will be happy to help.

So lets start:

To read the data from Excel I suggest the Excel UDF that comes with AutoIt. Function _ExcelReadSheetToArray reads the whole worksheet into a 2D array.

The form you need to fill. Is it a GUI or a web form?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
hanum

hi water,

Thank you for your suggesting Excel UDF. i'm asking programming about to select UDF or Com Reference which one is better. you suggested Don't mix Excel COM functions (_ExcelReadCell ...) and GUI automation (Send). thats the reason i mentioned new to programming. Nobody is expecting Spoon fitting.. 

Edited by hanum

Share this post


Link to post
Share on other sites
Rogue5099

You can just use "File Save As" in Excel to output the information to .txt which would be "TABBED" between entries.

What exact format do you want to transfer the information to?

Firstname

Lastname

Email

Firstname

Lastname

Email

....

or

(This would be the SaveAs option saved as .txt)

Firstname {TAB} Lastname {TAB} Email

Firstname {TAB} Lastname {TAB} Email

....

or

any other format?

Here is a test one I created:

#include <Excel.au3>

Local $oExcel = _ExcelBookNew() ; Creating a Test Excel
$Text = ""
For $y = 1 To 9
    Switch $y
        Case 1
            $Text = "Firstname"
        Case 2
            $Text = "Lastname"
        Case 3
            $Text = "Email"
        Case 4
            $Text = "OtherName"
        Case 5
            $Text = "DateofBirth"
        Case 6
            $Text = "Username"
        Case 7
            $Text = "Telephone"
        Case 8
            $Text = "Address"
        Case 9
            $Text = "PostalCode"
    EndSwitch
    For $x = 1 To 15
        _ExcelWriteCell($oExcel, $Text & $x, $x, $y)
    Next
Next

Local $aArray = _ExcelReadSheetToArray($oExcel) ;Reading created Excel
For $y = 1 To UBound($aArray, 1) - 1
    For $x = 1 To UBound($aArray, 2) - 1
        $aArray[$y][$x] = StringStripCR($aArray[$y][$x])
        $aArray[$y][$x] = StringStripWS($aArray[$y][$x], 1)
        $aArray[$y][$x] = StringStripWS($aArray[$y][$x], 2)
    Next
Next

_ExcelBookClose($oExcel)

$NewFile = FileOpen(@ScriptDir & "\Test.txt", 1) ;Creating Text document
For $y = 1 To UBound($aArray, 1) - 1
    For $x = 1 To UBound($aArray, 2) - 1
        FileWrite($NewFile, $aArray[$y][$x] & @CRLF) ;Writing read Excel to text document
    Next
    FileWrite($NewFile, @CRLF)
Next
FileClose($NewFile)
ShellExecute(@ScriptDir & "\Test.txt")
Edited by Rogue5099

Share this post


Link to post
Share on other sites
water

Reading the Excel file is easy, what we still need to know  is the type of application you want to write the data to.

#include <Excel.au3>
#include <Array.au3>
Global $sFilePath = @ScriptDir & "\123.xlsx"
Global $oExcel = _ExcelBookOpen($sFilePath)
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
$aCells = _ExcelReadSheetToArray($oExcel)
For $i = 1 to $aCell[0][0]
    ; fill the form - depending on the application (browser or GUI)
Next

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
l3ill

Hi Water,
 
  Can I get a preview of what your going to use to get the info from array to....say a PDF form. 
The suspense is getting to me :-)
 
Here is one for example: sanirecord.pdf  (should be downloaded to use. not in browser)
 
Pretty straight forward: Loop is 1-4
Batch # top left of each record 12345.....
Serial # is 10 digits (from excel sheet)
Make is always the same word (default) or whatever
Model is always either H&C / Reg (from excel sheet)
Location is always the same word (default) or whatever
Last Date is NowDate
 
Does this 4 times (1 page) - prints - closes
and opens again for the the next 4 5-8) until EOF
 
I wouldn't call this a hijack, just a detour until the original poster gets back  o:)
 
Here is what I have been using:
 

Func _sanirecord() ;Sanitation Record



Local $sanirecord = (@ScriptDir & "\sanirecord.pdf")

BlockInput(1)

;Splash================================================================>>>

$splash = @ScriptDir & "\sanirecord.jpg"

$hwnd = GUICreate("", 600, 200, -1, -1, $WS_POPUP)

GUICtrlCreatePic($splash, 0, 0, 600, 200)

DllCall("user32.dll", "int", "AnimateWindow", "hwnd", $hwnd, "int", 500, "long", 0x00080000);fade-in

GUISetState()

;Sleep(800);View picture

DllCall("user32.dll", "int", "AnimateWindow", "hwnd", $hwnd, "int", 500, "long", 0x00090000);fade-out

;Splash================================================================>>>

BlockInput(0)



#region start code that opens the excel file with serial numbers//saved from barcode scanner====================>>>

If WinExists("Microsoft Excel") Then

MsgBox(16, "Open Workbook Identified", "Please close any open Excel Workbooks" & @CRLF & "Function will continue after closing", 2)

EndIf

While WinExists("Microsoft Excel")

Sleep(100)

WEnd

$message = "Select Workbook"

$var = FileOpenDialog($message, @DesktopDir & "\", "Excel (*.xls;*.xlsx)", 1)

If @error Then

MsgBox(4096, "", "No File Selected - Exiting")

Return 0

Else

$var = StringReplace($var, "|", @CRLF)

EndIf

$oExcel = _ExcelBookOpen($var)

Sleep(100)

WinSetState("Microsoft Excel", "", @SW_MINIMIZE)

Sleep(100)

#endregion start code that opens the excel file with serial numbers//saved from barcode scanner====================>>>



$aArray = _ExcelReadSheetToArray($oExcel) ;Using Default Parameters

;_ArrayDisplay($aArray, "Array using Default Parameters")

$rows = UBound($aArray)

ConsoleWrite("$rows=" & $rows & @CRLF)

$stop = $rows - 1

For $c = 1 To $stop Step 4 ;===>>For/Next - Repeater ...After form filler 4 times goes to next set

BlockInput(1)

;Open sanirecord.pdf / maximize / zoom to fit page ===================>>>

ShellExecute('AcroRd32.exe', $sanirecord)

WinWaitActive("sanirecord.pdf")

WinSetState("sanirecord.pdf", "", @SW_MAXIMIZE)

Sleep(300)

Send("^0")

;Open sanirecord.pdf / maximize / zoom to fit page ===================>>>

;Begin - filling in form the 4 forms on sanirecord.pdf ==========================>>>

Sleep(4500)

Send("+{TAB}")

Sleep(700)

For $iCount = $c To $c + 4 - 1 ;=====>>> Repeates the form filler 4 times



$sCellValue1 = _ExcelReadCell($oExcel, $iCount, 3) ;Sends Position Counter integer

Send($sCellValue1)

Sleep(700)

Send("{TAB}")

Sleep(700)

$sCellValue = _ExcelReadCell($oExcel, $iCount, 1) ;Sends Cooler #

If $sCellValue = "" Then ExitLoop

ConsoleWrite("$c=" & $c & @CRLF)

Send($sCellValue)

Sleep(700)

Send("{TAB}")

Sleep(700)

Send("Oasis")

Sleep(700)

Send("{TAB}")

Sleep(700)

$sCellValue2 = _ExcelReadCell($oExcel, $iCount, 2) ;Sends Reg or H&C

Send($sCellValue2)

Sleep(700)

Send("{TAB}")

Sleep(700)

Send("Grunstadt")

Sleep(300)

Send("{TAB}")

Sleep(700)

Send(_NowDate())

Sleep(700)

Send("{TAB}")

Sleep(700)

Next

;End - filling in form the 4 forms on sanirecord.pdf ==========================>>>

;Print PDF =============================================================>>>



WinActivate("sanirecord.pdf")

WinWaitActive("sanirecord.pdf")

Sleep(300)

Send("^p")

Sleep(300)

WinWait("Print", "", 5)

WinWaitActive("Print")

Send("{ENTER}") ;cancels print for testing purposes============>>>

;ControlClick("Print", "Rotate and Center", "[CLASS:Button; INSTANCE:17]")

Sleep(500)

WinActivate("sanirecord.pdf")

WinWaitActive("sanirecord.pdf")

Sleep(300)

WinKill("sanirecord.pdf")

Sleep(1000)



;Print PDF ==============================================================>>>



If $sCellValue = "" Then

ExitLoop

EndIf

Next

_ExcelBookClose($oExcel)

BlockInput(0)

EndFunc ;==>_sanirecord

Example Excel File
 

Edited by billo

Share this post


Link to post
Share on other sites
water

Does the script you posted work for you?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

To automate an application I prefer to use an API/COM and not Send or Control* commands.

Means: IE and FF UDF for Internet Explorer and FireFox.

I've never used AutoIt to fill in PDF forms. But I know there is an API available.

>Here is a post that uses FDF to fill a PDF form. Maybe that's what you are looking for?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Starting point to get more information about FDF.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

And what would you use for GUI?

Control* functions in absence of an API.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
l3ill

Thanks for the tip! looks interesting.

Starting point to get more information about FDF.

 

So I wasn't too far off with my assumption that the send and control send was the correct way to fill out the PDF's.

I was hoping to learn a different (more stable) way to fill out these forms.

Thanks for your time Water  ;)

Edited by billo

Share this post


Link to post
Share on other sites
water

Your way of filling the PDF looks reliable to me. Locking the screen should block any problems caused by the user.

Maybe the FDF approach gives better performance and less work in case the form changes.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

Share this post


Link to post
Share on other sites
water

Double post - &@! WLAN in the train :(

Edited by water

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (2018-06-01 - Version 1.4.9.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2018-09-01 - Version 1.3.4.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example Scripts
PowerPoint (2017-06-06 - Version 0.0.5.0) - Download - General Help & Support
Excel - Example Scripts - Wiki
Word - Wiki
 
Tutorials:

ADO - Wiki

 

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  

×