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. The script reads the data from the Excel file row by row and outputs it to a simple Google Docs Web Form.
Or you may be able to use UISetValue depending on how "accessible" the web objects are (experiment with the FindObject Wizard).ĭownload this zip for a simple example script and Excel file.
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. 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). 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. 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. In your case you're probably going to need a bit more than this. 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).
You may be able to use UISetValue instead or some other method. Here we're using Send which sends keystrokes. So we're simulating the user typing the data in. We then focus Notepad and "Send" the data. Within each loop iteration we advance the row counter and use XLGetCell to get the data for that particular row. 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.
In your case your desktop application may already be running, so you may not need to do this.
This particular script first starts Notepad.
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. 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. Related HelpFile Links : IfFileExists | XLOpen | Input | WaitWindowOpen | Repeat | XLGetCell | Trim | SetFocus | Send | Press Transferring Data from Excel to a Desktop or Web App