Challenge: Copy cells from Excel, and insert the data into fields on a web page

[acantor]

A few days ago, someone asked how to accomplish a task with Macro Express that involved Excel and a website. The scenario was a little unclear, so in separate posts, Cory and I encouraged the OP to provide details. So far, the OP hasn’t responded. (It’s not too late! )

 

Because the problem looked interesting, I decided to invent my own details, and try to script a solution:

 

An Excel worksheet stores personal data in Row 1: an honorific in A1, first name in B1, middle initial in C1, surname in D1, and so on:

 

 

The challenge: Write a Macro Express script to copy values from ten cells, A1 to J1, and insert the values into the ten adjacent fields on the RoboForm web page:

 

 

The value from A1 goes in the “Title” field, the value from B1 goes in the “First Name” field, etc.

 

Here’s the URL to the RoboForm web page:

 

RoboForm Test - Fill in the fields

 

I'm not sure what RoboForm is, but I suggest your solution use fake data -- not your personal information -- and that you don't complete the form. Just use the page for test purposes. Feel free to use any other web page you find that consists of a series of form fields.
 

My first attempt to script a solution was horribly unreliable. Then I tried an entirely different method, which I eventually got to work, although it was clunky and failed occasionally. My latest macro appears to be reliable, but I’m not sure it’s the best solution.

 

How would you script this macro? I’ll post my solution in a couple of days.

[Cory]

Generate the POST form data and send it directly to the web server using an HTTP POST request. No browser required and about 100 submissions per minute. 😁

Just kidding. Sorry could not resist. 

 

Bust seriously now folks...

First thing I'd do is to export the table to a tab delimited file and use the ASCII Text File Process command. The results could also be saved to a test file, and I'd create a data link query in Excel to that file that I'd then perform a lookup for in the original table. Therefore there will be no MEP interaction with Excel and I can use the workbook to monitor progress. 

Then I'd open a new web page each time and tab to the fields and fill in the fields. Then I'd hit the home key and tab through all the fields and copy to compare that all the values match.

Then click the submit button and wait for confirmation and log the result to file. Ctrl+A is useful.

Put this all in a retry loop inside the main loop. 

I'd also run this on a VM or mule machine with no other apps to reduce disruptions. 

Sometimes I make the delays variables and use a factor. Start with a large factor, then reduce is iteratively until the reliable limit is reached. 

Some other things I'm not recalling now as it's been a long time. Lots of window activations. 

[acantor]

Quote

First thing I'd do is to export the table to a tab delimited file and use the ASCII Text File Process command.

 

Interesting!

 

There would be advantages to adding a tab-delimited file into the mix, especially if there is a lot of data to transfer. But in this case, there are only ten cells, the cells are contiguous, and the fields the data gets transferred into are also contiguous.

 

Using an intermediary file might help reduce the number of interactions between Macro Express and Excel, and between Macro Express and the web page when transferring data between two windows. I say "might help" because there may be ways to minimize the number of interactions without introducing an intermediary file.  I say "there may be ways" because I'm working on it!

[Cory]

In larger batches and processes with MEP I would break it down into multiple processes. So instead of doing steps 1, 2, and 3 for each record, I'd do step 1 for all records, verify the integrity of the data, then move on to the next steps. Often I'd learn that my idea was flawed because of an unknown and it saves me time often. 

 

If my client insists on working in Excel, I typically use F5 to select ranges. Then I process the range like a tab file in MEP. And I keep the data in MEP until it's all done then assemble it as tab data in a variable, use F5, and paste it in that way. It limits the switching back and forth to one cycle. 

[acantor]

Cory, I just changed my solution based on two ideas I borrowed from you.

 

1. Use F5 in Excel. I previously used Ctrl+G to do the same thing, but F5 might be more reliable. Sometimes, although rarely, sending modifier keys via Macro Express causes the modifier key to get "stuck" in the down position. Using F5 should minimize the problem.

 

2. Use F5 to select a range of cells to copy. I didn't know about selecting  ranges via the Go To dialog! My previous version outputted Ctrl+G to go to cell A1. Then, the macro repeated Shift+right arrow nine times to select the range. Doing both operations at once should make the macro more reliable... faster too, if that's important.

 

Although my solution expects the RoboForm to be open, it does locate the first field on the page by searching for its label and tabbing.

 

One of my first efforts stored the ten values in an array. But the solution turned out to be a bit ponderous. In time, I came to realize that after copying a range of cells to the clipboard, there was, by default, a carriage return after each of the ten values. My macro replaces the carriage returns with tabs. When the macro outputs the ten values, it does so as one operation. The Text Type instruction outputs each value followed immediately by a TAB. The Tab advances to the next field on the web page.

 

// Try to cancel whatever process may be started in Excel, e.g., an open dialog box
Text Type (Simulate Keystrokes): <ESC>
 
// Select Cells A1 through J1
Text Type (Simulate Keystrokes): <F5> // Shortcut to "Go To" dialog, which we use to select A1 through J1
Delay: 200 milliseconds
Text Type (Simulate Keystrokes): A1:J1<ENTER>
 
