Power Query Dependencies Viewer

The November 2016 update is now out and it finally brings a way to view the Power Query dependencies viewer.  While it’s been out in Power BI Desktop for a while, (as Matt posted about a while ago,) this is huge news for Excel, as this feature has been badly needed.

Viewing Power Query Dependencies

To get to the Power Query Dependencies view, you simply need to perform the following steps:

  • Edit any query (just to get into the Power Query editor)
  • Go to the View tab
  • Click the Query Dependencies button

image

Once you do so, you’ll be launched into the Power Query dependencies windows as shown below:

image

At first glance…

So at first glance, this is pretty exciting and yet – if you work with complicated Power Query setups like I do – you’ll find the Query dependencies view a bit… lacking in some areas too.

First off, if your query is complicated, it really does open that small.  Wow.  Now there is a scaling button down the bottom, but that quickly scales so that stuff is off-screen.  No problem, right?  We’ll just drag some stuff around then… oh… except we can’t.  Dragging any box around drags the entire model, not just that one box.  Sad smile

What can you do with the Query Dependencies viewer?

Rather than focus on the stuff we can’t do, I want to take a look at what we can (although I won’t be able to help making a couple of suggestions here as well.)

Maximizing the model layout

The first thing to point out is that despite the fact that it isn’t obvious, the window is resizable.  If you mouse over any border or corner you’ll get the arrows that indicate you can left click and drag to make the window bigger.

So normally the first thing I do is:

  • Move the window to the upper left of the screen
  • Drag the bottom right corner to make the model fill the entire screen
  • Click the little box icon in the bottom right corner by the scroll bar to “Fit to Screen”

After all, the reason I’m using this view is because the models are big!

Some things that would be really useful here:

  • It would be awesome if there was a Maximize button near the X in the top right (like the Power Query window and every other app has.)
  • It would also be good if we could double click the title bar and have it maximize the window (again, like so many apps out there.)

Either (or both) of those features would save me a lot of time.

Alternate Views for Tracing Query Dependencies

In the default view, the data sources are plotted at the top, and the queries cascade down below.  Fortunately you’re not stuck with this view, there are four different ways to display the model:

image

In this instance I’ve chosen Left to Right, which puts the data sources  on the left and fans the query dependencies out to the right hand side.

Honestly, if I had my preferred way it would probably be to use Bottom to Top (data sources at the bottom and data model tables on the top.)  To me this should basically “bubble up” the model tables to the top of the screen.  Unfortunately it doesn’t quite work like that… all we can guarantee is that the data sources will be at the bottom, but the model tables could end up anywhere.

Ideally, I’d love to have an option to force the Data Sources to be lined up based on the first choice in that menu, and the Load Destinations (whether table or data model) be lined up in the viewer based on the option chosen for the second choice.  This would allow me to easily see the “From” and “To”, with the chain of what happened in between.

Tracing Query Dependencies

In the image below (click on it to see the larger version), I’ve selected one of the tables in the middle of the query dependencies tree:

image

The effect is that it highlights all child and dependent queries in the data flow.  That’s cool, and I’m okay with this being the default behaviour when I select a query step.  Wouldn’t it be cool though, if we also had:

  • A right click option to trace precedent queries only
  • A right click option to trace dependent queries only

Those would be super helpful in tracing a queries flow without the extra noise, something that is really important in able to quickly dig in to the key factors you probably want to know about your query dependencies.

Identifying Load Destinations

So the very first thing I did when I threw this specific model into the query dependencies view was identify two queries that were not in the query chain.  “Awesome,” I though, so I went and deleted them.  Then I restored from backup, as one of them was in use!

Don’t get me wrong, the view was correct, it’s just that the distinction for load destinations is so weak that I saw no arrows and assumed it was good to be removed.  As it turns out, the words actually matter here:

image

The Day Types table is created from a hard coded list.  Since there are no queries flowing in or out of it (it is floating above the lines) I nuked it.  I missed the fact – especially with it being on the left), that it was actually loaded to the data model.

Raw Data-Departments, on the other hand, is pulling from the Current Workbook and is loaded as “Connection Only”.

