March 12, 2020

Sending Keystrokes to Elements in Chrome and Edge Using the New Chrome/Edge Functions

Filed under: Automation,Web/Tech — Marcus Tettmar @ 12:56 pm

It may not be immediately obvious that as well as setting the value of elements with ChromeSetElementValue and EdgeSetElementValue you can also send non-character keystrokes, such as Enter, or Page Down.

To do this, use the key codes for Edge and Chrome listed here.

For example:

ChromeSetElementValue>session_id,message_elements_1,First line
//Press enter on the element ... 
ChromeSetElementValue>session_id,message_elements_1,\uE006
ChromeSetElementValue>session_id,message_elements_1,Second line

Note that when sending keystrokes only one can be sent at a time.

Recently someone needed to scroll down inside a div, in order to force the page to fetch more data. Issuing a Page Down on the div element did the trick:

ChromeSetElementValue>session_id,div_elements_1,\uE00F

So, ChromeSetElementValue sends keys as well as sets values – perhaps it should have been called ChromeSendKeysToElement and just maybe we’ll add that as a mapping if it helps.

Of course you don’t have to use the new Chrome/Edge functions – you can still use UI methods and use SetFocus, SendText and/or Image Recognition to locate page elements.

March 6, 2020

Working with Frames in Chrome and Edge

Filed under: Automation,Scripting,Web/Tech — Marcus Tettmar @ 1:28 pm

The new Chrome and Edge functions in Macro Scheduler 15 make it possible to locate frames and iframes and then manipulate elements within them.

Switching Frames

The ChromeSwitchFrame and EdgeSwitchFrame functions allow you to specify a frame or iframe element, by index or element reference, to switch the “browsing context” to.

What this means is that any subsequent interactions will take place against elements within that frame. So a subsequent ChromeFindElements call will attempt to locate the specific element within the frame, rather than the parent page, and ChromeElementAction will act against the given element within that frame.

//Find the frame using xpath
ChromeFindElements>sessionID,xpath,//iframe[@src='/contact-form/formpage.html'],elements

//switch browsing context to this frame
ChromeSwitchFrame>session_id,element,elements_1,res

If xpath doesn’t mean anything to you see my recent post Using Macro Scheduler 15’s Chrome Functions which includes an explanation of using Chrome’s Developer Tools to identify elements.

At some point you may need to switch the browsing context back to the parent frame. To do this you call ChromeSwitchFrame/EdgeSwitchFrame again with a null index. Subsequent calls to the Chrome/Edge functions will then act against the parent frame.

ChromeSwitchFrame>session_id,index,null,res

Traversing Frames

Since each time you switch frames you change the context to that frame, calling ChromeSwitchFrame or EdgeSwitchFrame again (on a valid frame element within) will switch the context down another level.

//Find the frame using xpath
ChromeFindElements>sessionID,xpath,//iframe[@src='/contact-form/formpage.html'],elements
ChromeSwitchFrame>session_id,element,elements_1,res

//Switch to the next frame down *within the current frame*
ChromeSwitchFrame>session_id,index,0,res

Differences between Edge and Chrome

The Edge and Chrome functions work in the same way and are almost identical. There’s one major difference when it comes to switching frames. When specifying an element (rather than index) ChromeSwitchFrame requires the element ID, whereas EdgeSwitchFrame requires the full element object. As well as an array of element IDs, EdgeFindElements returns a second array of the objects:

EdgeFindElements>sessionID,xpath,//iframe[@src='/contact-form/formpage.html'],FrameElements

This returns two arrays prefixed with the name passed as the return var (TheElements): FrameElements_1 … FrameElements_n and FrameElements_objects_1 … FrameElements_objects_n. For EdgeSwitchFrame use the second _objects array:

//switch to the frame
EdgeSwitchFrame>session_id,element,FrameElements_objects_1,res

More Help

View the Chrome Functions and Edge Functions topics in the Macro Scheduler Manual.

Full Example:

Let>CHROMEDRIVER_EXE=c:\chromedriver.exe

