Transferring Data from Excel to a Desktop or Web App

*This article is partially out of date as it references Internet Explorer functions later in the article.

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.

Desktop Apps

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

Note: Please use the Macro Scheduler version 15 Chrome/Edge functions instead of the Internet Explorer functions mentioned below.

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).

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.