Did you know that since v15 you can run Excel VBA macros from directly within your Macro Scheduler code? We’ve added a helpdesk article showing you how it works here.
November 12, 2020
May 27, 2020
The world has changed during the past 9 weeks or so, and some of the MJT staff have been putting Macro Scheduler to good use to help them during lockdown.
But Macro Scheduler came to the rescue.
One simple automation script monitored a handful of web pages 24/7, refreshing every minute or so. It watched our local Supermarket looking for slots, and it monitored the white-goods stores waiting for any one of half-a-dozen freezers to be re-stocked.
A few days went by. No luck. Was this going to work? Would there ever be any slots? On day 6 I received a text on my phone – I’d been smart enough to use Zapier so Macro Scheduler would notify me via text wherever I may be.
A freezer had become available on the John Lewis website! We scurried over to the laptop and hurriedly clicked on the link. It’s no exaggeration to say our hearts were in our mouths. This was important! We didn’t even read the description. We just needed a freezer. Added it to our cart. Paid for it. Got the confirmation email. A few seconds later none were available again. We had successfully managed to purchase a freezer during the few minutes one online retailer had some.
Since then, over the past 8 weeks or so we used Macro Scheduler to tell us when those supermarket deliveries are available. They only pop up once or twice a day if we’re lucky – but when they do, we hear our little notification beep and off we run to the laptop and start shopping! It has been a Godsend, and it hasn’t failed us yet. Thanks to Macro Scheduler we top-up our little freezer every two weeks, and lockdown has been largely worry-free.
The process was simple. Here are some of the commands we used. IECreate, IENavigate to get us to the pages we were looking for and IEGetAllText helped us scrape the text from the page, then we used Position to see if the text we were looking for was, or wasn’t, on the page. All contained in a nice little loop, watching and waiting 24/7.
April 9, 2020
We find ourselves in challenging times. At MJT Net we’re all working from home, with the difficulty of trying to educate and entertain our children at the same time.
Of course there is also a great deal of financial uncertainty. Businesses and people everywhere are feeling the pinch, ourselves included.
Clearly though, the biggest challenge is being felt by our amazing health services, which are under incredible pressures.
Behind the scenes, healthcare IT departments are working against the clock, often with huge volumes of data. So it’s great to know that Macro Scheduler is reducing the burden during the current crisis.
Here’s what Tom, a Clinical Systems Developer at an NHS hospital in Northern England, told us last week:
“In a crisis situation, we needed a straightforward automation tool which could help us make quick changes to a wide range of systems – without time to write API code. We found that Macro Scheduler more than fit the bill: it rapidly enabled us to automate repetitive tasks and to free up needed resources – ultimately giving us the time to better support our clinical staff in fighting coronavirus. In particular we have been very impressed with its ability to easily integrate with Google Chrome – like many organisations a lot of our tools are web-based and our ability to build automation into these workflows is a real game-changer.”
If you work for a hospital on the front-line of coronavirus care and would benefit in using Macro Scheduler, or require additional licenses, please contact me using your official work email address, tell me what you need Macro Scheduler for and we will see how we can help.
We’ve worked closely with a few UK hospital trusts over the years. In particular, Bournemouth Hospital has used Macro Scheduler extensively to automate dozens of clinical and administrative processes within the trust. You will find a case study we did with them a few years ago here. Many times Macro Scheduler has been used to streamline processes that save the time of clinicians. And that can mean more patients get seen.
March 19, 2020
We find ourselves in challenging times. Things here in the UK seem to be changing rapidly every day. Only last night we heard that our schools will close tomorrow and this morning it was announced they were likely to remain closed for the rest of the academic year. So no school until September. I am already working from home. All of us here at MJT are working remotely, so we’re all safe and able to support you. But with two boys at secondary school I am now preparing for having them both at home and having to make sure they do their work while trying to do mine. Could be interesting!
Clearly though, the biggest challenge is being felt by our amazing health service, which is under incredible pressures. So, where possible I’d like to offer any NHS establishment free use of Macro Scheduler while this crisis lasts. If you work for the NHS and would benefit in using Macro Scheduler, or require additional licenses, please contact me using your NHS email address, tell me what you need Macro Scheduler for and we will see how we can help.
We’ve worked closely with a few NHS trusts over the years. In particular, Bournemouth Hospital has used Macro Scheduler extensively to automate dozens of clinical and administrative processes within the trust. You will find a case study we did with them a few years ago here. Many times Macro Scheduler has been used to streamline processes that save the time of clinicians. And that can mean more patients get seen.
March 12, 2020
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.
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:
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 11, 2020
Are you automating web apps, or interacting with APIs?
Macro Scheduler is packed with features that help with automating desktop applications and simulating user input. But it also has great support for consuming web resources via HTTP requests, as well as built in functions for automating modern web browsers.
“Being able to move between GUI and API methods to handle our automation needs is priceless”
March 6, 2020
The new Chrome and Edge functions in Macro Scheduler 15 make it possible to locate frames and iframes and then manipulate elements within them.
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.
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:
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
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 28, 2020
A quick note for those of you using the Macro Scheduler Pro Enterprise pack and/or the MacroScript SDK. Workflow Designer and the SDK have now been updated with the latest 15.0.06 MacroScript engine and can be downloaded from the registered user area.
February 27, 2020
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.
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:
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:
Run your script and if your script is correctly pointing at ChromeDriver.exe you should see an empty Chrome window appear.
To navigate to Yahoo add the ChromeNavigate command, passing the session_id to it:
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:
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:
The script so far:
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:
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:
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:
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:
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:
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:
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:
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:
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:
Here’s the full script:
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.
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.
February 20, 2020
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:
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
If you have a subscription visit your personal link sent to you in your welcome email.