So here’s my thoughts here:

  • I’d love to see nodes that are loaded to worksheets or the data model identified.  Either an icon in the top right, or a shading  in place would be ideal.  Something that makes them a bit less subtle than they are today.
  • I’m not a fan of the “Not loaded” term… it’s about as awesome as the “Load Disabled” that Power Query used to use about two years ago.  This should – in my opinion – be consistent with the UI and should read “Connection only”.  Not loaded makes it look like it isn’t working.

Navigating Query Dependencies

One of the issues I had above is that my Day Types table – being standalone – should not sit on top of any arrows… that’s just scary bad and misleading… but that’s actually part of a much bigger issue as this is kind of the style used throughout the entire tool:

image

This also leads me to another issue in that I need to be able to follow these arrows.  Today the only ability you have – because you can’t move the boxes – is to essentially print the query dependencies window (you’ll need screen capture software for that since there isn’t a print button) – and trace with a highlighter.

What I’d love to see in this instance is the ability to select a single (or multiple arrows) and have them turn bold.  It would be an even bigger bonus if they shaded the tables on each end of the arrow and allowed you to select multiple arrows.  That would actually solve a few issues mentioned earlier too, allowing us to really drill into the relationships we need to trace.

Overall Impressions of the Query Dependencies Viewer

Overall it’s a good first version.  I’d really love to see some (or all) of the improvements I mentioned above, but it’s a HUGE amount better than what we had a month ago.  Smile

MZ-Tools 8.0 for VBA

One of my favourite add-ins of all time just got an upgrade, and I’m super stoked about it.  Why?  Because I can use it again!

As I began my VBA journey, there were two add-ins that I used all the time:

Both were invaluable, with SmartIndenter allowing right click access to re-indent code, and MZ Tools providing a TON of useful content.  (My favourite was the error handling template I could just inject with a couple of clicks.)

It became painful to work on or debug VBA code on anyone’s PC who didn't’ have these tools installed, and the became part of the default installation routine for my machine.

Why I’ve been Add-in free for years

Unfortunately, both MZ Tools (3.0) and SmartIndenter were written in VB6, which meant that they were restricted to the 32 bit versions of Excel.  And that meant that the day I started using Power Pivot, I lost the ability to use either add-in.  (Okay, to be fair I could have stuck with 32 bit Excel for Power Pivot… except there was no way I was doing that.  The need for more memory accessed trumped the tools that made my VBA life easier.)

I’ve now been running without the aid of these tools for about 5 years… which is shocking… and STILL miss them.  A few times over the last few years, I even made some attempts to replicate some of these features on my own, but I could never figure out how to get VB.NET to hook into the VBIDE, so gave up on it.  Instead I focussed on tools I could control, building add-ins and software in other areas.  (It always irked me that I couldn’t figure out how to hook the VBIDE though!)

No longer Add-in Free

For that reason, I was pretty jazzed when Carlos Quintero emailed out to say that he’s updated and released not only MZ-Tools for Visual Studio, but also MZ-Tools 8.0 for VBA.  That is FANTASTIC.  I’ve downloaded it, got it installed, and am already digging through the loads of features to customize my templates.

Unfortunately I’m not such a good judge of what’s new in this version (my memory of it is five years out of date) but here’s some of the stuff that I’m looking forward to (re-)acquainting myself with:

  • Dead code review.  I’ve already scanned a couple of my add-ins and found unused variables and unused routines that can be trimmed.
  • Statistics.  Kind of a vanity thing, maybe, but I’ve always wondered how many lines of code are actually in my XLGFileTools add-in.  As of today, the answer is 6,726.  (Maybe a couple less once I review the Dead Code report above)
  • Code templates:  I can’t wait to rebuild the error handling template.  I also remember in the past the ability to insert a comment block at the top of each routine/module very easily for documentation too.
  • The simple thing of being able to right click the Immediate window and choose Clear.  Oh my how I’ve missed you!

These are just some highlights, there are obviously tons more.

Worth the cost

if you look back you’ll see I don’t endorse many products, and certainly not as passionately as I am here.

The goal of MZ-Tools is to make your everyday programming life easier.  I 100% believe that it does that, and that it is worth the cost to purchase it – something I don’t say very often!  (Understand I’m not making any commission or advertising revenues off this, either.)  The software is just that good and useful.

But even better, if you are in the market for it, Carlos has a 50% sale on through the end of October.  That will save you $40 off the regular $79.95 price tag.  How can you beat that?

