WinAutomation Winners

Sorry folks, I intended to post this yesterday, but I’ve been having issues posting to my blog from Word.  Somehow it’s just stopped working.  I have a full post ready to go, but it’s just not publishing for some reason, from either Word 2007 or 2010.  It would seem to indicate that the issue is server side, so I’m going to need to work that out.

At any rate, the winners of a free license for WinAutomation, randomly generated from the list of entries are:

  • Dennis
  • Sam
  • Eric

I’ll be in touch to let you know how to claim your licenses.

Hopefully I’ll be able to get my posting abilities back from Word, as this is a bit odd.

WinAutomation

A few weeks ago I received an email which read in part:

I’m writing to you to make you aware of WinAutomation (www.WinAutomation.com), our software product (that includes a powerful macro recorder) that helps you automate your repetitive tasks (e,g. files, folder, database, web actions etc), so that you can save time, effort and labor.

In the name of full disclosure, the person offered me a pro version of their software if I’d like to blog a review of it. Actually, they even went further and offered me some licenses to give away to blog readers as well. It sounded like a pretty good deal to me, so I accepted.

Now, before I really give a review on this, I think it’s worth acknowledging that I’m not your typical user. When I feel like automating a task, I typically write the code to do it. I’d also say that the majority of those tasks are inside office, and I do very little in the way of repetitive tasks at the Windows Shell level.

WinAutomation does offer the ability to automate Excel to some degree and has an “Execute SQL Statement” ability to work with databases. Neither of those are any interest to me though, as I’d do both through VBA. I really don’t do any repetitive web actions either… unless you count opening bookmarks. So what could this offer me?

Basic Scripting

One thing that I was looking to accomplish was file sharing. I use Windows Live Mesh to write a critical file up from my laptop to the cloud and share it with a couple of people. The problem I had is that this file also needs to exist on the network, and I don’t want to install Windows Live Mesh on my file server. So I needed to come up with a way that I could copy this file to the network every time it was changed. WinAutomation does actually accomplish that need for me.

The interface doesn’t take too long to get used to, and in a short time I was able to knock up the following script:

Long story short, this checks if my network exists and copies the file to the network if it does, or notifies me if it isn’t copied. The script is set up with a trigger to monitor the source file and runs each time the file is changed. And I get a notification popup with the result:

This is pretty cool for me because LiveMesh syncs the file to the cloud, and WinAutomation copies it to my local server. With a Lenovo feature I can run an exe each time I log on to a specific network profile, and WinAutomation can compile the macro to an exe file for me.

Of course, if I don’t want to go the exe route, I can set up a trigger in WinAutomation to monitor the system’s “Application” event log and run the script each time a certain event is triggered. It gets a little tricky though, as you need to find an event that fires consistently after WinAutomation starts up, but it seems to me that the WinLogon 4101 event should work:

Variables

I am sorely disappointed in the variables that are offered. In the job above I really wanted to just set the files to variables at the beginning of the routines so that I could check properties of the files. I couldn’t seem to do that, though. For example I set a file into a file variable and a folder to a folder variable. Then I wanted to check if the file existed inside the folder… it was a non-starter, and pretty frustrating.

Macro Recorder

WinAutomation also has a Macro recorder. I tried recording a macro which:

  • Opened Notepad
  • Typed in some text
  • Opened Outlook

The results are below.

Honestly, I was a little disappointed in this. Maybe it’s because I’m used to using VBA and controlling an Object Model, maybe because I actively avoid using SendKeys… I picked Notepad off my “Most Recent” list, so I really wonder what happens when it changes position. Based on what I see below the macro would stop working… I don’t claim to be smart enough to offer a solution though, but then I didn’t write the program.

Recording file/folder actions through Windows Explorer yields the same issues with mouse clicks and SendKeys as those above. I find it a little weird that there is no warning to the potential of failure if the window wasn’t open in exactly the same place.

I can give an example of where the macro recorder is pretty useful though, despite the limitations above. You can set up a keyboard trigger (CTRL + ALT + SHFT + F for example). This worked well for some data entry I had to do when I was fixing a loyalty program setup today. I had to enter a category number and then enter data in about 12 columns for each category. The rub was that only the category number changed, and the rest was consistent data. So I recorded a quick macro, hooked it to the keyboard shortcut and then just had to enter the number and hit the key combo to fill the line. Much better. :)

Setting Triggers

