Viki

Copy from excel row (one by one) and paste into another application

16 posts in this topic

This is my first time here so please dont bombard me that what a silly question I am asking!!

I have 500 rows (A1:A500) in a spreadsheet and I just want to copy one by one row and then paste into another application and then press enter, loop should repeat this until finishes all 500 rows.

I have looked at clipget(), clip(put() but dont know how to select next row in next turn. I also looked at Array to store but again no luck. Can some guide me please..

Share this post


Link to post
Share on other sites



Welcome to AutoIt and the forum!

To process Excel workbooks I suggest you have a look at the Excel UDF that comes with AutoIt. Function _Excel_RangeRead should do what you want.
How to paste the read Excel cells to your application depends on the type of application (browser, GUI ...).

If you can provide more information we might be able to provide a solution ;)


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

Hi Water, many thank for your quick reply.

I have see those UDF and looked at the examples as well which is copying the data from one cell, so how can I copy on cell go to (for example teamviewer)  paste the value into the partnerid field and press enter and then again go to excel and repeat the process.

 

Regards,

Vik

Share this post


Link to post
Share on other sites

#4 ·  Posted (edited)

I would use _Excel_RangeRead to read the whole worksheet into an array in a single go. Reading cell by cell takes much more time.
Then loop through the array and send each "cell" to your application using ControlSend and ControlClick.

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

I have found example where I can store the values in the Arrays but could not find a way to paste it into the application however, I have written something like this, which works fine and paste to the Notepad one by one but now I have another issue, I could not find a way to select the input field in my application, here for example lets think about teamviewer and if I want to select the password field then what will be the bast way, I tried the windows info tool but could not figure out what command should I use and what unique identifier should I choose and how to use it. It will be great if can please help me in this:

#include <Excel.au3>
#include <MsgBoxConstants.au3>

Local $oExcel = _Excel_Open()
If @error Then Exit MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error creating the Excel application object." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
Local $oWorkbook = _Excel_BookOpen($oExcel, @ScriptDir & "\TESting.xlsx")
If @error Then
    MsgBox($MB_SYSTEMMODAL, "Excel UDF: _Excel_RangeRead Example", "Error opening workbook '" & @ScriptDir & "\TESting.xlsx'." & @CRLF & "@error = " & @error & ", @extended = " & @extended)
    _Excel_Close($oExcel)
    Exit
EndIf
Local $sResult = _Excel_RangeRead($oWorkbook, Default, "A1")

Do
    Local $sData = ClipGet()
    ClipPut($sResult)
    $sData = ClipGet().
    WinActivate("Untitled - Notepad")
    Send("^v")
    Send ("{ENTER}")
    $sResult = $sResult + 1

Until $sResult = 123465

 

Share this post


Link to post
Share on other sites

Use the ID desplayed on the Control tab of the Window Info tool and call function ControlSend with this information.


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

Thanks, the application I wanted to copy to is internet based so can you please point me to the right direction if I have to automate IE or firefox, I do I have to download before I start working with any of the browser. I tried this (t start with):

#include <ff.au3>

_FFStart("www.google.co.uk")

but got the error

Line 12  (File "C:\Users\va012278\Desktop\ff.au3"):

#include <ff.au3>

Error: #include depth exceeded.  Make sure there are no recursive includes.

 

 

 

Share this post


Link to post
Share on other sites

Seems there is a recursive include in your script. The file ff.au3 which you include in your script itself includes ff.au3 and this results in an endless loop.


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

Hi,

Can you please help, what I am doing wrong here, I am trying to read the A1:A10 range into Array and then loop through them and show on Msgbox one by one..

Local $oExcel = _Excel_Open()
$oExcel = _Excel_BookOpen("C:\Users\Desktop\TESting.xlsx")
$aArray = _Excel_RangeRead($oExcel, Default, "A1:A10")

    For $vElement In $aArray
        MsgBox($MB_SYSTEMMODAL,"Test",$vElement)
        $vElement+1
    Next

 

Share this post


Link to post
Share on other sites
Local $oExcel = _Excel_Open()
$oExcel = _Excel_BookOpen("C:\Users\Desktop\TESting.xlsx")
$aArray = _Excel_RangeRead($oExcel, Default, "A1:A10")
For $i = 0 to UBound($aArray) - 1
    MsgBox($MB_SYSTEMMODAL, "Test", $aArray[$i])
Next

 


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

Hi

I am bit struggling when my webapp opens a popup to add a new record. It is fine if I put the sleep to let window open that popup after I click on 'Add' but is there any way I can dynamically check if that popup has opened because popup may take longer or shorter to load. I tried to get the reference (through window info utility) but no luck, it shown something as

<div class="ui-dialog-titlebar ui-widget-header ui-corner-all ui-helper-clearfix">
<span id="ui-id-9" class="ui-dialog-title">Add Street</span>

I could not find any online help as well for this, can you please give me a hand...

Share this post


Link to post
Share on other sites

Unfortunately I have never used the Firefox UDF so I can't help you with this problem.
Hopefully some experienced Firefox coder chimes in :)


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

Sorry if I did not make it clear, I was using _IE functions for this not firefox...if that helps..

 

Regards,

Viki

Share this post


Link to post
Share on other sites

Ah .. but in post #7 you were talking about FF.

My first try would be to access the element by using _IEGetObjById.


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

I have managed to do with _IE but now I have another obstacle, I want to loop through all the directories within a directory but want to see folder starting with "my" I have something like this

Local $aFileList = _FileListToArray("\\server\c$\inet\root", "*")