You can find it at http://mztools.com/index.aspx 

Happy coding!

A review of Add-in Express

A while ago I started transitioning from VBA to VB.NET again, attempting to build a tool to manipulate the new Power Pivot components in Excel 2013. While I was able to get part way to a working solution using Visual Studio 2012 and VSTO, I ran into two key issues:

1. VSTO seems to be “bit” specific, meaning that I would need to keep one version of the solution for 32 bit versions of Office, and another for 64 bit versions, and

2. While I could run my code, the Power Pivot engine would crash on me unless I opened Power Pivot BEFORE I ran any of my code.

Both of these issues were rather severe to me, as I didn’t want to maintain multiple versions of the same code, nor could I release something and expect users to open Power Pivot before running my project. A friend of mine suggested I try Add-in Express to deal with these issues.

In the spirit of full disclosure, I contacted them and asked if they had a trial version. They don’t, but offered to let me trial it if I’d blog on my experiences. I agreed to do that, and what follows is my honest observations of the software.

I do want to preface that, like my friend Rob Collie, I am not a “Read the manual” kind of guy. Ironic, since I write a lot of material, but I take the approach of diving in, and hitting Bing for a quick pointer when I get stuck. It usually causes me a lot of pain, but I tend to learn better that way.

So, here’s how it all came together for me…

When you install Add-in Express, you get a new set of templates. I started my project by creating a new ADX COM Add-in targeting the .NET Framework 2.0. (For those not in the know, in order to target Excel 2010 with VSTO, you need to use .NET 4.0 and Excel 2013 is .NET 4.5. So .NET 2.0 is way too early a framework to have ever even heard of Power Pivot!)

adx

From there you pretty much follow the prompts through the setup. I called it “Connect”, I set the minimum supported version to Office 2010 (since PowerPivot didn’t exist in 2007), I chose a Visual Basic Project, I selected Excel, and I left the rest of the settings at their defaults. Pretty easy, that part.

Once I had a project to work with, I created a new VB Module, just like normal, and built the code I would need to refresh my PowerPivot model and all the PivotTables. (Be aware that, for simplicity of the post, this is 2013 specific code, and will not work with Excel 2010.)

The key piece in this is making sure the xlApp declaration is correct, as you need to refer to the AddinModule portion in order to bind it to Add-in Express’s handlers, instead of just binding to the Interop.Excel objects. That change is what makes Add-in Express work:

[vb]Imports Excel = Microsoft.Office.Interop.Excel

Module Module1

Public xlApp = Connect.AddinModule.CurrentInstance.ExcelApp

Public Sub RefreshPowerPivotTables()

Dim xlWorkbook As Excel.Workbook = xlApp.ActiveWorkbook

Dim ws As Excel.Worksheet

Dim pvt As Excel.PivotTable

'Attempt to connect

Try

xlWorkbook.Connections("ThisWorkbookDataModel").Refresh()

Catch ex As Exception

'Data connection could not be refreshed.

MsgBox("Sorry, but I could not refresh the model! Are you sure this workbook has one?")

Exit Sub

End Try

'Refresh all PivotTables

For Each ws In xlWorkbook.Worksheets

For Each pvt In ws.PivotTables

With pvt

.PivotCache.Refresh()

.RefreshTable()

End With

Next

Next

End Sub

End Module[/vb]

So far so good. Now I needed a user interface.

To be fair, it took me a bit to figure this one out. Once I finally realized that you need to open the “AddinModule.vb” portion in the solution explorer, then add a Ribbon tab to the canvas, then things got easier.

Despite reading very little documentation, with a little help from the blogs and articles on the Add-in Express site when I did get stuck, I was very quickly able to build a simple UI. There’s a good article on doing this here, which I wish I’d read earlier in the process.

As I say, it’s a really simple UI: a tab called “Model”, a single group, and a button with an image on it.

One criticism I do have is that it would be nice to be able to link the buttons and other controls to their callbacks inside the visual designer. As is, it’s a bit clunky, as you have to select the designer, then choose the other controls in the properties window. It’s not totally intuitive, but once you know where to look (read the article linked to above), it is workable.

My callback code for my button (which I didn’t bother renaming) is as follows:

[vb]Private Sub AdxRibbonButton1_OnClick(sender As Object, control As IRibbonControl, pressed As Boolean) Handles AdxRibbonButton1.OnClick