//start a Chrome session
ChromeStart>session_id

//navigate to google.com
ChromeNavigate>session_id,url,https://www.mjtnet.com/contact.htm

//Find the frame
ChromeFindElements>sessionID,xpath,//iframe[@src='/contact-form/formpage.html'],elements

//switch to the first frame (the one with the fields)
ChromeSwitchFrame>session_id,element,elements_1,res

//now anything we do is inside that frame so we should be able to get the name field and enter something
ChromeFindElements>session_id,id,name,inputs
ChromeSetElementValue>session_id,inputs_1,john doe

//Switch context back to parent
ChromeSwitchFrame>session_id,index,null,res

February 27, 2020

Using Macro Scheduler 15’s Chrome Automation Functions

Filed under: Automation,Scripting,Web/Tech — Marcus Tettmar @ 6:48 pm

Macro Scheduler 15 now has native Chrome and Edge functions. You may be wondering how they work. There are a couple of sample scripts included with v15 but if you’re not sure how to get things set up or how to identify elements in the page this article is for you.

I wanted an example that wasn’t too involved but juicy enough to offer a few hurdles and demonstrate some features like xpath. I’ve set upon using Yahoo Finance to pull out the value of a stock symbol. Bear in mind that websites have a habit of changing so I can’t guarantee that the code in this article will work 6 months from now. But that shouldn’t matter much – it’s the principles of how to do this that matters most.

Before we begin make sure you are running Macro Scheduler 15.0.06 or above.

ChromeDriver

Before we can use the new Chrome functions we need to download the win32 version of ChromeDriver from: https://chromedriver.chromium.org

The zip file contains a single file: chromedriver.exe. Save this somewhere.

We need to tell the script where chromedriver.exe is. In my examples below I’ve got it in the root of drive C. So the first line of my script needs to be:

Let>CHROMEDRIVER_EXE=c:\chromedriver.exe

Start a Chrome Session

Now, we can start a Chrome session. To do this we use the ChromeStart function and give it a variable name that we want the session ID stored in. I’m using session_id. We’ll need this for the rest of the commands:

ChromeStart>session_id

Run your script and if your script is correctly pointing at ChromeDriver.exe you should see an empty Chrome window appear.

Navigating

To navigate to Yahoo add the ChromeNavigate command, passing the session_id to it:

ChromeNavigate>session_id,url,https://finance.yahoo.com/

Close the Chrome browser window (you don’t have to but running your script is going to start another) and run your script again.  This time you should see it navigate to Yahoo and show something like this:

Using Developer Tools

So the first thing we need to do is get past that “I agree” button. This is where Chrome’s Developer Tools comes in to help us find the element and the attributes we can use to identify it.

Right click on the “I agree” button and select “Inspect” at the bottom of the popup menu.  Chrome’s Developer Tools should appear. Usually at the bottom, but if you’ve used it before you may have changed the layout. It should appear in ‘Elements’ view and the highlighted element should be the “I agree” button:

Sometimes, I’ve found that the first time I select “Inspect”, if Developer Tools wasn’t already open, the element I want isn’t the highlighted one. Usually right clicking on the element and selecting Inspect a second time does the trick.

Another way to identify the element once Developer Tools is already open is to click the element selector button which you see at top left in the screenshot above. Then move the mouse to the “I agree” button and click.

Once you’ve got the right element selected look for an attribute which we could use to identify it:

<button type="submit" class="btn primary" name="agree" value="agree">I agree</button>

Ideally we want something unique.That isn’t always possible and I’ll talk about what we can do in those cases later. Here we’re in luck. The element has a name – “agree”. We can use this to get a reference to the element so that we can then ‘click’ it:

ChromeFindElements>session_id,name,agree,agree_elements

Notice how we’re passing the session ID again (we want to make sure we’re referring to the Chrome window we started). The second parameter is the “Strategy” we want to use to identify the element. ‘Name’ is an available strategy – find the element by name. The third parameter is the strategy value. For ‘Name’ it’s the name itself which in this case is “agree”. Finally we give the command a variable name we’d like to store the located element IDs in.