// Copy the ten cells to the Clipboard, and assign to %Clip%
Text Type (Simulate Keystrokes): <CONTROL>c
Delay: 500 milliseconds // This short delay gives time for clipboard to populate
Variable Set String %Clip% from the clipboard contents
 
// By default, each cell ends with a Carriage Return character
// Replace each Carriage Return character with a Tab character
Variable Set to ASCII Char 13 to %CR%
Variable Set to ASCII Char 9 to %Tab%
Variable Modify String: Replace "%CR%" in %Clip% with "%Tab%"
 
// Switch to the RoboForm Tutorial, which must be open
     (https://www.roboform.com/filling-test-all-fields)
Window Activate: RoboForm Tutorials - Form Filler
 
// Give focus to "Title" field by searching and tabbing
Text Type (Simulate Keystrokes): <CONTROL>f // Search on page
Delay: 50 milliseconds
Text Type (Simulate Keystrokes): Title
Text Type (Simulate Keystrokes): <ESC> // Cancel search
Delay: 200 milliseconds
Text Type (Simulate Keystrokes): <TAB> // Tab to the adjacent "Title" field
 
// Output the ten values. Each value ends with Tab, which advances to the next field on the web page
Text Type (Simulate Keystrokes): %Clip%

<COMMENT Value="Try to cancel whatever process may be started in Excel, e.g., an open dialog box"/>
<TEXT TYPE Action="0" Text="<ESC>"/>
<COMMENT/>
<COMMENT Value="Select Cells A1 through J1"/>
<TEXT TYPE Action="0" Text="<F5>" _COMMENT="Shortcut to \"Go To\" dialog, which we use to select A1 through J1"/>
<DELAY Flags="\x02" Time="200"/>
<TEXT TYPE Action="0" Text="A1:J1<ENTER>"/>
<COMMENT/>
<COMMENT Value="Copy the ten cells to the Clipboard, and assign to %Clip%"/>
<TEXT TYPE Action="0" Text="<CONTROL>c"/>
<DELAY Flags="\x02" Time="500" _COMMENT="This short delay gives time for clipboard to populate"/>
<VARIABLE SET STRING Option="\x02" Destination="%Clip%" NoEmbeddedVars="FALSE"/>
<COMMENT/>
<COMMENT Value="By default, each cell ends with a Carriage Return character"/>
<COMMENT Value="Replace each Carriage Return character with a Tab character"/>
<VARIABLE SET TO ASCII CHAR Value="13" Destination="%CR%"/>
<VARIABLE SET TO ASCII CHAR Value="9" Destination="%Tab%"/>
<VARIABLE MODIFY STRING Option="\x0F" Destination="%Clip%" ToReplace="%CR%" ReplaceWith="%Tab%" All="TRUE" IgnoreCase="FALSE" NoEmbeddedVars="FALSE"/>
<COMMENT/>
<COMMENT Value="Switch to the RoboForm Tutorial, which must be open\r\n(https://www.roboform.com/filling-test-all-fields)"/>
<WINDOW ACTIVATE Title="RoboForm Tutorials - Form Filler" Exact_Match="FALSE" Wildcards="FALSE" _IGNORE="0x0006"/>
<COMMENT/>
<COMMENT Value="Give focus to \"Title\" field by searching and tabbing"/>
<TEXT TYPE Action="0" Text="<CONTROL>f" _COMMENT="Search on page"/>
<DELAY Flags="\x02" Time="50"/>
<TEXT TYPE Action="0" Text="Title"/>
<TEXT TYPE Action="0" Text="<ESC>" _COMMENT="Cancel search"/>
<DELAY Flags="\x02" Time="200"/>
<TEXT TYPE Action="0" Text="<TAB>" _COMMENT="Tab to the adjacent \"Title\" field"/>
<COMMENT/>
<COMMENT Value="Output the ten values. Each value ends with Tab, which advances to the next field on the web page"/>
<TEXT TYPE Action="0" Text="%Clip%"/>

 

[Cory]

Also using names is handy. See your name manager in the Formula tab. Doing A1:B2 is in most cases stupid. If you're referencing it , it's significant and shoudl have a name. Also much nicer in formulas. Besides many obvious reasons, named regions can change shape and you don't need to update your macro.

Aside: If you haven't learned to use tables [Insert > Table] and named resources in Excel for tabular data, stop now, spend an hour and learn. You will thank me later. And your forehead will be red from constantly slapping yourself there and cursing yourself "Why didn't I learn this sooner!"

Back to my thing is that a table will have a name and the headers are the column names and can be used in formulas and such, but most importantly they change shape, usually having more rows. But with F5 if you reference the table "Clients" you will get all the clients no regardless of how to row or column count changes. And if you want part of it like Clients-Name then you can get just that column, and when someone inserts a new column left of it, you don't miss. 

Aside: Also a reason for using named references in formulas. Not to mention something like a VLookup is much easier to understand in your formulas. You don't have to remember that the third column is the name, you just see "name" and know what that part of your formula is doing. 

Aside: Stop using Vlookup. Xlookup was designed as it's replacement and is much easier and more capable and easy to learn. It's like using the Index/Match function combination, but all wrapped up in the ideal single function.