Call RefreshPowerPivotTables()

End Sub[/vb]

And with that done, it came time to debug. Again, fairly straight forward:

  • Build -> Build the project
  • Build -> Register ADX project
  • Make sure Excel is closed
  • Start the debugging engine

My “Model” tab showed up, with the command I’d built, as shown below:

onLoad

And with a single click (and a bit of a wait since Power Pivot is so slow), my Power Pivot data was refreshed, and the PivotTable updated to reflect the changes I made in my database. Notice the new customer and the new sales transactions for 8/1/2013:

onRefresh

But the best part is this:

  • The solution is deployable to both 32 bit and 64 bit Office platforms, and
  • I can open my Power Pivot project even if I run my code first, and it doesn’t crash.

I can honestly say that I fought that Power Pivot crash issue for about 2 months with VSTO, and I was really worried that it was going to kill my project completely. No amount of searching would turn up a fix, and other help calls didn’t yield any gold either; where they were answered, it was with a “don’t know” answer. Add-in Express has actually made this goal achievable.

I’ll also tell you that, while refreshing Power Pivot isn’t the focus on my full project, I have been able to use Add-in Express to successfully target and manipulate Power Pivot in both 32 and 64 bit versions of both Excel 2010 and 2013. I.e. multi version deployment with one code base. Pretty damn awesome.

I should also mention that their support has been phenomenal as well. Not only have they answered my emails, but I even ended up on a call with one of their lead people to examine why I didn’t seem able to use the debugging tools at first (a blog post for another day). 30 minutes, problem solved, and I’m good to go. Again, pretty damn awesome.

Readers of this blog will know that I don’t endorse very many products at all. Sure, I use Google Adwords and stuff, but I don’t write too many blog posts talking about how awesome a product is. Here’s my word on Add-in Express:

I’m sold. This product has been a life saver, and I won’t develop using VSTO.

A new article, a new site host, a Facebook contest, an old blog…

It's been crazy busy over here the past couple of weeks.  I've been hard at work creating 3 training videos for a client, (we shoot in Studio on Friday,) preparing for my next live course (this Saturday,) and trying to keep the forum posts all answered... among other things...

Excelguru has a new host

The biggest news, as far as the site is concerned though, is that we're running on a new host.  My former host kept sending me messages about the server usage, asking me to block users and shut things down, as I was too busy.  They suggested that, if I didn't want to do that, I should upgrade to their dedicated server plan.  I have to say I was a little shocked by that, especially when they told me it would move me from $4.95 per month to $164.95 per month.  While I love hosting the forums and site, that was a little much to swallow.

Instead, after some research and conversations with my friend Simon, I've moved the site to host with URLJet, a company that specializes in vBulletin and WordPress hosting.  They're more expensive than what I was paying, certainly, but what a difference in service.  I signed up and within 10 minutes they called me to talk through the migration scenarios.  The site migration was finished, tested and working within 4 hours, all done by them.  They reviewd it, made some performance tuning changes, and recommended a few for me to do too.  Very cool, and all done at no extra cost.  I have to say that so far I'm really pleased with them.

We've been running on URLJet for a week now, and the site seems more responsive and forum posts are picking up.  I'm not sure if that's coincidence (probably), but it's getting to the point where I could certainly use more people on the site.  So if you love Excel or just want to learn, we have posts that need answering!  🙂

The old blog is back!

As if moving my site and optimizing it for me weren't enough already, URLJet also fixed the WordPress blog that I've been locked out of for 1.5 years too!  While I was planning on using the vBulletin blog, WordPress is way more flexible, and I didn't want to lose all the old content so...

With this blog back live, I'm going to resume posting here.  I ported the two articles back from the vBulletin blog that were worth saving, so if you see those in your feed, just ignore them.  🙂

A new article

In celebration of the whole thing, I also created a nice new article on the site as well.  If you've ever been confused about how conditional formatting rules are applied in Excel 2007+, I've written up a VERY detailed, step by step article that will walk you through exactly what happens.  You can find that by clicking here.

Facebook contest

My facebook page at http://www.facebook.com/xlguru is creeping ever closer to 100 likes.  I'd really like to make that milestone, so I announced last week that I'd give away a copy of my Magic of PivotTables video course to the 100th like.