Valid ‘strategies’ you can use are:

id, name, class name, css selector, link text, partial link text, tag name, xpath

Clicking on the Element

ChromeFindElements returns an array of matching elements. In this case the array should have only one item because there’s only one matching element. So the item we want is the first one: agree_elements_1.

Let’s use that to click on the button using the ChromeElementAction command:

ChromeElementAction>session_id,agree_elements_1,click

The script so far:

Let>CHROMEDRIVER_EXE=c:\chromedriver.exe
ChromeStart>session_id
ChromeNavigate>session_id,url,https://finance.yahoo.com/
ChromeFindElements>session_id,name,agree,agree_elements
ChromeElementAction>session_id,agree_elements_1,click

Close your browser window and re-run the script. Hopefully you’ll sail right through that I agree box.

ChromeDriver should wait for events to complete and pages to load before firing new events, but if you have any issues you may need to slow things down a touch. E.g. stick a Wait>1 before the click.

Sending the Stock Symbol

We now need to find that input box, enter something and submit it. Using the same ‘Identify’ technique as described above you should find that the input element has its “id” attribute set to “yfin-usr-qry”:

So we’ll use that:

ChromeFindElements>session_id,id,yfin-usr-qry,input_elements

I’ve used input_elements as my array name for the found elements. We now want to enter some text. Let’s search for Microsoft which is stock symbol “MSFT”. To do this we’ll use the ChromeSetElementValue command. There’s only one matching element again so as above we use the first match input_elements_1:

ChromeSetElementValue>session_id,input_elements_1,MSFT
Wait>1

Notice this time I have put a wait afterwards. The next step is to ‘submit’ the field. I found that submitting it without the wait didn’t allow enough time for the entry to ‘take’. There could be some background processing going on which needs time to finish. Remember, as with most automation, we’re automating something that was designed for a user, and Robots run faster than users, so sometimes need slowing down!

We will now use the ChromeElementAction command again, this time with the submit action:

ChromeElementAction>session_id,input_elements_1,submit

Instead of doing this we could have found the search button and issued a ‘click’ on that. But that would require another ChromeFindElements which in this case is unnecessary. We can ‘submit’ the input directly.

Extracting The Stock Value

Rerun the script and you should end up looking at the Microsoft stock info which we want to extract.

Tip: rerunning the script from the top every time isn’t ideal. We’ve got the Chrome window open so the session is still active. So what you could do if you just want to run and test a few commands is grab the session ID from the watch list and assign it with a Let statement placed just before the commands you want to test. E.g:

Let>session_id=b6976cfec0326dcce35ad3674c3ed90e
ChromeFindElements>session_id,id,yfin-usr-qry,input_elements
ChromeSetElementValue>session_id,input_elements_1,MSFT

This is where things get a little more interesting. Use the inspect element tool to locate the element as above. You’ll see it’s a span element. You might also notice the class keeps changing. This site is very dynamic, updating as the stock value changes. In this instance we aren’t lucky enough to have an id, or a name. But notice those custom ‘data-reactid’ attributes.  You’ll see that they all have different values, and the one we want is “34”.  Let’s see if we can use that.

Now, ‘data-reactid’ is a custom attribute. It’s not standard html, and there’s no ‘strategy’ called that. So we’re going to use something called ‘xpath’. xpath is incredibly powerful and can help us find pretty much anything. There’s a great tutorial on xpath here.

We need to find a ‘span’ element with attribute data-reactid which is set to “34”. In xpath we can do this with:

//span[@data-reactid='34']

The // means find nodes regardless of where they are – we don’t need to provide a path. The @ symbol is used to specify an attribute. And we’re giving it a value. We’re saying find a span element with attribute data-reactid set to value “34” and we don’t care where it is.

So we can use this in the ChromeFindElements command as follows:

ChromeFindElements>session_id,xpath,//span[@data-reactid='34'],info_elements