For $i = 0 to 10
    If StringInStr($i,"my") Then

    MsgBox($MB_SYSTEMMODAL, "", $aFileList[$i])

    EndIf

Next

please help to only retrieve folder starting with 'my'

 

Regards,

Viki

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

  • Similar Content

    • JSmith312
      By JSmith312
      Hello Everyone! 
      I'm attempting to create a checklist app that performs other functions. For performance, I would like to check the box, then have the button to the right enabled. After that button is pressed, the second checkbox is enabled, and ready to be checked, allowing the second button to be pressed. After the second button is pressed, the completion/exit button is enabled/shown. I have my code that currently creates the GUI, creates the checkbox, but when you check it it enables the button and the next checkbox. Can't seem to figure out a Case for GUISetOnEvent. Any assistance would be appreciated!
      #include <GUIConstantsEx.au3> #include <WindowsConstants.au3> #include <StaticConstants.au3> #include <EditConstants.au3> #include <MsgBoxConstants.au3> ; Below is the GUI interface _DeploymentProcess() Func _DeploymentProcess() $aStep1 = GUICreate("Deployment Process", 475, 345, 500, 175) $CBcStep1 = GUICtrlCreateCheckbox("1. Step 01.", 15, 25, 300, 25) $BTNS1 = GUICtrlCreateButton("Email 01", 365, 25, 90, 20) $CBcStep2 = GUICtrlCreateCheckbox("2. Step 02.", 15, 50, 300, 25) $BTNS2 = GUICtrlCreateButton("Email 02", 365, 50, 90, 20) GUICtrlSetState($CBcStep2, $GUI_DISABLE) GUICtrlSetState($BTNS1, $GUI_DISABLE) $CBcStep3 = GUICtrlCreateButton("Hurray! You're Complete!", 85, 276, 300, 60) GUICtrlSetState($CBcStep3, $GUI_SHOW) ; Below are the button and checkbox enables/disables. GUISetState(@SW_SHOW) While 1 Switch GUIGetMsg() Case $GUI_EVENT_CLOSE GUIDelete($aStep1) Return ; Step 1 Case $CBcStep1 If GUICtrlRead($CBcStep1) = $GUI_CHECKED Then GUICtrlSetState($BTNS1, $GUI_ENABLE) GUICtrlSetState($CBcStep2, $GUI_ENABLE) Else GUICtrlSetState($BTNS1, $GUI_DISABLE) GUICtrlSetState($CBcStep2, $GUI_DISABLE) EndIf ; Step 2 Case $CBcStep2 If GUICtrlRead($CBcStep2) = $GUI_CHECKED Then GUICtrlSetState($BTNS2, $GUI_ENABLE) GUICtrlSetState($CBcStep3, $GUI_ENABLE) Else GUICtrlSetState($BTNS2, $GUI_DISABLE) GUICtrlSetState($CBcStep3, $GUI_DISABLE) EndIf Case $CBcStep3 If GUICtrlRead($CBcStep2) = $GUI_CHECKED Then GUICtrlSetState($BTNS10, $GUI_ENABLE) GUICtrlSetState($CBcStep3, $GUI_SHOW) Else GUICtrlSetState($CBcStep3, $GUI_EVENT_CLOSE) EndIf Case $GUI_EVENT_CLOSE, $CBcStep3 #comments-end Exit EndSwitch WEnd EndFunc  
    • RoundChecker
      By RoundChecker
      Hi everyone,

      Is there a way to determine whether the script that is running, is already running, without using;
       
      If WinExists ?

      Or is there a way to use "If WinExists" to determine the script itself?

      The reason I am asking this is because if I want someone to run the .Exe I have and they rename it to whatever they want, how could I then determine in the script to check if there's already an .Exe open, or the script itself is already open?

      Should I use Class, or how do I achieve this?

      Thanks in advance.
    • svenjatzu
      By svenjatzu
      Id like to build a little helpertool for my boss in office but i dont know how to sart it.
      thats no complicated task. im working for an insurancecenter and have to search the customers in different orders and different tags.
      due to my boss is old and not really commen with pc he saves the customers without system.
      eg, customer andreas statham got an insurenca for his car then i got to search for
      andreas statham car, andreas car statham, car andreas statham, statham andreas car, statham car andreas, car statham andreass etc to find all the files from this customer in the harddrive.
      some customers also are fmiliarnamed in one file like andreas statam and monika rog are married an both got a carinsurance then i got to earch for
      andreas statham monika rog car, andreas statham monika car rog,.... this can take real long to find the right customer if i do it from hand.
      how can i realise this someone in here got an excamlple script or has any sugestion?
       
       
    • X_xkijux_x
      By X_xkijux_x
      Ok so im using this program. clarify for school. I would like to have a program that takes every word i type in microsoft word and searches on it in claryfi. is this possible. I would like to have the program not stopping me from typing more after that word. Like when i type a word it auto search it on clarify without making me stop typing and if i want to change i can do that and if im fine that word i can just keep typing and it will search after the next word. 
    • Miliardsto
      By Miliardsto
      Is this possible to make program works like in diagram? There is so much encryption methods and UDFs are they give security? If the $Address will be crypted with some encryption algorithm could be possible to get the value of $Address in easy way? Of course We know its easy too look into autoit code and get value of variable and so We dont want to anyone see $Address value - there are functions Could func1.au3, func2.au3.. be for example func1.php or other type of file? As we know .php files are unnable to see. For example func1 would be read by FileRead() and then Execute() Is this generally possible to do?
      Will it give secure?
      What do you think about? Maybe there is something wrong in this concept or missed.