I have to admit that I was hoping I'd be there by now, but then I got the following comment from Rick Grantham on Twitter:

"guess I need to unlike your FB page so I can like it back at the right moment.  Maybe I shouldn't have told you that :)"

I had a good laugh there, but it did make me wonder... so I think I'll change the game up.  (All those of you who are already Facebook fans can thank Rick (@BIStrategyGuy) for this....)  Here's the new deal:

Be one of the first 100 likes on the xlguru page for a chance to win a free copy of my Magic of PivotTables video course!

Once I hit 100 likes, I'll draw a random name from the first 100 people, and then I'll send you a discount code to download a free copy.  It's just that easy.  🙂

See, now no reason to wait.  In fact, you might want to hurry, as there are 97 likes as I post this!  (But don't worry, this won't be the last one.)

 

PowerPivot And DAX Just Got A Whole Lot Easier…

As many readers of this blog will know, I am a huge fan of PowerPivot, and honestly believe that this is THE most important feature to hit Excel since VBA was introduced to the product. And anyone who has ever taken a course from me knows that VBA is the greatest thing to be added to Excel since the grid was mapped into rows and columns.

For those who don’t know, PowerPivot was a free add-in to Microsoft Excel 2010, and is now baked into the Excel 2013 release. It basically allows us to pull data from different sources (multiple databases, text files, web data feeds, excel worksheets and more) into a separate layer of our file, and create relationships between the resulting tables. Basically you can aggregate data from a bunch of different sources and EASILY aggregate it into your very own business intelligence engine.

The amazingly cool part is that it’s not hard to do this. With a little knowledge about relational data (and I mean a little), and knowledge of text functions like these, you can build the columns necessary to relate your data. From there you create the table relationship (PowerPivot even tells you if you try to build it backwards), and you’re off to the races.

The results of this are, quite frankly, groundbreaking. You can then build PivotTables off this data: PivotTables that are sourced from multiple tables at once. No more massive VLOOKUP tables to build one huge data table with everything. It’s simply no longer necessary. And with the variety of sources, you can even pull in and start adding items to your pivots that you never thought of before, like the weather for example. Does your corporate database have that? I doubt it, but who cares? Just source a weather feed from the internet, relate the dates to the dates in your sales table and presto! You can now see how sales were on the sunny days in a month vs the cloudy ones.

It’s amazingly easy to get some cool stuff out of PowerPivot… at least… to a point. To over-simplify things, I’d say that PowerPivot allows you to do four overall things:

  1. You can source and relate tables to build your own aggregated data source (a mini BI database if you like.)
  2. You can build PivotTables pulling in fields from multiple tables.
  3. You can build OLAP formulas, allowing you to pull data for a specific element right into the worksheet, without building a whole PivotTable to do it.
  4. You can build super duper wickedly complex and powerful calculated fields for your PivotTables using DAX.

To me, the first three were easy. As an Excel pro with a bit of relational database knowledge, I’ve been able to work through the first 3 over the past couple of years with very little in the way of roadblocks. And by doing so, I’ve built some amazingly cool things for our company. Yet despite this, I’ve always been aware that the real piece de resistance, the part that really makes things sing in PowerPivot is DAX.

What the heck is DAX? It stands for Data Analysis eXpressions, and is the formula language that you use to create what PivotTable users know as “Calculated Fields”. It is unbelievably powerful but, without tutelage, it is unbelievably hard. For almost two years I’ve been struggling to understand DAX.

That ended this past week.

Last week I bought a bought Rob Collie’s new book “DAX Formulas for PowerPivot: The Excel Pro's Guide to Mastering DAX”. When you are ready to take the DAX journey, you MUST have this book.
Rob’s writing style is identical to that you’ll find on his blog at http://www.powerpivotpro.com/. In fact, the only difference is that he doesn’t fill up any real estate with movie quotes. 🙂 What you do get is a huge amount of insightful information.

I can honestly say that, despite being a fairly accomplished Excel pro, I have been unable to wrap my arms around DAX. But that is over. Four days of working through this book with my own data and I am now making some damn cool stuff that eluded me before.

What I love about this book is that Rob sat down and carefully thought through how an Excel pro should learn DAX, then started at the beginning. It progresses logically, is very easy to read, and actually gets you there. Yes, you need to practice, but weren’t you going to do that anyway? And when you practice…

