XLG Favourites Updated

Jon Peltier emailed me today and suggested that the Favourites add-in would be even a little bit better if you could open multiple files from your favourite folders at the same time. Despite my intention to put it off until I’d finished another project, I somehow couldn’t resist looking at it. So Jon’s suggestions have been implemented.

The biggest change is that, in version 1.104, when you click on a favourite folder (set up through the management utility) you will be able to select one or more files:

It will open each one for you and, as always, will also deal with Word, Powerpoint, PDF, or any other file you select, opening it in the correct application.

I also tweaked the “About” form so that you can click on the hyperlink to get to my site. (It actually used to be a double click, but that wasn’t intuitive.) To help out even a bit more, I added a tooltip to it:

That feature is hardly a deal breaker, obviously, but it will make it easier for people to find me if they need to report an issue.

You can find out more about the (free) XLG Favourites Add-in and download it from this page on my main site. Instructions to install and start using it can be found here.

I’m also curious to hear what other features/enhancements people might want from this tool. Some features that I had been thinking of for the future include the following:

  • An Office 2007+ UI (I still haven’t fallen in love with a design for the Ribbon implementation yet)
  • Foreign language support (so that I don’t get any more emails telling me I can’t spell favorite correctly.) ;)
  • Ability to clear the MS Office Most Recently Used (MRU) list
  • A flag to turn on/off favourite files being added to the MRU. (Currently it doesn’t, but I find that frustrating sometimes.)
  • One user suggested that it would be nice to have favourite ranges within workbooks as well. Implementing that one concerns me a bit. (Do you store it in the database, or in a worksheet within the workbook? What happens when the file is renamed, moved or deleted?)

Any other things that you’d like to see?

Weird Word to Excel conversion

I seem to be tripping on some odd things with Excel lately. I was trying to take a list of stuff from Word and turn it into a table of data when I tripped on this one. It’s a bit bizarre and a bit amusing… Try this:

Create a new Word document with the following:

(The bullets are important)

Copy the data and paste it in Excel. You should now get something that looks like this:

Now, go into cell A1, and copy the bullet and the spaces from the formula bar:

Highlight column A and choose Find/Replace. (On the Ribbon this is on the Home TabàFind & SelectàReplace)

In the box that comes up, paste the text you just copied into the “Find” section and click “Replace All”

Here’s the result:

Wow! That’s kind of funky! Yet when you click in A2, the formula bar shows the correct text:

Go figure! What happened is that the bullet character is actually in the Symbol font, but the rest of the text in the cell is in whatever your default font was in Word. Replacing the leading text applies that font to the rest of the cell. It was particularly frightening in my first view (on real data):

I thought I’d moved to another country!

Screw up Excel by setting a PivotFilter via VBA

Today I did the following:

  • Created a new workbook
  • Added a pivot table, based on an Access database connection
  • Created it using the following setup

After doing that, I grouped my months by year, and dropped in the following code to the Sheet1 module:

  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. Application.EnableEvents = False
  3. With ActiveSheet
  4. .PivotTables("PivotTable1").PivotFields("Account").ClearAllFilters
  5. .PivotTables("PivotTable1").PivotFields("Account").PivotFilters.Add _
  6. Type:=xlCaptionEquals, _
  7. Value1:=.Range("B1").Value
  8. End With
  9. Application.EnableEvents = True
  10. End Sub

Then I changed the value of B1 to one of my account numbers and… BOOM!

And when it says the Object invoked has disconnected from its clients, it really means it! What’s really bizarre is that hitting end leaves me able to move around the worksheet using the mouse, as evident in the formula bar, but the active cell on the worksheet never changes. Closing and re-opening the workbook has no effect, as the issue persists. The only way to cure it is to shut down Excel and restart.

As it turns out, the problem is that I passed the pivotfilter a number, not a text string. An easy fix with CStr to convert it, and now I can quickly check the balances in my accounts over the last few years:

