February 20, 2006

Accessing Databases

Filed under: Scripting — Marcus Tettmar @ 1:08 pm

Update 7th April 2008: Macro Scheduler 10.1 now has native database functions built in so in most cases it is no longer necessary to use the ADODB object in VBScript as described below. See Using Macro Scheduler’s Database Functions

The best way to access (retrieve, modify, or insert) data from a database with Macro Scheduler is to use VBScript and the ADODB object. This lets you access any SQL data source which provides an ODBC driver. E.g. Microsoft Access, Oracle, Informix, Microsoft SQL Server, MySql, etc.

We have a tutorial which shows how to access and update data in Microsoft Access using ADO. It is on the VBScript Resources page, here.

Recently I created an example showing how to retrieve data from a CSV file and insert it into two Access tables. This was in response to a post on the forum. You can download a zip containing a sample CSV file, the Access database and the script file here. To make it work you just have to change the path in the script.

In reality, as I mentioned in my post on the forum, it would be just as simple to create some Access queries to do what this example does within Access itself, but there may be reasons why you’d need to do everything from within a Macro Scheduler script and the demo is valid for any database, not just Microsoft Access. You may want to insert data into a SQL Server database, or a MySQL database held on another server for example. And this may need to be part of a greater automation process.

The examples mentioned here work with Microsoft Access. The code is almost identical for any other type of database. To work with a different database provider you just need to change the “connection string” which is used in the MyDB.Open line. The connection string can be just a data source name if you have a system data source set up. If not it needs to identify which datasource driver to use, where the database is and, if necessary, a username and password to access the database. Depending on the type of database there may be other paramaters that can be used to specify how to access the database.

How do you find out what the connection string should be? Well, the documentation that comes with your database server software should tell you. But if you can’t figure it out here’s a really useful site I found which lists a whole bunch of different connection strings for different types of databases:
http://www.carlprothman.net/Default.aspx?tabid=81.

The only other thing that may need changing is the syntax of the SQL itself. SQL is fairly standard but there can be differences between database providers, and some proprietary statements and functions that may be specific to one particular database.

For more examples try searching the forums for the term ADODB.

Become an Affiliate

Filed under: General — Marcus Tettmar @ 9:05 am

The people we outsource our payments processing to, Plimus, also run an affiliate sales program. So if you find yourself recommending our products to clients, friends or colleagues, why not sign up as an affiliate and have folks click through your links to receive 10% when they purchase.

Once you’ve signed up as an affiliate with Plimus, find Macro Scheduler under “Product Catalog” and click the “Sell this Product” link.

Plimus carry hundreds of other titles too, so you could earn commission on all sorts of other products from other vendors. Don’t forget to add your affiliate links to your website, if you have one, and tell people how useful Macro Scheduler is.

February 17, 2006

February 16, 2006

Scripting File Upload Boxes

Filed under: Scripting — Marcus Tettmar @ 7:02 pm

I helped someone today who was trying to record a web form that performed a file upload. File uploads are handled using an INPUT tag of type FILE. Unfortunately it turns out that these can’t be scripted for security reasons. Microsoft has prevented applications from setting the value property of these types of object. The solution was to send keystrokes to the Internet Explorer window to simulate a user entering the file name in the file box. You can view a demonstration of how to modify the WebRecorder code to handle this here.

February 15, 2006

Seventeen Minutes with Bill

Filed under: General — Marcus Tettmar @ 6:50 pm

Interesting video interview on Channel 9 with Bill Gates. Find out what he thinks about the Web and what the future holds for software. Microsoft have really become much more transparent lately. Channel 9 is a great way to get an insight into what goes on at Microsoft and meet some of the key people. Watch the interview.

February 14, 2006

Getting Data From Dynamic Web Pages

Filed under: Scripting — Marcus Tettmar @ 6:38 pm

I’ve just posted an example in response to a message on the forum where someone needed to extract data from finance.yahoo.com. The easiest way to create code to extract data from a page is to use WebRecorder‘s Tag Extractor. Unfortunately, finance.yahoo.com shows random adverts above the data and the HTML used to display the adverts is not always the same. What’s more the adverts are not always shown. This results in the HTML of the page being dynamic and the number of table elements can be different each time the page is loaded. Therefore the code that gets data from a specific table element may not always retrieve the correct data.

My proposed solution is to have the script search for the start of the data table. If you go to http://finance.yahoo.com/q/ae?s=UPS you will see that the first element in the table has the text “Earnings Est”. So the script uses the ExtractTag function in a loop. The loop starts with tag TD, index 30. If the content of that element is “Earnings Est” it stops, if not it loops back, incrementing the index. So on the next iteration it looks at TD31 and so on. When the value “Earnings Est” is found it jumps out of the loop and we now have the index of the first TD tag in the data table.

Now all we need to do to extract the data we want is to apply an offset to the located index. E.g. the value for Avg. Estimate, Current Qtr is in the 6th cell after the starting TD. So we add 6 to the index and use that with ExtractTag to get the data. And so on.

For the code and my explanation see the forum topic How do I pick numbers off a webpage?

February 10, 2006

WebRecorder 1.67

Filed under: Announcements — Marcus Tettmar @ 7:41 pm

