Transferring Data from Excel to a Desktop or Web App
This is a common requirement. You have an Excel sheet with some data in it and you want to loop through that data and send each row (or record) into some other application.
There's actually a basic sample script that ships with Macro Scheduler called "Extract from Excel" which demonstrates how to do this. You'll find it in the Samples group after installing the software. It sends data to Notepad. Your own application is going to be a bit more involved than Notepad but the principle is the same and everyone has Notepad so you'll be able to run the sample script.
Here's the sample script:
//Specify the path of the Excel file here Let>filename=%SCRIPT_DIR%\example.xls IfFileExists>filename //Start Excel and open the book XLOpen>filename,1,xlH //Ask how many rows we should get Input>maxrows,How many rows shall I get?,200 If>maxrows=0 Exit>0 Endif //As an example I'm going to paste the data into Notepad Run>Notepad.exe WaitWindowOpen>Untitled - Notepad //Data starts on row 2 - row 1 has header Let>row=1 Repeat>row Let>row=row+1 //get the fields for this row XLGetCell>xlH,Sheet1,row,1,field_1 XLGetCell>xlH,Sheet1,row,2,field_2 XLGetCell>xlH,Sheet1,row,3,field_3 XLGetCell>xlH,Sheet1,row,4,field_4 //trim the results (Excel sometimes adds CRLFs) Trim>field_1,field_1 Trim>field_2,field_2 Trim>field_3,field_3 Trim>field_4,field_4 //For this example we'll just paste each row into Notepad SetFocus>Notepad* Send>Row: %row% Press Tab Send>%field_1% Press Tab Send>%field_2% Press Tab Send>%field_3% Press Tab Send>%field_4% Press Enter //you'll want to replace the above lines to send the data to your real app. Wait>0.05 Until>row>maxrows Else MessageModal>Could not find: %filename% Endif
Essentially there are three main things going on in this script:
- A Repeat/Until loop.
- Getting the data out of Excel using XLGetCell
- Sending the data to the target app (Notepad in this case) using keystrokes.
This particular script first starts Notepad. In your case your desktop application may already be running, so you may not need to do this.
The Repeat/Until loop loops until maxrows which is set by the Input box which asks the user how many rows to get. It's more likely that you'll not want to ask the user anything and you'll want to loop through ALL rows in the sheet. In which case use XLGetSheetDims to get the number of rows instead.
Within each loop iteration we advance the row counter and use XLGetCell to get the data for that particular row. We then focus Notepad and "Send" the data. Here we're using Send which sends keystrokes. So we're simulating the user typing the data in. You may be able to use UISetValue instead or some other method.
With Notepad we simply send a line of text for each row in the sheet. So at the end of our loop we just hit Enter (Press Enter on line 47). In your case you're probably going to need a bit more than this. Let's imagine you are adding customer records. At the end of the loop you'd probably hit a Save button or similar. You'd then want to wait until you know the record has been saved.
That might be a case of waiting for the window title to change, or a dialog box to appear, or possibly some other visual cue. So you might use WaitWindowOpen or WaitScreenText or WaitScreenImage or something else altogether.
You're then going to need to get back to your starting point for the next record. That might involve sending some more keystrokes or clicking somewhere else to start a new record. And you'll want to make sure focus is in the first input box you're going to send data to so you might want to "Press Tab" the required number of times to get focus there (or you may be able to use UIFocus).
Sending to Web Apps
If you are wanting to send the data into a Web application it is better to avoid sending keystrokes and instead use the IE functions such as IEFormFill or IETagEventByAttrib. These work by getting beneath the skin of Internet Explorer and accessing its "Document Object Model". They allow you to access the HTML objects directly. Or you may be able to use UISetValue depending on how "accessible" the web objects are (experiment with the FindObject Wizard).
Download this zip for a simple example script and Excel file. The script reads the data from the Excel file row by row and outputs it to a simple Google Docs Web Form.
Tip - Start with the Tricky Stuff
When building a script like this the first thing I usually do is write the code that sends data into the target application. I'll send static data first, hard code into my script. That's the bit that will take the time to get right - to make sure you're focusing the right elements and sending data into the right places, that the script moves from field to field correctly. Once I'm happy with that part of it I can add in my Excel loop and substitute the static data for the variables coming from the XLGetCell function.
Another idea is to put the code that sends the data into the target application into a subroutine. You can then call this subroutine with a GoSub from within your Excel which makes your loop easier to read.