What really strikes me as really bizarre is that passing a number to a string variable in VBA will not cause an error. The value is accepted just fine. If I’d had to guess about this, I would have expected it to work the same way or, at the very worst, throw a trappable error. Passing a string to a numeric field shouldn’t cause such as drastic automation error that forces you to close Excel to cure it.

Suspicious that this might be something to do with my setup, I also tested this on a pivot table created on data from within the workbook (I mocked up a simple three line table) and the same thing happens.

Breaking Links in Excel

So during my infatuation with converting my workbook to use IFERROR statements throughout, I suddenly realized that the reports I had to send to head office needed to work in Excel 2003. After a bit of… um… motivational speaking… I elected to do the following:

  • Create a new workbook
  • Copy each of the required worksheets to the new workbook
  • Break the links

This method would allow me to take full advantage of Excel 2007′s additional functionality, speed up my calc time by using more efficient formulas, and also still provide head office with the reports they need. And easy enough to do as well, since it’s just a bit of VBA code to automate it all… right?

Yeah, right. Nothing is ever as easy as it should be.

Copying the worksheets to a new workbook was simple, but the code to break the links is bizarre. I used the code that I’d posted on this blog a while back, but I had to do it in a weird way. Here’s the method I ended up using:

  • A loop to copy the worksheets to the new workbook
  • A call to break links within the loop
  • Save and close the workbook
  • Re-open the workbook and break the links
  • Save and close the workbook

Seems a little redundant, doesn’t it?

The reason I ran the link breaking within the loop that I was using to copy the worksheets to a new workbook is because is dramatically improved the speed of the routine. I can only surmise that it did break some links, as it certainly didn’t do them all.

After that I tried to break the links with the workbook still open (saved or not), and it didn’t have any effect. Basically the only way I found to reliably kill those links was to save, close and re-open the workbook. Very irritating and slow, but effective.

Of course, as trying to work this out wasn’t hard enough, I was also battling this issue, which was leaving me with phantom VBA projects. I’m virtually certain that this was affecting some of the code, although I still had to resort to the above method once I had uninstalled that add-in.

My conclusion… breaking links on an unsaved workbook is hokey. This should work.

And just in case anyone wants it, here’s the routine I used to break the links:

  1. Private Sub Util_BreakWorkbookLinks(wb As Workbook)
  2. 'Date Created : 3/18/2009 08:36
  3. 'Author       : Ken Puls (www.excelguru.ca)
  4. 'Macro Purpose: Break all links in the workbook
  5.  
  6. Dim vLinks As Variant
  7. Dim lLink As Long
  8.  
  9. On Error Resume Next
  10. vLinks = wb.LinkSources(Type:=xlLinkTypeExcelLinks)
  11. If Not vLinks = vbNullString Then
  12. ' Break all links in the active workbook.
  13. For lLink = LBound(vLinks) To UBound(vLinks)
  14. wb.BreakLink _
  15. Name:=vLinks(lLink), _
  16. Type:=xlLinkTypeExcelLinks
  17. Next lLink
  18. End If
  19. vLinks = Nothing
  20. On Error GoTo 0
  21. End Sub

Dealing with VLOOKUP and GETPIVOTDATA errors

I need to thank Joe Chirilov and Danny Khen, from the Excel team at Microsoft for this tip. We were discussing some Excel things while we were having dinner during the summit, and I was bemoaning the fact that it would be really nice to have an additional argument for the VLOOKUP function that returned a value instead of #N/A if nothing was found in the list.

The method I had been using was the tried, tested and true approach. Here’s what my VLOOKUP would look like:

=IF(ISNA(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0, VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False))

Now, it’s no secret that VLOOKUP is a pretty heavy formula. If you don’t believe that, stick a thousand in a worksheet and see how much it starts to slow down. If you count the function calls in the above, you’ll see that instead of calling VLOOKUP once, I had to call it twice, as well as calling both the IF and ISNA functions as well. Four functions to return one result. And while I haven’t formally benchmarked the speed, I can tell you that my workbooks were running very slowly.

