arch13

Excel Copy/ Paste Cell by Cell to Webform (new to programming)

5 posts in this topic

Hi all,

I'll open with the caveat that I have never used AutoIT before and have very limited programming experience.

My work has tasked me with learning AutoIT to automate a data entry action for 1800 records, so I've dedicated 4 days to this to try and see how much I can learn. 

For some context, I work in research administration and we are changing our research management system from one system which is desktop based to another system which is web-based. We need to import the last 10 years of data into the new management system. Most of the data has a workaround, but for one group of data, we have to enter each record manually into a webform, which has text, radio button, and drop down fields (drop downs aren't so important as we can probably set default values for these).
I have an Excel spreadsheet with the values and each row is one record. I want to write a script where the user will manually navigate to the webpage for the new management system, press a hot key, and the Funding Sources will start adding in.

The highest priority parts of the script (that I've identified) are:

  • Let AutoIT know which web page to start from
  • Hotkey press which will run the following actions:
    • Direct the mouse to the correct menu tab - hover on the tab and "click" (button press) the '+' to add a new fund source
    • Move to first text field and copy A1 from Excel to the webform text field 1
    • "Click" to add text field 2
      • Copy B1 from Excel to the webform text field 2
      • "Click" to add to the record
    • "Click" to add text field 3
      • Copy C1 from Excel to the webform text field 3
      • Send "Enter" to add to the record
    • Select "Yes" from a "Yes/ No" Radio Button
    • Move to the next field and copy D1 from Excel to the webform text field 4
    • "Click" to save the record
    • Write "Complete" or "Error #" to the last column of the row (to see whether the entry was successful)
    • Loop this whole process for rows 2:xxx
    • Once complete, save the Excel sheet as a new file

Writing it out like this makes it seem so simple but after reading "Learning to Script with AutoIT V3", "Learning to Program using FREE tools with AutoIT", the helpfile, and searching the forums, I'm still struggling. 

So far I can:

  • Make a hotkey run a basic script
  • Open IE and tell it to go to "Google" and enter a search term in the search bar (learning step for inputting text in the online form fields)
  • "Open" and "SaveAs" in Excel
  • Copy and Paste a range to another range in Excel (I realise now this isn't useful as I thought as _Excel_RangeCopyPaste only works within Excel)
  • Create a basic loop (Do-Until) that copy/pastes a value and adds +1 to the copy column each time, and stops when the cell has no value
  • Have AutoIT read the Excel sheet to an array
  • Have AutoIT detect whether an excel sheet is an object and return a message box with the result
  • Write a value to a cell in Excel

The parts I'm particularly struggling with are:

  • Moving the mouse and "clicks"
  • Telling AutoIT which part of the source code to use (_IEFormElementGetObjByName) and when to send a "click" (and whether this is an appropriate action to take)
  • The Excel format to copy/paste with (xlsx or turn it into an array?) and sending ctrl-C, ctrl-V to get the value into the webform

If anyone has any advice on any part of this post (other resources for learning I've missed or would be helpful, other posts that are similar, sample code for any part of this that I can dissect), that would be really amazing. I've really tried to exploit all other resources to help with this but my limited understanding of programming makes it very difficult.

Cheers,

Archie

 

Share this post


Link to post
Share on other sites



Mouse moves and clicks can be very easily done by MouseMove() and MouseClick() functions.  For your project it will be more advisable to use the _IE functions. It is more reliable , but more complicated to do.

Share this post


Link to post
Share on other sites

It's not complicated, just a bit more complex ;)


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

Complex and complicated are synonym according to my humble understanding of English. Google translate tend to agree with me on this one.

Share this post


Link to post
Share on other sites

In this case the german language differentiates.

complex: It takes a lot of effort to finish the task but it is doable.
complicated: It is complex and I fear I'm not able to do it. Or: It is more complex then it needs to be.

But I might be the only one seeing it this way ;)


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

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