Now, before we continue we should confirm whether or not this is the ONLY span tag with this attribute and value. We could do that either by searching the source using Chrome’s element inspector, or, I think easier, just debug the code to see what we get back. Stick a breakpoint just after this line, run the script and then look at the watch list. You’ll see there are two:

Now, we’re going to need to extract them to see which is the right one. We’re going to use the ChromeGetElementData command:

ChromeGetElementData>session_id,info_elements_1,text,stockValue

If you step through this command and look at the value of stockValue in the watch list, you’ll notice that info_elements_1 is not the one we want. We want the second one:

ChromeGetElementData>session_id,info_elements_2,text,stockValue
MessageModal>Value is %stockValue%

So there we have it. We’ve started Chrome, navigated to Yahoo Finance, clicked ‘I agree’, searched for MSFT and extracted the stock value.  We might want to close the Chrome window at the end:

ChromeQuit>session_id

Here’s the full script:

Let>CHROMEDRIVER_EXE=c:\chromedriver.exe

ChromeStart>session_id
ChromeNavigate>session_id,url,https://finance.yahoo.com/

ChromeFindElements>session_id,name,agree,agree_elements
ChromeElementAction>session_id,agree_elements_1,click

ChromeFindElements>session_id,id,yfin-usr-qry,input_elements
ChromeSetElementValue>session_id,input_elements_1,MSFT
Wait>1

ChromeElementAction>session_id,input_elements_1,submit
Wait>1

ChromeFindElements>session_id,xpath,//span[@data-reactid='34'],info_elements
ChromeGetElementData>session_id,info_elements_2,text,stockValue
MessageModal>stockValue

ChromeQuit>session_id

Using Edge instead of Chrome?

The Edge commands work in exactly the same way as Chrome. And finding elements in Edge is done in exactly the same way. So you should be able to adapt the above code to work with Edge very easily. The main difference is that Edge is a bit more picky when it comes to installing the correct version of Edge Driver.  And if you have an older ‘legacy’ version of Edge you will need to install Microsoft Web Driver instead of MS Edge Driver.  The manual explains how.

Conclusion

The Chrome and Edge functions are incredibly powerful and finding elements using Chrome’s, or Edge’s, Developers Tools is quick and easy. There’s also a lot more that you can do, such as grab the entire source of the page. I hope this has been useful. Let me know in the comments below how you are using these functions.

You’ll find more info in the manual. See Chrome Functions and Edge Functions.

If you have any questions find me on the forums or drop us a line.

February 20, 2020

Macro Scheduler 15 is Here!

Filed under: Announcements,Automation — Marcus Tettmar @ 11:26 am

I am really pleased to announce the release of Macro Scheduler 15.

As well as lots of small internal improvements, Macro Scheduler 15 brings native functions for automating the Chrome and Microsoft Edge browsers. We’ve also added some new Excel functions, including one that allows you to run any VBA code you like, making the native Excel functions infinitely extendable.

It was also time for a visual refresh…

Modern Web Browser Automation

With Macro Scheduler 15 we’ve introduced two sets of new web browser automation commands. One for Microsoft Edge and another for Google Chrome.

These functions use the new WebDriver interface, and connect to Chrome and Edge via Google’s ChromeDriver and Microsoft’s WebDriver or MS Edge Driver components.

Here’s a short video of Chrome being controlled with these new commands:

For more info see the help topics: Chrome Functions, Edge Functions.

Enhanced Excel Automation

More Excel functions has been a popular request. So we added some. The challenge though, is how to know when to stop. Microsoft’s Excel VBA is pretty unlimited. There’s no way we could wrap every possible function into a native Macro Scheduler one, so we needed a solution.

Well, for years now we’ve been able to convert Excel VBA into VBScript which can be run inside Macro Scheduler. But converting VBA to VBScript can be challenging. Instead, we thought it would be great if you could just pass any valid VBA directly into Excel. So now you can!

//run pure VBA code to set the color
XLRunCode>xlH,ActiveSheet.Range("B12").Interior.Color = vbRed