Both Joe & Danny looked at me kind of funny and asked why I wasn’t using the IFERROR formula. My response was something really clever, like “Huh?”

I’m really glad that we had the conversation at all. This program is so big, that things just slip in during releases that we either don’t hear about, or I don’t notice. :)

The IFERROR approach to this issue is far superior to the old method, in my opinion. Using IFERROR, the same VLOOKUP results can be achieved with:

=IFERROR(VLOOKUP(rngToLookup,rngToLookIn,ColToReturn,False),0)

Much better!

What really spurred the discussion was that I’d also been working on (and still am) a set of financial statements that are driven by pivot tables. In making heavy use of the GETPIVOTDATA function, I was running into the same problems there: If an account/department combination didn’t exist for the month, I would get a #REF! error. To solve this, I’d wrap them up in an IF/ISERR combination like this:

=IF(ISERR(GETPIVOTDATA(“Amount”,PT_Actual_YTD!$A$5,”GLMonth”,rngMonthEnd,”Account”,$D102,”Dept”,$E102)),0,GETPIVOTDATA(“Amount”,PT_Actual_YTD!$A$5,”GLMonth”,rngMonthEnd,”Account”,$D102,”Dept”,$E102))

With IFERROR, the formula compresses down to:

=IFERROR(GETPIVOTDATA(“Amount”,PT_Actual_YTD!$A$5,”GLMonth”,rngMonthEnd,”Account”,D46,”Dept”,E46),0)

In my opinion, IFERROR is a far superior approach than the prior. It is so much easier to:

  • Write
  • Read
  • Maintain

So far as I can see, it pretty much wins on all counts but two…

  • This function is only available in Excel 2007+. Open the file in a previous version of Excel and all those awesome formulas return #NAME? errors. :(
  • It is not generic enough to capture the difference between #N/A, #REF! and other errors. While this isn’t an issue for me, I’m sure someone will have a practical use why they might want to react differently if one type of error was returned vs another.

At any rate, as great as this function is, and as much time as it will save me in the long run, it didn’t go in without issue for me. Naturally, all of my financial information also has to be sent to head office. And what do they use? Why Excel 2003, of course! In some ways I’m kind of glad that I didn’t put that together immediately, so I had half my file converted to IFERROR before I realized the issue. Had I not done so, I might have stuck with the slower approach, even though I need the time most at month end. Instead I wrote a utility to copy the sheets they need to a new workbook and break all the links back to the original source. A little bit painful to have to do, but at least I can still take advantage of the new approach, and they can have files without #NAME? references in them. :)

Microsoft Add-in Causes VBE Ghost Project Issues

Recently I was introduced to this really cool add-in by a vendor: SQL Server 2005 Data Mining Add-in for Office 2007. It’s a pretty neat tool that you can use to analyze relationships within data.

Unfortunately, as cool as this add-in is, it has wee little issue with it. It creates ghost projects in the Visual Basic Editor. What I mean is this…

When you open Excel, a new file called Book1. Go into the VBE (press Alt+F11) and you’ll see VBAProject(Book1) in the list. What should happen is that when you close Book1 in Excel, it should go away in the VBE as well. With this add-in installed, it doesn’t. The workbook closes in Excel, but it hangs around in the VBE. You can’t do anything with it, because it doesn’t really exist, but it just loiters there.

You should also never be able to see the same file name multiple times in the VBE, as you can’t open the same file more than once in Excel. The picture below is a mock-up of what I saw, as I’ve fixed the issue, but this is what it looked like.

(Note: My issues were not with Book2, but rather with one of my own files that was listed several times.)

I’ve been having issues with a certain procedure I’ve been writing all day, and I’m not sure if this was affecting it or not. I suspect that it was a bit, although I can’t prove it, as I was opening and closing the same file repeatedly in testing, and seeing some weird results… almost like it was pointing to a former copy or something. Again, I can’t prove it, I just suspect it is all.

I also don’t know if it would affect a normal, non-code hungry user at all. If you’re a developer though, it may be something you want to know about.