I noticed an error that had crept into 1.66 when it was released which was preventing downloads from being recorded. This was basically because we’d gone and left some diagnostic code in. Our bad. Slapped wrists and all that. This has been fixed and 1.67 has now been released. Registered users can download from the registered download area. Or grab the evaluation version here.

We’ve also been working on a version of WebRecorder which creates Ruby scripts using the Watir library. More info on Watir WebRecorder can be found here.

February 7, 2006

Macro Scheduler Success Story – EPA

Filed under: Success Stories, Uncategorized — Marcus Tettmar @ 9:38 pm

By Guy Tanzer

Environment Protection AgencyMy name is Guy Tanzer, and I live in the Raleigh area in North Carolina, USA. Nearby is the Research Triangle Park, with the highest concentration of IT people and jobs in the eastern United States. I’ve been working with computers since 1977 and been in the business since 1984.

In early 1997, I started a job as a computer-support contractor to the United States Environmental Protection Agency in the RTP. One of the first tasks our team tackled after my hiring was migrating our lab of 350 users to Windows 95. To speed the process, my managers wanted to automate all the application software installs down to “one-touch” processes, which we could pick off a menu. The other team members got Lotus 1-2-3, Freelance, Word Perfect, etc., and Netscape 3.01 Gold was handed to me.

Now, Lotus and Word Perfect products came with documentation on how to write scripts to do automated installs. With Netscape, all we got was the .EXE file, with no docs on how to automate the install. A search of Netscape’s website turned nothing up. A call to them revealed that they did indeed have a solution, and we could buy it for only US$10,000…..

Well, that went a mite over the contracting company’s budget for solving this problem, if you get my drift. I needed a product of some sort that could wait for a window to open and be able to click on buttons, fill in fields, and so forth – just as if a “ghost in the machine” were watching and running the process for me.

I searched high and low for several weeks and tried perhaps eight different products, with poor results, before by chance I found and tried Marcus Tettmar’s MACRO SCHEDULER, which had only just been released. It did the job, and fit the bill, as elegantly and reliably as any product I’d ever used on a computer. Since we were one of his very first customers, Mr. Tettmar generously let the EPA lab use it on all their machines for the cost of a single license – only US$40 at the time. In return, I pushed the envelope on Macro Scheduler and kept in touch with Mr. Tettmar, giving him tips and ideas.

I used Macro Scheduler for at least a dozen more software installs, combining it with DOS batch files and compiled Quick BASIC programs to give it an incredible degree of flexibility. (I’ve since left behind Quick BASIC – it doesn’t handle advanced file handling systems – but Liberty BASIC is very similar to QB and another very worthy software investment. I find Visual BASIC to be clumsy and poorly designed for the types of scripts and programs I write in comparison to Macro Scheduler and Liberty BASIC.)

ANY software install or process which doesn’t give you “hooks” into the process, or a way to script an automatic run, can be attacked and solved with Macro Scheduler – quickly and easily. As I said, it’s “the ghost in the machine” in a positive way. It can watch for a window to open, and react to it – filling in fields, clicking buttons, whatever is needed. All the commands are simple, self-evident and in English, without the clumsy VBA scripting commands like:

GoAroundYourAssToGetToYourElbowJustToPushADamnButton
("Cincinatti",13,X,,"The Larch",,,,-.2E33,,42)

The one real problem I ever ran into was very specialized, yet Mr. Tettmar addressed it quickly and reliably. The problem was with Microsoft (surprise!) Powerpoint Viewer installation. Usually one keeps track of progress in a procedure by keeping track of the title bar in each open window; most software installs have unique title bars for each window as it opens. With the Powerpoint Viewer install, three or four consecutive windows had the same title bar and Microsoft, of course, offered no way to automate the install themselves. I asked Mr. Tettmar if there was a way to check to see what text appeared INSIDE a window, since each step in the Powerpoint Viewer install had unique text inside the uniformly-titled windows. Within a week, he came up with the FindWindowWithText command which worked perfectly in every application I used it on – Word Viewer, Excel Viewer, and I don’t even remember what else.

The benefits of all of this? I saved a fortune for the EPA and my contracting company. I saved boatloads of time working with a simple scripting language that is actually written in something extremely close to English, doesn’t have complex syntactical demands, and works like a charm. Mr. Tettmar has stayed with the product for nine years now; he isn’t a fly-by-night operator. He’s a good man, friendly, willing to help with obscure issues and find fixes for them. We’ve been email friends as long as I’ve been his customer. If I ever make it to “that side of the pond”, I’m buying the fish, chips and brew.
_________________
Thanks Guy. I’ll hold you to that offer!

If you have a Macro Scheduler Success Story I’d love to hear it. Drop us a line.

How Does Macro Scheduler Help You?

Filed under: General — Marcus Tettmar @ 1:29 pm

I’ve resurrected an old thread in the forums called “Tell us how Macro Scheduler helps you, what you use it for”. It’s now a sticky so that it won’t get buried away again. We’re always interested to hear how Macro Scheduler is being used and how it benefits people. It’s helpful for other people to read examples of how Macro Scheduler is used in the real world and how it benefits businesses. So please dive in and write a few lines to explain what Macro Scheduler does for you.

« Newer Posts — Older Posts »