Transferring information from a row of data in an excel document into a web form

Question

I spend hours on end every week transferring information from an excel file into a webform. Right now I am copying and pasting each cell individually. What I would prefer to do is select a row in my excel file and have some sort of automation take the information in certain columns and paste them into a form in my web browser. I want this to be something I can repeat quickly and with as few keystrokes as possible.

I tried Automator on mac, but it did a terrible job, so I'm hoping there can be something better.

Copying a row in Excel places on the clipboard the cells separated by tabs. I don't have a Mac, but it should be possible to write a script that splits the text on tabs and then loops on paste-next(tab) after clicking in the browser on the first field. — harrymc, Oct 31 '14 at 08:10
I've been thinking about writing a browser plugin, but that would take a hefty amount of time for me to do, so I know very little about that stuff. Maybe one already exists...? — invot, Oct 31 '14 at 22:04
I was thinking more about AppleScript, which can [access the clipboard](http://stackoverflow.com/questions/15190868/copy-pure-text-from-clipboard-using-applescript) and [send it as keystrokes](http://apple.stackexchange.com/questions/36943/how-do-i-automate-a-key-press-in-applescript) to the browser. The tab separator will then serve to move the cursor to the next field in the form, so all you need to do is click in the first field before launching the script (which can also be bound to a hotkey). — harrymc, Nov 01 '14 at 07:09
can You give us an idea of what that form is like?? Do you have one form for multiple rows of your spreadsheet or do open the form multiple times to enter multiple records? — sgp667, Nov 01 '14 at 20:10
@sgp667: It's a very simple form with only 6 fields. The markup of the form is straightforward with only `label` and `input` tags nested within. Each field has it's own ID, which makes me feel like linking linking clipboard data to those field IDs would be a simple solution. Though automating tab hits can work too. — invot, Nov 03 '14 at 17:32
If I understand correctly, there is no security (username/password) on the form? In that case, you might be able to put together a HTTP POST string that you can run from VBA. See [this Stackoverflow question and answer](http://stackoverflow.com/questions/3911981/how-can-i-use-an-excel-formula-no-vba-to-retrieve-a-piece-of-data-within-an-ht) for a hint in that direction. If this is what you want, than it's a matter of standing on the right cell and clicking a button or hitting a key and things will fly. Let me know if this sounds promising. If you provide the form labels, I'll help you further. — agtoever, Nov 05 '14 at 13:22

score 0 · Answer 1 · edited Oct 30 '14 at 19:07

0

Have you tried using mail merge?

Load your web form (assuming you have an HTML version of the form) into MS word.
The exact steps would differ depending on which version of Word you you are using.
Load the Excel file as a data source.
Insert the fields from your Excel file into your Word form.
Now you would have all the data from the Excel file in HTML pages. These HTML pages can then be published and used where needed.

This is a high level explanation. If you need more detail send me an email and we can look at your requirements in more detail.

edited Oct 30 '14 at 19:07

t0mppa

205
1
3
8

answered Oct 29 '14 at 10:58

FlyEagle

11
1
4

Thanks for your reply Gary. I am having a hard time seeing how loading my form into Word will result in the online form from being populated. – invot Oct 30 '14 at 18:26

score 0 · Accepted Answer · edited May 23 '17 at 12:41

It should be possible to write an AppleScript script that transfers a row from Excel to the browser.

Copying a row in Excel places on the clipboard the contents of the cells, separated by tabs. The idea is for the script to copy these characters to the browser as keystrokes. All you need to do is then :

Select the Excel cells
Copy them to the clipboard all at once
Click in the first field in the browser where the copied data needs to go.
(I assume here that the fields in the browser page are in the same order as the Excel cells.)
Use the script to read the clipboard and then send the copied characters as separate keystrokes to the browser, one by one.

This will transfer the characters from the clipboard one-by-one to their fields. The Tab character will act like the Tab key to move the cursor from the current browser field to the following one.

I cannot write and test such a script because I don't have a Mac, but here are some useful articles that contain the necessary information :

Copy pure text from clipboard using AppleScript

set theData to (the clipboard as text)

Looping Through a String As If It Were a List

set my_string to (the clipboard as text)
repeat with counter_variable_name from 1 to count of my_string
    set current_character to item counter_variable_name of my_string
end repeat

Automate a key press in AppleScript

tell application "<browser-appl-name-here>" to keystroke current_character using command down

You could even use this same mechanism to send first the sequence Cmnd+C to Excel, so all you will need to do is select the cells in the spreadsheet and the script will also do the copy to the clipboard.

For more info, see Introduction to AppleScript Language Guide.

Once the script is written and tested, you could bind it to a hotkey by using one of the many available methods.

Well, I tried to get it to work, but it didn't. I think applescript will take some time for me to learn. It's a shame that there isn't something out there that will already do what I'm trying for. I feel like there's a demand for it. — invot, Nov 06 '14 at 21:52
If the script is almost done, you could ask for help in completing it on an applescript forum such as [The macosxhints Forums > AppleScript](http://hintsforums.macworld.com/forumdisplay.php?s=d5a55699550d25abd5e6022a9e4e4507&f=37). — harrymc, Nov 07 '14 at 06:57

Transferring information from a row of data in an excel document into a web form

2 Answers2