There’s something really important between these pages that I doubt you’ll ever see in the advertising. With any programming language (yes, Excel formulas fit that bill, as do DAX formulas) there are two critical things to learn. The first is Syntax (how you do something), and the second is how to debug something. Rob gives you both, which is critical on the path to mastery. He has some nice little charts that explain how filters are applied to the CALCULATE function, (which is like SUMIF on steroids.) With those charts, you can actually follow the steps to figure out exactly why your measure is not doing what you want. (Trust me, it was doing this that actually helped me finally get that Eureka moment that I’ve been looking for!)

Four days. I’ve gone from not being able to make anything but the most simple DAX measure to the point where I can create some pretty cool CALCULATE functions (my new best friend), with nested date/time intelligent functions as well. I’ve been pursuing that for TWO YEARS!

In an hour this morning, I was able to build a nice little “Server Contest” dashboard for our food and beverage department. It has slicers to drive down into sales areas, major groups (entrees vs desserts), and dates. And it will correctly report the week-to-date, month-to-date and prior weekly sales in units, as at the selected point in time. (Previously, I was able to get the “effective date” to work, and my “current month” to work, but I could never figure out how to filter things to give me the correct month to date number if I clicked an earlier date. It always kept returning the real month to date, rather than the effective month to date.)

The dashboard is a beautiful thing (to us anyway), that we’ve never been able to produce until today:

11-22-2012 10-38-01 PM

After struggling with DAX for so long, I think it’s fair to say that I wouldn’t have been able to break through this without Rob’s help. I certainly wouldn’t have been able to build the report above in such a short time.

If you’re struggling with DAX, or just getting ready to start the journey get his book. You owe it to yourself.

You can pick it up from Amazon.ca (for all my Canadian colleagues) by clicking here. If you'd prefer to deal with Amazon.com, you can get it by clicking here. And/or from other routes as detailed on Rob's Website.

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

Backup Strategies

As the IT guy at work, I'm responsible for making sure that we've got good backups available in case anything goes wrong. Like many corporations we use tape backup which we rotate off site. It works reasonably well as we've got it down to a routine, with daily and weekly backup tapes, ensuring that we can roll back 1-5 days or 1-5 weeks. (Or less, as we also use ShadowCopy on our servers.)

At home though, it's a different story. I find its way harder to get into a backup schedule at home, for a variety of reasons. Despite making sure we have backups at work, I've been notoriously bad about backing up the data on my personal machines.

This obviously leads to the inevitable "What If" questions… what if the house burns down? What if a computer was stolen? What happens if I lost my laptop? What happens if we have a catastrophic hard drive failure? What if… So last week I decided to actually deal with this issue.

Acronis True Image

I installed a copy of Acronis True Image (an older version) that Acronis was generous enough to provide to me a year or so ago. I managed to backup my laptop to an external USB hard drive, which was great. But there's this thing about backups… as my friend John once said "The value is not in the backup, it's in the restore." To complete a backup test, I decided to restore it to another machine that I have in the office.

It was a no-go. Not the fault of Acronis, to be sure. I've heard great things about their products, and I'm sure it would have worked just fine. The issue is that I backed up 200GB of stuff, and the machine I could mess around with only has a 75GB drive. It just won't fit.

So I've still got that backup, but it's not really as portable as I want. And while the new version of Acronis has a "set it and forget it" feature, this one doesn't. So that still leaves me needing to manually trigger backups which, as I mentioned earlier, I'm not very good at remembering to do. So I gave up on Acronis, looking for a better solution.

One thing I would say about Acronis is that I'm not really a fan of the restore interface. It strikes me much like the old Norton Ghost versions which aren't quite clearly explained. I don't use this product much, so what I'd like to see are screens that read something like the following:

  • What file would you like to restore FROM?
    • Show the list of drives, but mark which are internal and which are external drives
  • Where would you like to restore TO?
    • Show the list of drives, again mark which are internal vs external
    • Clearly explain that the drive you select here will be overwritten

I just really didn't find it was worded quite like that, which made me a little nervous.

Mozy

So at any rate, I kept looking, and re-visited Mozy, eventually holding my breath and shelling out $9.90 for a month of backup to test it out for our PC's.

