Jump to content

Sky Slate Blueberry Blackcurrant Watermelon Strawberry Orange Banana Apple Emerald Chocolate
Photo

Simple copy & paste script between Excel & web form fires too quickly (maybe?)


Best Answer Jackie Sztuk _Blackholyman , 30 April 2013 - 07:12 PM

Go to the full post »


  • Please log in to reply
8 replies to this topic
agoat
  • Members
  • 4 posts
  • Last active: May 03 2013 05:45 PM
  • Joined: 30 Apr 2013

So I've only used AHK a little bit before and for very simple tasks. Right now I'm trying to automate something that's probably very easy for you guys and more difficult for me:

 

I've got a bunch of data in Excel spreadsheets and I'm trying to automate posting them to a web form. I don't have access to the guts of the site so the most I can probably do is use AHK to speed it along.

 

I tried making a script that fills in blanks, tabs to the next relevant one, pastes its clipboard contents, alt-tabs to Excel, moves right a cell, copies that, alt-tabs back and tabs down to the next field, pastes it, and so on, then finally tabs to the submit button and hits enter. If I can get that working, then I could just add some extra bits to make it go down to the next field and repeat.

 

It looks about like this: 

#NoEnv  ; Recommended for performance and compatibility with future AutoHotkey releases.
; #Warn  ; Enable warnings to assist with detecting common errors.
SendMode Input  ; Recommended for new scripts due to its superior speed and reliability.
SetWorkingDir %A_ScriptDir%  ; Ensures a consistent starting directory.
!space:: Send admin{tab}{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v!{tab}{right}^c!{tab}{tab}^v{tab}{tab}{tab}{enter}

I'm sure there's some more elegant way to do this, but I don't have a lot of time to learn more complex scripting and the job is small enough that having someone just execute the script a couple hundred times is probably going to be easier for me than making something fully automated that will keep going until the job is done. I'm just trying to avoid spending a week on mind-numbing data-entry.

 

The problem is that when I set it off it does the first couple parts just fine but then stops. I'm assuming the script is activating too quickly for the alt-tabbing between windows and the arrowing around through cells for everything to be where it needs to be and for the clipboard functions to work. I tried using sleep but I'm not sure I was using it correctly. 

 

How can I make this work? Thanks.



robert_ilbrink
  • Members
  • 561 posts
  • Last active: Mar 10 2015 08:36 PM
  • Joined: 05 May 2012

Wow, All in one line... Remember that ^c (Copy) requires some time and is normally followed by a line with ClipWait,2 or sleep, 300 (or whatever you need to wait), Paste can be faster, but takes time as well. and instead of {Tab}{Tab}{Tab} you could write {Tab 3}. My suggestion is to split this into multiple lines and to at least put sleep OR ClipWait after send, ^c and maybe in general set a keydelay to give the system time to catch up with your flood of instructions.



agoat
  • Members
  • 4 posts
  • Last active: May 03 2013 05:45 PM
  • Joined: 30 Apr 2013

Thanks for the response. I had another version with line breaks and sleep commands but I must have been formatting them incorrectly because it was coming up with an error about an unexpected curly bracket very early on, in one of the tabs. It looked like this, pretty much.

!space:: Send admin
Sleep, 100 {tab}
Sleep, 100 {tab}
Sleep, 100 {tab}
Sleep, 100 ^v

and so on. I've had trouble finding comparable examples since the documentation just shows the basic command in isolation and other peoples' scripts are much more complicated.

 

Am I formatting that correctly? Also, do line breaks have any effect beyond improving human readability? Thanks again.



Jackie Sztuk _Blackholyman
  • Spam Officer
  • 3757 posts
  • Last active: Apr 03 2016 08:47 PM
  • Joined: 28 Feb 2012
✓  Best Answer
this may or may not work but try it out
!space:: 
Send admin{tab 3}
sleep 100
Send ^v
sleep 100
send !{tab}{right}
Clipboard =
send ^c
ClipWait, 0.5
Send !{tab}{tab}^v
sleep 100
send !{tab}{right}
Clipboard =
send ^c
ClipWait, 0.5
Send !{tab}{tab}^v
sleep 100
send !{tab}{right}
Clipboard =
send ^c
ClipWait, 0.5
Send !{tab}{tab}{tab}^v
sleep 100
send !{tab}{right}
Clipboard =
send ^c
ClipWait, 0.5
Send !{tab}{tab}^v
sleep 100
send !{tab}{right}
Clipboard =
send ^c
ClipWait, 0.5
Loop 9
{
   Send !{tab}{tab}^v
   sleep 100
   send !{tab}{right}
   Clipboard =
   send ^c
   ClipWait, 0.5
}
Send !{tab}{tab}^v{tab 3}{enter}
I just modifyed your one line script...

and see how there is nowthing on the same line as the hotkey!

also "alt tab" i'd say may not be the best way it is better to use WinActivate to be sure the right window is activ when you need it to be.

Hope this helps

Note: when working with Ms Excel its more reliable to use the COM (Component Object Model) http://www.autohotke...e-4#entry381256
Helping%20you%20learn%20autohotkey.jpg?d

[AHK] Version. 1.1+ [CLOUD] DropBox ; Copy [WEBSITE] Blog ; About

agoat
  • Members
  • 4 posts
  • Last active: May 03 2013 05:45 PM
  • Joined: 30 Apr 2013

It's getting there. I found that one mistake I had is that AHK doesn't seem to respond to {right} and only likes {Right}. 

 

Also seems to need a lot more sleeps since it's pasting multiple cells into single fields, but I got one of them fixed so I can probably figure out the rest. I'll welcome any more advice if people have it but for now I'm gonna try to get this sucker working.

 

Thanks everyone.



CloudSurfer777
  • Members
  • 42 posts
  • Last active: Sep 04 2013 01:55 AM
  • Joined: 02 Oct 2012

agoat,

 

SetKeyDelay, 100     

 

Instead of using multiple Sleeps, put this at the beginning of your script, this will put a sleep 100 between each sent keystroke whether it's  ^c or ^v or {Tab}.  

 

--

Luke



agoat
  • Members
  • 4 posts
  • Last active: May 03 2013 05:45 PM
  • Joined: 30 Apr 2013

agoat,

 

SetKeyDelay, 100     

 

Instead of using multiple Sleeps, put this at the beginning of your script, this will put a sleep 100 between each sent keystroke whether it's  ^c or ^v or {Tab}.  

 

--

Luke

 

That worked perfectly. Man this is super messy overall but everything's doing what it needs to do, and it's reduced a couple weeks of data entry to a couple hours of hammering hotkeys. (The site I'm working with is less than reliable so I'm iffy about automating the entire process).

 

Thanks everyone!



Gopiraj C
  • Members
  • 1 posts
  • Last active: Aug 08 2014 01:19 PM
  • Joined: 08 Aug 2014

Hi Guys

 

My work based on copy the tab fields from excel and paste in to online form, excel contains 22 fields and I want to copy the every excel tab field and paste in this online form's fields, so kindly guide me, Is there any loop action is there?



danonepereira
  • New members
  • 2 posts
  • Last active: Aug 28 2015 05:40 PM
  • Joined: 28 Aug 2015
  Can someone help me with script to automate copying from this Excel table below:   LabID Date ARShipToID LabName Address1 Address2 City State Zip Alias ContactName ContactTitle ContactPhone ContactExt ContactFax StartMonth Comments InActive Product InstrumentType

InstrumentID

 

 

 

Into this web form below:

 

 

Lab ID

Lab name
Email
Email Confirmed
Not Set True False 
First name
Last name
Title
Start Date
Phone number
Extension
Fax number
Password
Confirm Password
Address
Line 1 Line 2 City State Zip
Is Locked?
Instruments
  Type Serial Number Add   Choose...Medonic M-SeriesMedonic CA620Swelab AlfaSwelab Ac920Act-DiffAcT 10MD IIMD16OnyxQuintusCell-Dyn 1400Cell-Dyn 1600Cell-Dyn 1700Cell-Dyn 1800T-SeriesMINDRAYMedonic CA530ABX MicrosAcT diff IIAcT SeriesMaxmMD SeriesS-Plus Series Remove
Role
 Admin Laboratory TechSupport 
Comments