//could be an entire block of code - let's create a bar chart 
LabelToVar>vba_code,theCode
XLRunCode>xlH,theCode

/*
vba_code:
  Range("A1:B11").Select
  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
  ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$11")
  ActiveSheet.Shapes("Chart 1").IncrementLeft +50
  ActiveSheet.Shapes("Chart 1").IncrementTop -30
*/

You should be able to record a macro in Excel and then copy/paste the code it produces into Macro Scheduler like the above.

Downloads & Upgrades

Licensed Downloads/Upgrades | Trial Version | New Licenses

If you have a subscription visit your personal link sent to you in your welcome email.

February 12, 2020

Setting cell functions in Excel, plus a sneak peak!

Filed under: Announcements,Automation — Marcus Tettmar @ 12:30 pm

Something that often gets asks is ‘Can I insert a function into Excel’ using Macro Scheduler‘s native XL functions?’.

Yes, you can. It may not be obvious from the documentation but with XLSetCell you can output any expression accepted by Excel. It doesn’t have to be a literal value.

You might set a literal value with something like this:

XLSetCell>xlH,Sheet1,2,2,560,result

In Excel you insert a function by starting the input with the ‘=’ sign, so we can do the same with XLSetCell:

XLSetCell>xlH,Sheet1,20,2,=SUM(B1:B19),result

In the same way we can force the cell to text format by preceding the input with an apostrophe:

XLSetCell>xlH,Sheet1,2,2,'560,result

These formats are standard Excel features. All we’re doing here is passing a value to Excel that it understands. But you may not have realised you can do this with Macro Scheduler’s native functions.

Coming Soon – More functions & run ANY VBA code!

A few people have requested more native XL functions. We’re working on it.

For example, a new function to set the cell colour:

RGB>50,150,50,color1
XLSetCellColor>xlH,Sheet1,13,2,color1

But how about being able to run ANY valid Excel VBA you like?

XLRunVBA>xlH,ActiveSheet.Range("B12").Interior.Color = vbRed

It could be a whole block of code:

LabelToVar>vba_code,theCode
XLRunVBA>xlH,theCode

/*
vba_code:
  Range("A1:B11").Select
  ActiveSheet.Shapes.AddChart2(201, xlColumnClustered).Select
  ActiveChart.SetSourceData Source:=Range("Sheet1!$A$1:$B$11")
  ActiveSheet.Shapes("Chart 1").IncrementLeft +50
  ActiveSheet.Shapes("Chart 1").IncrementTop -30
*/

These features are in development. Watch them in action here:

The sky is the limit!

Note: At time of writing these functions do NOT exist in the current version. These are in development. Their names and syntax may change. Keep an eye out for updates.

Be the first to get these new features by making sure your maintenance or subscription us up to date 🙂

April 4, 2018

Running Powershell Code From Inside Your Macros

Filed under: Automation,Scripting — Marcus Tettmar @ 4:25 pm

A while back I wrote this blog post which shows you how you can have a PowerShell script run Macro Scheduler commands via the MacroScript SDK.

However, what if you want to do it the other way around and run PowerShell code from inside a Macro Scheduler macro instead?

One way would simply be to run a PowerShell script on the command line, like this:

Run>powershell.exe -file "c:\myfolder\myscript.ps1"

But what if you didn’t have a PowerShell script file?  Well, while you could always have your macro create one using the WriteLn command (and delete it after), you don’t need to as you can pass a sequence of commands to PowerShell on the command line:

powershell.exe -executionPolicy bypass -command "Write-Host 'Hello World'; exit 123;"

As you can see all we’re really doing is passing the content of the script instead of a file.

We can also return the exit code of the PowerShell script to a variable in your macro.  Here’s an example:

//This line ensures the powershell script finishes before the macro continues
Let>RP_WAIT=1
//Uncomment this line to HIDE the powershell window when ready to go live
//Let>RP_WINDOWMODE=0

Let>localVar=Hello World
LabelToVar>MyPowershellScript,theScript
Run>powershell.exe -executionPolicy bypass -command "%theScript%"

