Jump to content

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


arch13
 Share

Recommended Posts

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

 

Link to comment
Share on other sites

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

My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2022-02-19 - Version 1.6.1.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 (NEW 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

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 2022-02-19 - Version 1.6.1.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 (NEW 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

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