Mozy seems pretty slick. It basically works like this:

  • Flag the files you want to back up
  • It compresses and encrypts them
  • It uploads them to Mozy's server
  • They are available to restore

The actual backup interface is pretty simple, and you can pick the speed it uploads at if you want to scale up/down your upload speeds. The shot below is a backup in progress. Keep in mind that I don't have much bandwidth here at home, since I'm running over a wireless G connection.

There is something misleading about the image above though… You'd think that pressing "Cancel Backup" would actually cancel the backup. It doesn't. It stops it temporarily, as you can see below:

I also tested the restore. Again, the interface is pretty simple, as you can see below:

It took about two minutes to "find the file on the server", and then the restore of the file was pretty much instant. The file works just fine.

Some of the things that really sell me on Mozy are:

  • It has "set it and forget it" scheduling. Set the backup files and it just works away in the background
  • It is on a remote server. So I don't have to remember to burn discs or write to tape and take it away
  • It only backs up the changes going forward, so it won't consume all my bandwidth while I'm trying to use it

One thing to be aware of though, is that you do need bandwidth to upload your data to their servers. Dee's backup is over 12GB, and has been working for about 2 days now to send it all along at about 800kbps. That may seem like a lot of time, but the peace of mind of having the family photos backed up is worth it.

My Verdict

I'm pretty frugal when it comes to my software, but so far, I'd recommend this one. I think it has some serious advantages over traditional backup methods, gives good peace of mind and, at $4.95 per month for unlimited backup for home users, it's pretty affordable.

If you're interested, you can find out more at www.mozy.com/home.  I was also made aware by email of a full review at onlinebackupsreview.com, where you can also save 20% with a Mozy Promotional Code.

Windows 7 install

Last night I decided to install Windows7 RC on a spare laptop hard drive. So I pulled my Vista drive out of the laptop, slipped in the other drive and away I went.

For reference, I found the install very slow compared to XP or Vista. It seemed to take forever and a day to install it. Oh, and just as a note, you need to license code before it will boot into Windows (duh!). I had to run to my wife's computer to grab a code from MSDN as I kind of forgot that little part.

I'd already installed this on a desktop PC, and found some interesting new features that look nice:

  • Screen Magnifier tool (no more need for the magnifier that comes with my mouse software?)
  • Sticky Notes (stick right on the desktop)
  • Live Preview of application pages/windows from the task bar
  • Clipping tools (competes with SnagIt/GrabIt)

Interestingly enough the screen magnifier and live preview did NOT work well on my laptop, where they did work on the desktop.

I'll also say that the version of IE8 included with Win7 sucks. On both the desktop and laptop it crashed repeatedly. I ended up restarting IE8, clearing 2 error messages and going straight to Firefox.com to download a useable web browser.

Windows Live Mesh will not install on Windows7 at this time. That's a bit of a problem for me as I keep some pretty critical documents there to make sure I have a backup of them.

Upon shutting the laptop down last night I also ended up triggering the blue screen of death… I haven't actually seen one of those in a long time.

Of course, I had to work today, so I pulled the Windows 7 drive out and put my old Vista one back in… and just about had a heart attack! The BitLocker drive encryption screen came up. I've never actually seen that before, as it doesn't actually appear to do anything when you turn it on. The momentary panic passed though when I verified that the key I had tucked away was the right one, and I'm up and running again.

Blogging to WordPress from Microsoft Word

So based on Johan's comment on yesterday's post about this being easy, I decided to give it a shot. So far, with the exception of a boneheaded move where I tossed a virtually complete post without saving it, I'm pretty impressed at how easy this seems. I'll know for sure if this appears live though.

For lack of better content to test with, I figured I'd give a step by step guide to setting this up. J Okay, seriously, it also shows how easy it really is, and gives me a good excuse to try all kinds of formatting, such as Styles, images, URL's, numbering, and smileys. 😉

So here we go…

Creating a Blog Post – Part I

To start with, I simply went to the Office Menu, chose to start a New file, and chose Blog Post, then Create:

Registering Blog Credentials