//display the exit code
MDL>RP_RESULT

/*
MyPowershellScript:

$var = '%localVar%';
Write-Host $var;
Start-Sleep -s 10;
exit 555;
*/

What we’ve done here is store our PowerShell code in a label. We get that code into a variable and then pass it to PowerShell on the command line.

Notice how we can embed Macro Scheduler variables in the code too. We use PowerShell’s exit statement to set a return value which Macro Scheduler gets in the RP_RESULT variable.

Setting RP_WAIT to 1 makes sure the macro waits for PowerShell to finish before proceeding with the rest of the script. You may also want to hide the PowerShell window by setting RP_WINDOWMODE to 0, but for testing purposes I’ve left that commented out so that you can actually see it working. That’s also the reason for the 10 second delay at the end of the PowerShell script – apart from showing how we can add multiple lines of code it also gives us a chance to see things happen!

April 2, 2018

Escaping File Paths in HTTP Requests

Filed under: Automation,Web/Tech — Marcus Tettmar @ 5:48 pm

If you ever need to send a path as a URL parameter in an HTTP request, be sure to “escape” it to avoid the path delimiters (“\” symbols) being seen as delimiters in the URL path.

E.g. let’s say you need to send a filename as a parameter in a GET request, like this:

https://someserver.com/resource?filename=c:\my files\subfolder\filename.txt

If we fail to escape those “\” characters we are likely to get a server error or a 404 not found error at best. The space character in the above example is also likely to upset things!

We can solve this by using VBScript’s Escape function to “URL Encode” the string. What this does is replace the special characters with a special code made of a % symbol and two hexadecimal digits. E.g. a space character is replaced with %20.

So, we should use the Escape function something like this:

Let>strFilename=c:\my files\subfolder\filename.txt
VBEval>Escape("%strFilename%"),strFilename

Then we can safely do:

HTTPRequest>https://someserver.com/resource?filename=%strFilename%,,GET,,strResponse

If you were to paste the URL into Google Chrome or IE you will find that behind the scenes they apply this encoding for you.

Tip: A great way to test HTTP requests is to use http://webhook.site – this gives you a special URL you can send your data to (in place of your real web service) and for each request you make it will show you what it received. Try manually sending a filename like the one above using your web browser and you’ll see those % codes being added for you.

March 28, 2018

Will robotic process automation take your job away?

Filed under: Automation,General,Macro Recorder — Marcus Tettmar @ 2:25 pm

TLDR: No! That’s not what we’re here for. Robotic process automation is here to help you.

We often get called in to help a company automate a process. Many times this involves automating something that an end-user does. To make macros that make their job easier, less painful, and make them more productive.

Initial Resistance to Change

Sometimes, at the outset, we (or the IT department proposing Macro Scheduler as a solution) detect a little resistance from the end user. If not properly briefed they may be concerned that what we want to do is take work away from them, and they may be concerned that the macros, or software robots, are about to take their jobs away.

Epiphany!

Nothing could be further from the truth and by the time we’re done, the end users love us to bits and usually give us a long list of other things they’d like us to automate! Often they discover how easy it is to do it themselves with tools like the Macro Recorder and other code wizards.

I’ve written thousands of macros for hundreds of companies. I’ve never worked on anything that was designed to take a job away from an employee, other than one the employee didn’t like doing and didn’t have time to do without it impacting on their core purpose. In every case the macros we wrote were there to make the employees’ jobs easier, or to allow them to be more productive in more important areas. For example: removing a tedious admin task from an already overstretched clinician, allowing them to see more patients.

I suppose you could argue that the many over-night data-entry style macros we’ve written could have been done instead by a team of data entry personnel. But such a team didn’t exist to start with, or would have been cost prohibitive, or too error-prone to be considered in the first place.

Improving Productivity

So to answer the question, no, I don’t believe robotic process automation is about taking jobs away. It’s not about replacing staff. In my 21 years of automating user interfaces this isn’t what I’ve seen. Instead, I’ve been rewarded with making people’s lives easier, removing cumbersome, painful, processes, or using macros to simplify and improve an existing process.