What I want from Outlook…

In a word, linked sub-tasks. I use Outlook every day for my task list, my calendar and my email. I spend hours in the this program and, while I’ve fully aware I could always use more training in it, I don’t think I’m a total dummy in it’s use either. (Glad to be proven wrong though!)

Ultimately, what I want from Outlook is an EASY way to manage the projects that I work on. I don’t want Microsoft Project and I don’t want to have to go to the hassle of setting up Sharepoint for a one-off project. (Steve French showed me a cool trick for doing pushing repeated tasks into Outlook from Sharepoint, but that’s overkill here.) The big issue that I run into with Outlook is that I can’t link the minor milestones to the overall project goal.

What I do now is create a task, and within it create a table of what needs to be done and the status, like this one that I’m working on now:

This has a few inherent problems. The very first is that the Due Date is what shows in my calendar on the task view. As you can see below, where the work week starts on Sunday, the task is showing up on Friday (the 20th). I have to complete a lot of these tasks as I go along through the week. So if I don’t want an overdue task in my calendar, I have to update that field every day.

What I want is to be able to create all of the milestones into “Sub Tasks”, then link them to the main task. When I click on the main task, I should be able to get a status update that shows me exactly what I’m seeing above in the first image… Okay, maybe not subgroup like I’ve done the “Build Statement” tasks, but I could preface each of those as something if I wanted. With proper sub-tasking, I could then manage my calendar.

Honestly, my needs aren’t that big… I just want a central place to manage related tasks as compared to those that aren’t. Surely this is something that should be native in Outlook as a “productivity” app, shouldn’t it?

MVP Summit Wednesday

And so, sadly, Wednesday dawned as the last day of the 2009 summit. Breakfast was a huge buffet at the WTCC:

This is where we found Richard again, looking a little worse for wear. Reminds me of the first morning of my first summit. <grin>

It ended off with a half day of keynote speeches, and then lunch. They keynotes were interesting, with Steve Ballmer’s being as energetic as usual. He really is a dynamic guy, and I believe genuinely values the contributions that the MVP’s make to Microsoft’s products. One memory I’ll take away is him signing the back of a photo. This particular photo was of an MVP (Frank) who had passed away shortly before the summit. The Small Business Server guys asked Steve if he’d sign something for Frank’s family. Steve called him up and proceeded to write on the back for about 3 minutes. Not just a line or two, but a real essay. He didn’t have to go to that length, and it really displays something of his character, and of his feelings of the MVP crowd.

After the keynotes, we broke out and there were a lot of goodbyes as people had to start heading off to flights. We did manage to get a few photos along the way though:

That’s Kevin Jones on the left, Mike Rosenblum, me in the hockey jersey, Richard Schollar in the back row beside Greg Truby. Smitty Smith is in front of Richard, and Tom Urtis is beside him. Below is the photo of the Canadian MVP’s in the our hockey jerseys… a tradition at the summit keynote. I’m in the back row somewhere.

From there, we headed back to the Westin again, and then Dee, Mike Rosenblum and I went out to dinner at Il Fornaio. It was a great meal with some awesome in depth conversation about .NET code. (My wife is just awesome about me getting into geek talk with someone!). Mike is a great guy, and we both had a lot of fun over dinner.

And then, to close off the event, the three of us headed back to the Westin bar where we met up with Roger Govier and a couple of UK MVP’s that he brought with him. Nate Oliver also dropped in too. One of the UK MVP’s that Roger brought was Joe Fawcett, an XML MVP whom I’d met at a previous summit. Seeing Joe caused another round of geek talk to break out as the whole group got into a deep discussion on the compatibility of XML Namespaces. (Look, snicker all you want, but it WAS a technical event!)

I really had a blast this summit. Learned a lot, gave a lot of feedback and met some great new people. Now I just have to make sure I get invited back next year. ;)

MVP Summit Tuesday

Again, another early rise to breakfast, and off to the bus again. Fortunately the session started late due to technical difficulties as the bus got a bit snarled in traffic!