The program certainly does have some power to it, and the selection of objects that you can program is fairly large. I didn’t have too much trouble creating jobs through the designer to do most of what I wanted, although working with the variables seemed pretty clunky to me.

The hardest part of working with this software though is coming up with the appropriate trigger for your macro. Here’s a list of the available triggers for completed jobs:

So the thing is that you need to get creative with how you set up the trigger. And what you want may not actually be there, so you may need to look at another route. For example, I thought about setting up one job with a ping trigger… but the ping trigger here only fires when the host doesn’t respond. I kind of wanted it where the host did respond. So I’m left hunting for another way to figure out if the server exists.

One case in point… Assuming that you are not using Windows 7 or a Lenovo computer, (both have built in functionality for this,) you may want to set a different default printer when you connect your laptop at home vs work. Creating the actual macro is super easy as you can see:

The real question lies in how you set the trigger… one way is shown below:

In browsing my event logs though, it looks like you can have multiple entries in the event log for WinLogon, so maybe it’s better to monitor a specific outcome. This one will kick off each time the Windows license is validated, which I think only happens right after you log on:

At any rate, setting triggers is the toughest part of this program. It takes a bit of getting used to, and can be pretty frustrating when you first start. A little persistence pays off though, and really starts to unlock some deep power.

Conclusion

I went through some ups and downs as I was learning to work with WinAutomation, with it sometimes feeling impossible to make it actually return the true potential it should offer. Building macros is actually very easy using the drag’n'drop interface provided. But the real key is to not give up on the triggers portion and look at things in different ways until you find one that works.

To me the “SendKeys” style output of the macro recorder is a big disappointment, but despite that, it still offers the ability to knock up some quick data entry features in programs that don’t otherwise support any automation. That value cannot be overstated as it can save you tons of time.

The more I work with this program, the more I do find uses for it.

Free Licenses!

So… would you like to try it? They have generously provided me with 3 licenses to give away.

Here’s how it works…

  1. Send me an email at ken at excelguru dot ca with the following subject: “WinAutomation Giveaway”. (If you don’t get it right, then you won’t be entered. ;) )
  2. I’ll take all the valid entries received between now and midnight Pacific time on Tuesday, October 27, 2009
  3. All valid emails within the timeframe will be entered in an Excel spreadsheet
  4. Each entry will get a RAND function beside their name
  5. I’m going to sort them, and whomever comes out in the top three wins

New article on the site

Over the past… ages… it seems like I haven’t been able to my site, but I got a new article up today. It’s a variation on my Count Files (with a specific extension or not) in a folder entry. It doesn’t seem at first like a big departure, but I’ve posted a version now that will also count files in both the folder and subfolders. It was an easy fix, requested by a reader. The big difference is really that it uses recursion to keep calling itself to dig deeper in the folder structure to get the info.

At any rate, if you’re interested, you can find it here: http://www.excelguru.ca/node/106

I need a GOOD sharepoint Wiki template

One of the things I really encourage my staff to do while they work here is to create complete procedures manuals for their jobs. We’re talking about full documentation with step by step instructions on how to accomplish their tasks, complete with screenshots where applicable. And while many new staff members may find this a little strange, they quickly realize the worth when they can pick up a manual from their predecessor and start to work through their new job. So far I don’t think I’ve had anyone who has dismissed the value of these documents.

The concept also plays into other areas in our systems which probably won’t come as a surprise to anyone:

  • If someone is sick, a co-worker can pick up for them while they recover,
  • When training, it takes less time to re-familiarize yourself with the material to show the person,
  • Often time you can let the person work through stuff on their own without impacting your time, and
  • In slow periods there are always manuals that need to be updated, so there is less pressure on your time trying to find someone meaningful work to do.

I’m also sure you could come up with a few more points, but I figure those are enough to keep the motto of “Document It” alive in my workspace.

There are, however, some dangerous drawbacks that can come in to play here, and these typically surface when someone leaves the organization. That list includes, but certainly isn’t limited to the following:

  • Procedures that have changed, and the manuals not updated to reflect those changes,
  • Multiple copies of procedures being left in the manual,
  • Not being able to find the appropriate manuals, and
  • People “going through the motions” in their job without ever really understanding what they’re doing.

The last one is, of course, the supervisor’s job to identify and resolve, so I’m not going to focus on that at all right now. I’m more interested in the first points.

We recently had someone leave us. She’ll be missed, of course, but my team has really bonded and pulled together to make things happen. Fortunately in many cases we were able to simply pull the book off the shelf and run, but there were a couple that gave us pause…