March 23, 2018

Screen Scraping with Macro Scheduler [Update]

Filed under: Automation — Marcus Tettmar @ 9:55 pm

What is Screen Scraping?

Screen Scraping is a term used to describe the process of a computer program or macro extracting data from the display output of another application. Rather than parsing data from the database or data files belonging to an application, Screen Scraping pulls the data from the screen itself, extracting data that was intended to be displayed to the end-user as opposed to data designed for output to another application or database.  

Screen Scraping is necessary when there is a need to access the information displayed by the application but there is no method provided to access it behind the scenes. The database or data files may not be accessible, or may be undocumented or proprietary and therefore cannot be parsed easily; the costs associated with interacting with the database may be too high; or the license agreement or warranty prohibits it. In the case of legacy systems that are no longer supported there may be no knowledge of the data structures, or the technology used is no longer compatible with current technology. In these cases we are resorted to extracting the data from the screen – from the windows of the application.

The term Screen Scraping probably originates from the era of computer terminals when you could connect the terminal output of a computer to an input port on another and therefore record the screen data.  

Screen Scraping Methods

There are a number of ways we can retrieve information from the screen using Macro Scheduler, depending on the type of application the data is in.

Screen Scraping Web Applications

Applications like Macro Scheduler’s WebRecorder can access the data and objects inside an Interner Explorer window and can therefore be used to extract the data.  Technically speaking I would not call this screen scraping since WebRecorder is using an API interface provided by Internet Explorer, but the process of extracting information from web sites is commonly refered to as Screen Scraping.  With WebRecorder we can use the ExtractTag wizard to create code that extracts the text from a particular element in the page.   While WebRecorder is the easiest way to do it, it is also possible to automate IE and extract data from web pages by using VBScript. The following forum posts may help:

Automate Internet Explorer with OLE/ActiveX
Automate web forms with IE
HTTP GET and POST using VBscript

Screen Scraping Microsoft Office Applications

Microsoft Office Applications, like Internet Explorer, have a COM interface that allows scripts to manipulate them and access the data held within them.  Again, not really scraping data from the screen itself, as you are getting it directly from a programming interface. There are a number of examples in the forums and blog archives and also some sample scripts that come with Macro Scheduler which demonstrate how to automate Office applications and retrieve data from them.  

Working with Excel

Screen Scraping Regular Windows Applications

Most other applications don’t offer a scripting interface like MS Office or Internet Explorer.  This is where we really need to work directly with the screen.   There are a number of ways we can do this kind of Screen Scraping with Macro Scheduler.

Screen Scraping via Optical Character Recognition

Macro Scheduler 14.4 includes some really neat functions which make it really easy to OCR a portion of the screen:

  • OCRScreen
  • OCRWindow
  • OCRArea

The first of these is the simplest. It simply scans the entire screen and returns all the text it can recognise. Of course this is also the slowest as it has to perform OCR against the entire screen. OCRWindow takes a window title and scans only the area of the screen where that window appears. This is nice and simple and a good compromise if the window isn’t too large. Finally, OCRArea can be given a rectangular screen region (X1,Y1,X2,Y2). You could use FindObject to find the coordinates of a specific UI object and pass those coordinates to OCRArea if you want to narrow things right down.

The Text Capture Functions

Macro Scheduler includes some Text Capture functions which can be used to extract text from a given window, rectangular screen area or screen point.  These functions use low level system hooks which monitor applications calling the various “TextOut” functions that Windows uses to output text to the screen.  By doing so they are able to capture this text.  The Text Capture functions return the text to  a variable which you can then use as needed.  

However, a few applications don’t use the Windows built-in functions to create and output text.  Don’t worry –Most do, but a few use their own techniques.  When you realise that text on the screen is just a sequence of small dots, if the application programmer decided to build his own routine to assemble text from dots rather than calling the Windows functions which already do that for you, you’re not going to be able to capture it.