At this point, I was greeted with a (hopefully) one time setup wizard to "Register My Blog". (Basically tell Word where my blog is, and what the login credentials are to publish content.) Here are the steps to making it happen.

  1. First, you need to choose your blog provider from the list shown below. Fortunately, WordPress was in the list, so I didn't need to create my own:

     

  2. Next, you tell Word where your blog is located, and provide your login credentials in the box below:

     

    NOTE: I think the Word team did a good job with this by putting the <Enter your blog URL here> part, as it makes it really obvious where it goes. For me, this was pretty simple to complete, (http://www.excelguru.ca/blog). Hopefully you'll know your login credentials for your own site. 😉

     

  3. Just to be thorough, I also decided to check the Picture Options. I actually ended up going with the default of My Blog Provider, so we'll see if it works.

     

  4. Click OK a couple of times, and the blog registration is done.

Creating a Blog Post – Part II

Okay, so now that we're done with the setup intermission, I'm dumped into a blank Word document with the following:

Again, it was pretty obvious what to do!

  • As you can imagine, I selected the text above, changed it to Blogging to WordPress from Microsoft Word.
  • Finally I dropped below the line and started composing my post.

Adding a Category

Of course, we also have Categories in our blogs. In the UI, there is a place to select those, so I figured I might as well try that too. I clicked the Insert Category button.


I had to clear a login box to the blog, and after that nothing seemed to happen. I clicked it again… still nothing. Again… (like anything was going to change, but I like to do stupid things like that sometimes…)

Finally, I scrolled back up to the top of the document... aha! It had inserted some fields for me to play with under the title!

So I picked General, Software Reviews, and Office 2007. J

The End Result

All right, I'm not going to tell you what I typed, as you can already see that. (Remember that I'm still writing, so I don't even know if this will make it to the blog, although I have some faith it will!)

What I am going to do is quickly summarize the stuff that I believe is important in this post, to see how it comes through. This is partly notes to me, so I know what the original Word document looked like before I posted it, as well as notes to anyone else who is interested…

Paragraph / Line beginning…

Items of importance

Why it's important

For lack of better…

Contains a happy face, and a winking icon that translate into smileys in WordPress.

First, I want to see if it interprets smiley faces. Second, the happy face (colon + closing parenthesis) already converted to a happy face in Word, while the winking still shows here as a semi-colon and a closing parenthesis before publishing. (Actually, you can see the smiley in the category image just above.)

Creating a Blog Post – Part I

Done in the Heading 1 style

How are heading styles implemented?

To start with…

Contains words formatted in bold, and is followed by an image

How does bold formatting implement, and how are images in Word dealt with?

First, you need to…

Contains numbering

How is numbering implemented?

At this point…

Contains "smart quotes", not plain old regular ones

These were apparently an issue in Word Beta2, so I want to see if they were truly fixed.

NOTE: I think…

NOTE is in red, the entire line is in italics, it contains a URL, and a smiley set (in italics this time though.) It also contains the "greater than" and "less than" signs around the "Enter your blog URL here" phrase.

The greater than and less than characters can be problematic, as they are typically characters that indicate code, particularly in XML. I'm also curious to see how more complex (overlapping) formatting works.

"Enter Post Title Here" picture

Has a border around it in Word.

The rest of the pictures were snapped (by the world's best screen capture program; SnagIt), and pasted into Word. This is the only picture that was changed by adding a border to it.

As you can imagine…

This line, as well as the following one, are bullet points. In addition, the Blogging to… title was underlined.

The underline was just to check formatting. I was more interested in seeing how the bullets would be implemented.

This whole table

Is a table

How are tables implemented?

 

And there you go! I'm going to hit Publish now, and see what happens!

This is a post about speech recognition

I tried to set up my laptop today to use the speech recognition native in Microsoft Windows.  I found it pretty hard to actually get it to recognize my voice the first time around.  It doesn’t actually feel quite as efficient as typing right now, but I’m sure that it will get better with time.

It’s very apparent that it’s quite powerful, but it feels quite clunky to try and talk to your computer to begin with.  I also found myself correcting an awful lot of mistakes where the computer misunderstood me, and that in itself was quite painful.

Probably the most frustrating part, however, was I had to do this speech recognition in Microsoft word as it could not be done in Firefox.  (I was trying to post to the WordPress blog.)

Despite the pain, I persevered and this blog post was completely dictated.  I will also be attempting, using voice commands alone, to actually copy and paste this in the blog.  If it shows up you’ll know I was successful.

Something tells me that I need to get Word to post directly to my blog which I understand can be done.