We naturally rooted through binders and other files and ended up coming up with multiple procedures in some cases, or not all in others. Then the question surfaces on which is the correct procedure, or if any of them are. In our case it wasn’t always obvious, so we had to spend time working through them to tell. Naturally this kind of defeats the purpose.

In the cases where the procedure is out of date, (either because it subtly grew over time, or radically changed and new documentation wasn’t yet prepared,) it needed revising… but where was the original document to make those changes? Was it stored in a common folder, private folder, semi-private… who knows? In some cases these procedures had been written by a former staff member and their binder was divided up between people when they left… so now we have procedures that may be stored anywhere on our network.

This got me thinking about how we organize and store our manuals, as well as updates. We could use one specific folder on our network, but then we get into issues with naming conventions and finding the documents within the subfolders as well. “No,” I thought… “the perfect solution would actually be a Wiki!”

A wiki, by its very nature is a collaboration tool that allows shared editing, complete with versioning and rollback features. This would be great for us, as we could store the entire set of procedures in one common place, share it among all our accounting staff, and let them edit the entries if they found that they were out of date. (Of course we’d encourage them to keep them up to date, but let’s face it… we’re all human.)

I figured this would be an easy slam dunk, actually. We have Windows Sharepoint Services 3.0 (WSS3.0) installed on one of our servers, although we’ve never used it in earnest. I installed the default Wiki template that comes with WSS3.0 and gave it a run…

It SUCKS! I mean REALLY sucks!

The whole point behind this, to me, is to make it really easy to create rich entries. It looked good at first, when I went to edit a page and saw the nice editing toolbar:

So I started typing away, then went to insert a picture:

Wtf? I was seriously hoping for a Browse button, not to have to hand type in a URL. Not to be discouraged, I tried to paste the picture directly into the text, but it was a no-go either. The route you have to go for each picture you want to insert is:

  • Snap the shot
  • Save it as an image
  • Navigate to the Sharepoint Image Library
  • Upload the picture (and optionally name it)
  • Copy the link to the picture
  • Navigate back to your entry
  • Fill in the appropriate URL info
  • Hope it works when you click OK. (My test did not.)

Doesn’t that seem just a little awkward? I need to put this into the hands on non-programming type folks. I think the concept of the Wiki has a LOT to offer us, but the implementation here is brutal!

When I’m blogging to WordPress from Word I can just write up the document in Word, complete with pictures, and just publish it. It auto-magically uploads, resizes and embeds them for me. Sweet and simple. Why can’t this be the same? Or can it?

Despite my searching for over an hour, I have not been able to find another Wiki template that works with WSS 3.0, or a webpart that will allow for easy picture uploads. If anyone knows of one, I’d love to hear about it. I’m not above actually paying for them either, but I’d prefer to take them for a test drive first, as I’ve committed to my staff that we won’t go this route unless it is user friendly. They don’t want to become programmers, and they shouldn’t need to.

Our requirements are actually pretty simple. We need:

  • A simple text editor capable of:
    • Basic formatting such as Bold, Italics, Underline, Colors
    • Controlling font size for headings
    • Bullets
    • Numbering
    • Tables
    • Creating hyperlinks (without having to hand write HTML code)
    • Adding pictures in-line with text (without having to do the dog and pony show I described above)
  • A decent search engine
  • A Simple method for linking new pages into the Table of Contents
  • Versioning
  • It to work with Windows Sharepoint Services 3.0 (WSS3.0)

Bonus points for:

  • Being able to author the initial copy in Word
  • Styles
  • Workflows for approval of new/edited entries
  • RSS for updated entries

If anyone has any thoughts on this, I’d love to hear them.

Flowchart Programming

This has to be one of the weirdest little programs I’ve ever worked with…

Back in 2003 I built a database to track our liquor inventories at work. It’s fairly simple in that it has a table of product details like name, price, size, cost, etc… and a table that records my inventory counts. We used a little Palm Pilot with a built in Symbol scanner to collect the scan date, UPC code and quantity on hand. As we estimate our partial bottles that sit on the bar, we record decimal quantities on hand. (i.e. 2.1 bottles.)

This has worked really well for the last few years, but as with all technology, it wears out. While the database is fine, the Palm Pilot is another matter. The screen calibration went so far off and despite several resets I couldn’t fix it, rendering it useless. The bad news, of course, is that the unit we used has been discontinued for about 2 years, and my last replacement unit… well… it’s now in the trash.