That morning we actually saw something very, very cool which I can talk about since a public post has already been released. It is called Gemini, and it’s a pretty slick data mining tool for Excel. Basically what it allows you to do is connect Gemini to database tables. It instantly analyzes the tables and sets up relationships. You can even add new tables of external data, and it will analyze those and add any relationships it finds. From there, and this is the really cool part, it sets up the OLAP cube functionality. So in Excel 2007 and beyond, you can use Excel’s built in cube functions to pull your data out of the cube. To the lay person what this means is that you can link to your General Ledger table and then write a formula that pulls out GLBalance (“Account”,”Department”,”Month”). Over the last year I spent a ton of time programming that exact functionality, and soon I’ll be able to do it instantly. Seriously, they demoed analyzing 110 million rows in a couple of seconds. It was a real WOW moment.

The really awesome thing here is that it puts the OLAP cube functionality into the hands of the BI professional, not the IT department. You’ll no longer need to install a SQL server, install Analysis Services, create dimensions for the cube and anything else you need to do that I don’t know about. Very, very cool. I can’t wait to get my hands on a Beta of Gemini.

The rest of the sessions were great too, but due to my NDA, I can’t talk about them. Sorry!

After the sessions, we headed back to the hotel, then off to the Music Experience Project for the big MVP party. Lots of food, beer & wine to be had for sure. (There were several of the buffets like the one below)

In addition, there was Rock-a-roke. Kind of like karaoke, but with a live band. A live band and a lot of MVP’s whose talents lie in helping people… not necessarily signing. (Kudos to them for getting up and entertaining though!) Here’s a picture of my MVP lead Sasha Krsmanovic “singing” Hit Me Baby One More Time. :D

Eventually they closed down the bar there, so we had to go find another one. With Mike Alexander absent, no one felt it necessary to wander into a seedy bar, so we headed back to the Westin lobby bar again… as did every other MVP I think. Seriously, I’ve never seen so many people in there before. I found out the next evening that we had run them out of one of their beers!

Dee finally made it out with us on Tuesday night. That’s Greg Truby on the left, Zack Barresse in the middle and Dee on the right.

We had a ton of fun that night, as you can see in the picture below…

Smitty, Richard and Zack are enjoying is Roger’s antics… here he is dancing around in a woman’s jacket!

Somewhere during these antics Richard disappeared. We wondered aloud where he went, and then again half an hour later. The only trace of his being there was his full glass of red wine looking lonely on the table. Bob Umlas has a picture of him looking particularly ill the next morning. :D

It was a late night again on Tuesday. I think another 2AM turn in again. Thank god breakfast wasn’t until 7:30 on Wednesday!

MVP Summit Monday

It always irks me when I look back and realize that I didn’t take many pictures…

Monday dawned way too early. I made it down for breakfast, and then off on the 20 minute bus ride to the Microsoft Conference Center on the Redmond campus. I should really take a picture of that building every year. I’m just really glad that the morning session wasn’t too intense. :)

At any rate, from there it was off to our first real sessions with the Excel team, which was great. I genuinely enjoyed all the sessions this year. After the sessions were over, Zack, Smitty and I headed off to the Microsoft company store, (which has moved since last year and looks great.) And from there it was back to the session building for dinner with the Excel Product Group.

Dinner with the product group is always pretty cool. This year I got the chance to spend some time chatting with Joe Chirilov and Danny Khen, discussing everything from IFERROR formulas (awesome formula!) to Pivot Tables to AutoFilter/ListObject Filter differences. Some serious geek-talk that was a hoot!

So the only pictures I have of Monday are below. This is Zack Barresse (on the sign), and me lounging against it.

It’s kind of neat too that every lamp standard on the Redmond campus bears one of these banners. They sure do make you feel welcome as an MVP.

After the Product Group dinner, we headed back to the hotel. We set up in the Westin lobby bar that night, and ended up heading to our rooms before the bar closed. If I recall correctly, I got in around 1:15AM that night… and set the alarm for 6:00AM again.