The text capture functions and their limitations are explained here.  There is an example application, here, created with Macro Scheduler, which you can use to determine whether or not the text you want to capture can be captured using the text capture functions.

http://www.mjtnet.com/blog/2007/12/12/capturing-screen-text/
http://www.mjtnet.com/blog/2008/01/03/screen-scrape-text-capture-example/

Using the Clipboard for Screen Scraping

If the text you want to capture is selectable then you can use the clipboard to retrieve it.  A Macro Scheduler macro can send the keystrokes necessary to highlight and copy the text to the clipboard and then use the GetClipboard function to retrieve that text to a variable.  This is far less elegant than using the Text Capture functions but might be necessary if the application concerned is not utlising any of the Windows text out functions to create the text.

SetFocus>Notepad*
//Select ALL
Press CTRL
Send>a
Release CTRL
//Copy to clipboard
Press CTRL
Send>c
Release CTRL

//Get and display the data
WaitClipboard
GetClipboard>theData
MessageModal>theData

March 15, 2018

Saving You Time and Money With Robotic Process Automation

Filed under: Automation,General,Macro Recorder — Marcus Tettmar @ 3:24 pm

Before Macro Scheduler existed I was a junior member of an IT department. As part of my job I built small tools to automate specific tasks. At the time I was using VB. Each time I had to automate a task I had to reinvent the wheel a little.

The whole point of Macro Scheduler was to simplify the task of building automation routines, or software robots. To avoid having to reinvent the wheel.

There’s no reason why you can’t automate your Excel-to-SAP/WEB/ERP/ACME-Desktop-App by writing code using C# or C++. But it’s going to take you longer than using Macro Scheduler. Macro Scheduler functions like “SendText” and “UISetValue” encapsulate some pretty low level and quite convoluted code. The code wizards and macro recorders which help you use them are even more complicated.

One of the main purposes of Macro Scheduler is therefore to enable people to automate things more quickly and more easily than could be done with traditional programming tools. It makes it possible for non-programmers but also simplifies and speeds up automation for developers.

Over the last 21 years – yes 21, Macro Scheduler was first launched in 1997! – we’ve helped people with a lot of automation tasks. We offer consultancy and have been into people’s offices and also helped over the phone and remote desktop. Most routines take us a few hours to create a macro for. Some take a day or two. Rarely do we need to spend more than three days on one process, though there are some projects which involve a series of automation routines that may therefore take longer or be done over a few sessions.

To do the job from scratch with C#, C++ or VB might take weeks. Many people who approach us seem to be imagining that to automate their task may take days or weeks. They are often very surprised when we tell them it’s a few hours not a few days.

We are all about saving time and money. That’s what our tools do and it’s why we built them. Our tools mean you don’t have to pay developers lots of money to spend weeks or months building custom solutions.

The only down side to this from our point of view is that we routinely disappoint large consultancy businesses and potential partners who are used to selling IT contracts worth hundreds of thousands. They approach us thinking that there’s a huge opportunity and that we’ll pay them a large cut of a big consultancy project.

But rarely does a job require so much time, and that’s the reason we’re here. Sometimes I sense these people want us to “flesh” projects out. They think we’re “too good” at what we do. We should slow down, make things more complicated and thus charge for more time.

But that isn’t us. That’s not why we’re here or why we created Macro Scheduler. The whole ethos of Macro Scheduler and MJT Net Ltd is to save time, to find more efficient, less expensive ways of doing things that were once thought impossible or too expensive to do, and to enable people to automate without specialised knowledge.

That said we’re happy to work with companies on an on-going basis. I have found that most businesses approach us with one specific routine in mind and then when they see what can be done they realise how it can be applied across the organisation in other departments, for other teams. Saving a team one hour a week may not seem like much, but do that for 500 other teams and it adds up to a huge efficiency saving for the entire business.

If you would like to talk to us to find out how we could help your business, whether on an ongoing basis or just for a one-off job, please drop me a line here.

Older Posts »