Fortunately I already had a new device on hand to use. We’d installed a new system in another department last year that came with a handheld ruggedized scanner. Very nice little unit made by Unitech (HT-630). We were so impressed that we bought a second one. One reason was that we now have a backup unit, but the other is that the vendor (whom I know fairly well) convinced me that I could use this as a replacement for my aging Palm units. “Oh yeah,” he told me, “it’s fully programmable. You’ll have no problem knocking up a little program for it. There’s a whole programming environment, you build the job, upload it and you’re good to go. It won’t be a problem for you.”

I love that people have that much faith in me sometimes… then there’s other times where I want to phone them up and…

At any rate, I’ve been meaning to get around to programming this new unit, but just haven’t been able to make it a priority. But since our little Palm Pilot blew up, that kind of forced the issue. And naturally it had to happen two days before quarter end. Great. So last Thursday/Friday I spent time trying to get this thing working in time for them to count inventory on Saturday. Nothing like a little pressure!

The odd thing is that this unit is programmed by building a flowchart. I’ve never seen the like before. To me flowcharts are used for documenting how the program works, not telling the program how to work. Very odd. The shot below is from the program I released on Friday to count inventory. (Yeah… I DID do it, but I wouldn’t say it wasn’t a problem!)

This was a real trip to work with. Ever seen a flowchart that is allowed to go multiple directions at once? I haven’t. To me flowcharts have a decision point between every directional choice, but this little program actually flows data along one arrow and the input screens along another one.

And well creating the flowchart is all good, you then have to configure every option of every node in the chart. You can’t copy an existing node that is setup correctly either… each option of each box has to be configured. Each link, each input, everything. This is just one of at least 14 screens to configure. Each line you see (incoming and outgoing) increases that count by 1, (image below has 20 screens to do) so it’s very tedious:

It took me about 1½ days to built this program. I spent a ton of time spinning my wheels trying to figure out how to make a lookup file work, then trying to figure out how to write the data to the text file, then trying to figure out how to upload the program to the unit, and then I thought I had it. Then I reviewed the collection file and figure out that it was submitting a full record every time a single piece was captured. So I’d get four records for every UPC code, one with the correct quantity, three with the prior… arrggh!

I also capture the count date and month end date. I was really happy to see that you could use a formula too, so I figured that I could use a formula to convert the count date to the month end date. I quickly jumped into the formula screen… and after staring at that for about 4 seconds I clicked the Help button:

Seriously? “Write pieces of C code”? Try web searching for some help there… even when I did find something, I couldn’t figure out how to make any sense of it!

In Excel this would be easy…. =If(Day(CountDate)<15,Eomonth(CountDate,-1),EOMonth(CountDate,0))

If anyone has any idea how to write that in C code, I’d be really grateful. There is no manual with this software and the “Help” files are about as useful as… well… they’re not. I ended up ignoring the formula and elected to capture the count date twice. I just have to edit the text file and replace one of them with the month end date right now, and I’m good to go.

At any rate, I DID get the program working in time, and deployed it out there. The staff are ecstatic as it is much simpler to use than the last one. Yay, it’s time to celebrate!

Almost.

Today I was trying to import the text file into the existing table in Access. Access is causing me a hell of a hassle as it wants to populate the AutoNumber index field with my text file data. I don’t get that. It was never an issue before. I even put headers in my data fields, but no luck. I may have to resort to VBA to use a SQL insert statement in a loop to get this licked.

Worse though is that I messed something up. All of the decimal counts have been truncated to whole numbers, which makes my count file useless. I HATE having to deploy new technology in a hurry without proper testing. Our only recourse now is to estimate our inventory this month end and deal with fixing the technology issue for next month end.

At any rate, this has been an interesting learning experience. Programming C using flowcharts… it’s just plain weird.

Renewed for another year

I was really happy to see an email from Microsoft this morning to let me know I’ve been awarded as an MVP for my 4th consecutive year. :)

I’ve been pretty quiet since a couple of days into my vacation… partly because I was too busy enjoying it to blog, partly because I was back full on into work upon my return. I came back to work jetlagged to push through month end, then budgets, and now we’re back into a quarter end. After that, since it’s our “slow season”, we’ll be pushing through systems installs as well. Life is not dull, but it isn’t affording me much time at the moment to publish new stuff.

I’m hoping to write up something soon that describes the budget project I worked on, as it’s been a really cool one, and have a software review in the pipe right now too.

I need to get onto work now, but hopefully the blog won’t be quiet for long…