Can a spreadsheet fill out a form?

[Rick Altman]

I would like to accomplish something like the following. I want a macro to:

• Take data in CSV or Excel format.
• And sequentially stuff each column of data into an online form.

I know I could do this with brute force: Tab to a column, copy, jump to the forum, paste, repeat. But I would rather be smarter about this, like store each column in a variable and then rapid-fire paste the variables. I would rather be smart like that, but the problem is that I am not smart like that. So any help would be greatly appreciated.

 

V4.7.1.1.

Windows 11

[acantor]

There are many ways to accomplish this. Some methods are probably "smarter" than others, and it can be satisfying to write a clever macro. But if you can find a way to get the job done, any way at all, that's a good thing!

 

If you can figure out sequences of keys to do what you want to do, you may discover you are most of the way to a macro solution. For example...

 

1. Select a column in Excel.    [Ctrl+space]

2. Copy it.                                 [Ctrl+c] 

3. Switch to the form.              [Alt+Tab... but the Macro Express "Window Activate" will be better]

4. Output cell values one at a time, and press Tab after outputting a value in a field. [Not sure how to do this... but maybe when you get to this stage, ask for help!]

 

 

[rberq]

If your data is in a CSV file, it is especially easy.  (If the data is in an Excel sheet, you can save it as a CSV file, manually or as the first step of your macro.)

 

The instructions ASCII File Begin Process and ASCII File End Process constitute a repeating loop that read the CSV file one line at a time and place that line's data into an indexed array.  

For example, call your array "ARY".  
If the first line of the CSV file is "a","b","c","d"
then ARY(1)=a, ARY(2)=b, ARY(3)=c, ARY(4)=d

Then you type or paste the array entries into the form, and continue with the next line of the CSV file.
There is no visible jumping back and forth between the file and the form, unlike between a spreadsheet and the form, because the CSV file is not visible on the computer screen.  

 

Macro logic is something like this:
Open data-entry form if it is not already open

   [Beginning of loop]
ASCII File Begin Process (file name and array dimensions are defined within this command)
(data from one CSV line is automatically placed into array)
Paste ARY(1) into form
Paste ARY(2) into form
Paste ARY(3) into form
Paste ARY(4) into form
ASCII File End Process (control automatically returns to do the next CSV line)

   [End of loop, falls through to next instruction after entire CSV file has been processed]
Macro Exit

 

[Rick Altman]

That is phenomenal! I had no idea that a function like that existed in this program that I have been using for nearly 20 years. I created my solution in about 15 minutes and I can't wait to consider other uses for this way-cool and oh-so-powerful capability.

 

Here is my follow-up question: Is there a way to go in reverse? Can I start with a form, perform a copy to variable<Tab>copy to variable, etc., and then do a mass dump into a CSV file, column by column?

 

Many thanks!

[Cory]

Yes. However I recommend using a tab delimiter instead. This avoids needing to qualify text with quote marks. BTW this is native for the tabular exchange of Microsoft products. So you can build the big string of tabular data with tabs, carriage returns, and line feeds and either save it to a TSV or paste it into Excel where the entire table will be inserted in one action. I've written some one this in these articles. 

Process Tabular Data in Variables

CRLF and TAB

Macros If you have any other interests. 

 

If you do a lot of this sort of thing, you might want to go to the next level. I started with web automation with MEP but now due to the huge volume I do, I have .NET programs that do the work. But without having to learn a programming language there are ways to send information to a web form handler directly with a request. This requires no web browser so much simpler, faster, and more reliable. Also timing problems go away. Most web forms send their data in a HTTP POST request but some use GET. Natively MEP can only send a GET request but many form handlers will also accept the data in the parameters of a URI in a GET request. Also one can make a simple script one can use in a External Script command in MEP. If you have any interest in that, feel free to send me a PM or respond here.
I'm also available for hire and am willing to teach or do. I give free consultations, often enough for one to get going on their own. 

[rberq]

10 hours ago, Rick Altman said:

do a mass dump into a CSV file, column by column?

Macro command Variable Modify String has options that would allow you to take form data, concatenate multiple data items, and write the concatenated string to a file.  For example, with values a, b, and c from the form:

 

//  
Variable Set String %line% to "" // set line null

Variable Modify String %line%: Append Text (") // line contains double-quote
Variable Modify String %line%: Append Text String Variable (%form_data_1%) // line contains "a

Variable Modify String %line%: Append Text (",") // line contains "a","
Variable Modify String %line%: Append Text String Variable (%form_data_2%) // line contains "a","b

Variable Modify String %line%: Append Text (",") // line contains "a","b","
Variable Modify String %line%: Append Text String Variable (%form_data_3%) // line contains "a","b","c

Variable Modify String %line%: Append Text (") // line contains "a","b","c"
Variable Modify String: Append %line% to text file, "c:\test|filaname.csv" // write line to file
//  

 

Even though the last command writes to a "text" file, the fact that you use the .csv extension on the file name will make Windows, Excel, etc. consider it to be the csv file type.