Pivot tables that are based on external data are somewhat limited compared to normal external data queries. One limitation is that you can’t (read: it’s difficult) to create parameters. At least there’s no way to do it in the user interface that I’ve seen. This post will show you how to modify the CommandText property of the PivotCache object to create a parameter.

Note that you can simply create a normal external data table and base your pivot table off that. That you should give you all the flexibility you need, but it’s not as neat and clean. Also, I believe you have to perform two refreshes in that circumstance: one for the external data and one for the pivot table.

When you create the external data pivot table, include criteria to limit one of the fields. This builds the WHERE clause of the SQL statement and makes it far easier to convert to a parameter query. For this example, I’m using the Invoices table from Northwind.mdb and pivoting the ExtendedPrice on the PostalCode.

This produces a pivot table that shows how much we’ve invoiced for this particular zip code. I picked a zip code that was in the database, but it’s just a placeholder for now.

The SQL statement behind this pivot table is stored as a property of the PivotCache object. Here’s what that property looks like via the Immediate Window:

That property is just a string, so if you know what a parameter looks like, you can manipulate that string. Here’s how I might do it in a macro:

I’m replacing my placeholder zip code, 05022, with a question mark. The question mark is interpreted by MSQuery as a parameter. Once I run this sub, the pivot table tries to refresh, and I get this

Now that the question mark is the SQL statement, every time you refresh the pivot table, it will ask you for a value. What I can’t seem to do is manipulate the parameter to, say, change the prompt or base it on a cell. I know all those features are built in to the class, MS just didn’t expose them in this situation (i.e. they didn’t create a Parameters property of the PivotCache object like they did with QueryTable object). Consarnit!

The Dir function can be used to check for the existence of a file, strip out the path, or loop through all the files in a directory. The Attributes argument

is used to limit what kinds of file Dir finds. The default, vbNormal, finds files with no attributes. The other arguments find files with certain attributes AND no attributes. That means that you can find directories by using

but you’ll also find all the files. To limit the search to just directories (or folders, if you prefer), use the GetAttr function. You loop through both wanted and unwanted files and weed out the ones you don’t want.

I posted about how I didn’t know if the line continuation character was a hard limit or a memory limit. Usually, if I have a limit it’s a hard limit because my computer has enough ram for whatever Excel cares to do. Well, tonight I’m using my wife’s ThinkPad P3 with 256k of ram. That’s enough ram for just about anything, but I could see how a guy might run up against it. Since it’s late and I’m bored out of my mind, I thought I’d tell you why I’m using my wife’s laptop.

Last week I was in Tucson, as you no doubt know, and I was admiring JWalk’s mp3 collection and his method for listening to them. JWalk claims that he doesn’t watch TV. Whenever anyone claims that, I call bullsh*t. The Unabomber? Maybe. Anyone else, I simply don’t believe them. Well I’ll be damned if he isn’t telling the truth. He turned on a Diamondbacks game that was tied in the bottom of the 8th. Other than that, I watched Texas at Nebraska (the only good to come of the NHL strike is that ESPN is showing college baseball) and an Eric Clapton DVD. Besides that, the TV was only used for one thing: playing MP3s.

JWalk has a somewhat impressive mp3 collection. He has a computer hooked up to his TV that plays these MP3s via Winamp. Winamp has a feature called Visualizations where you can watch spyrographical renderings move to the music. It’s actually more interesting to watch than that sounds. Listing to a selected list of his MP3s was far more interesting than anything on TV. I vowed that when I returned to Omaha, that I would get a similar setup. I would stop watching TV and start playing music on my 52? rear projection.

I should note that I’m not going to stop watching TV altogether. The Simpsons is on twice a day here and my DVR is set to record every episode. I’m fifteen episodes behind because of my absence. There may come a day when I actually tire of watching The Simpsons. It happened with Seinfeld, MASH, and Cheers. All shows that were syndicated to death. So far it hasn’t happened for The Simpsons, though.

Normally when I vow to do something, it’s a lock that it won’t happen. But for some reason I came home inspired. Andrew and I trekked to BestBuy in search of a video card for my mothballed Gateway PC (PIII, 192MB ). After inspection of the back of my TV, it appeared the only thing I could connect to a computer said SVIDEO-Input1. That’s not the DV1 that JWalk was using.

As an aside, I hate BestBuy. They used to live two miles from house and they recently moved 10 miles away. Did they build a bigger parking lot? No. Did they build more registers to alleviate the lines to checkout? No. Can you buy anything at BestBuy that doesn’t require you to mail in for a rebate? No. Nevertheless, I live in a one-horse, one-BestBuy, zero-Fry’s town. If I want instant gratification, it’s BestBuy or a 25 mile drive to CompUSA.

Three hundred fifty bucks later, I’m the proud owner of a 200G Maxtor One-Touch (I swore I’d never buy Maxtor again), an $80 GEForce video card with, not only an SVideo connections, but an SVideo cable. (I had no idea if it would work, but as usual the BestBuy losers were no help.) And I got a camera case and Andrew got the Incredibles game for his x-box (If you know how to defeat the helicopter in Level 1, leave a comment and Andrew will be your buddy for life – but not in a Micheal Jackson way.) In case you’re interested, SVideo gives my 600×600 resolution by default and 1024×760 maximum resolution. That first resolution may be 800×600, but the keyboard wire is only 3 feet long and I need to be at least 10 feet from the TV in order to read it. Normally, I would switch to the highest resolution possible, but it’s really hard to read stuff on this screen to begin with, so I’m leaving it.

The next two hours of my life were spent searching for a Windows XP disk. The Gateway in question has Win98 on it. I don’t object to Win98, I just wanted the super convenience of plugging stuff in and having it work automatically that XP seems to offer. I found this packaging that said XP Home Upgrade on it. I really didn’t want to install Home, but the disk was right there and I was only 10 minutes into my search. “Screw it”, I said and began to install XP Home. It said it couldn’t find an existing Windows install and that I should insert my Win98 disk. Yeah, right, let me get that disk. Please. I don’ t know where the monitor for this computer is, you think I know where the freakin’ OS disk is?

Okay, the Home install isn’t going to work. Maybe, I need to start digging into the mountain of MSDN CDs. Now, where did I put those? Oh, here’s an MSDN CD that says Windows XP with Service Pack 1a. Put it in the CD drive. Reboot. Nothing. That’s right – I switched my MSDN subscription to DVDs because I was tired of getting 50 CDs a month. Good move, you’d think, but this computer doesn’t have a DVD player.

I went looking for my stash of CDs from before I converted the subscription and I finally found them. They were next to my $750 scanner and $200 printer that don’t work with XP and are therefore unusable. Green disks, green disks, where are those damn green disks. Okay, I found a green disk that says Windows Millennium Edition. The wheels start turning. I can put the XP Home disk back in and use this ME disk as my proof of upgrade. Genius.

Finally I stumbled on a retail version of XP Pro and I installed that. Thank goodness. Then it asked me for a product key. For the love of Pete, can they make this any harder. All I want is an operating system that installs with no hassles, that I don’t have to pay for, and that recognize all my hardware no matter what.

OS installed, I move to the video card. It installs without a hitch, almost. When I hook it up to the TV, it works perfectly. I plug in the Maxtor and it’s recognized automagically. I now have access to all “my” music files – hmmm, where did I get such a large library. JWalk put the Winamp installation executable on my external HD, so I installed that. I was ready to roll. Then Winamp wanted to create a Media Library. Okay, I let it. Twenty minutes later, it was 6% done and I went outside to cut the grass. An hour and a half later, it was done creating the library.

Now I could play music, show Winamps default Visualization, and my wife and mother-in-law were duly impressed with my abilities, if not my choice of music. The first thing my wife wants to know is if I can rip one of her CDs. How the heck should I know? JWalk uses some ripping software but it’s not Winamp. And besides, I can’t get to the internet yet.

Next I plug in a wireless USB adapter to see if I can use my existing Internet connection. Nope. It appears to install normally, but I can’t access anything – other computers or the Internet. Finally, tonight I figured out how to connect to the ‘net. My wireless connection was set to bridge. I changed it to Router, or some such setting, and I was on the net in a flash. That probably screws up my chances of actually networking the PC’s, but I’m happy to be able to get on the net.

I downloaded CDex to start ripping CDs. I started about 2 1/2 hours ago and I’ve got about 20 of them done. So, I’m watching progress bars, sitting in my kitchen, drinking one stuka after another, and therefore using my wife’s computer instead of mine which is downstairs. Can you believe it only took me 17 paragraphs to explain why I’m at my wife’s computer instead of my own. I must say that I really like this keyboard. The keys are nice and close to together, so there’s very little movement involved. No Windows key, though which is a real pain.

End rambling now.

In VBA, you can use a line continuation character (a space followed by an underscore) to split a single VBA line into two or more. Like this:

I just tried to make a really long constant string and I separated it into it’s 35 or so lines so that it would be readable. Lo and behold, at line continuation character number 25, I got an error: Too many line continuation characters.

I know I use too many line continuations. I’m a product of the newsgroups where it behooves you to not post code whose lines exceed 76 characters. Even when I code and have no intention of posting on a newsgroup, I still find myself keeping the lines short. Well, this is the first time I’ve ever run into a limit. By the way, I fixed it by:

One final note about error messages: This limit may be hard and fast, or it may be one of those “available memory” type limits. I don’t know and a rudimentary Google search didn’t tell me. (See footnote 1). Microsoft’s error message certainly could of told me though. How about a message like “Too many line continuation characters. They are limited by available memory.” or “Too many line continuation characters. You can only have 25.” or “Too many line continuation characters. Why don’t you learn how to code properly, you twit.”

FN1: I was going to tell this really boring story about why I’m using a computer that doesn’t have much RAM, but I think I’ll just make a really boring separate post instead.

I’m back from vacation. I had a mountain of mail, voice mail, and email waiting for me at work this morning. I spend a whole week recharging my batteries and they’re drained after the first day. But I had good time in Tucson and, as expected, JWalk and Pamn were excellent hosts. The weather was absolutely outstanding while I was down there. I must have brought it back with me, because Sunday was about as nice a day as you can expect in Omaha. It was eighty degrees, sunny, and no wind. That’s not a very common combination.

One thing I didn’t do on my trip was think about Excel. I worked with a lot of php and css stuff. One project that I played with on vacation was my company’s website. The content isn’t complete yet, but I wanted to get the css nailed down. I’m trying to use WordPress as a content management system, rather than a blog. I have three or so static pages on the site, and the rest of the pages will be project pages: basically the same information for each project and some photos. The original site is at www.paragonconstructioninc.com (I didn’t design it, so feel free to make fun of it) and the one on which I’m working is at www.paragon.revise.org (Thanks to my buddy Jeff at Revisemedia for helping me out yet again). I did “design” the latter, so be gentle when you make fun of that one.

The five pieces of project data in the project “posts” are in custom fields and the photos are in the main content section of the post. (There’s only one “post” with photos in it right now.) I’ve rearranged single.php, page.php, and archive.php from the default theme. I took all the stuff that would make it look like a blog instead of just a static site. The plugins I’m using are

• Get custom field values – to produce the unordered list of project data. I can’t use the_meta because it sorts it alphabetically and I can’t seem to change that.
• Page to Front – to make one of my static pages replace index.php.
• RunPHP – which runs php from within a WP page or post. My Project Portfolio page has php to list the categories. Super WP guys would have made their own php file, but I like this better.

Okay, I’ll quit boring you with php and css crap, but if you want to see how I hacked the default theme, you can see my butchery by downloading pciphp.zip. Now, I’ll bore you with Excel stuff starting again tomorrow.

You know what vba programmers do on vacation? Learn another programming language. Yes, that’s my idea of fun. Anyway, here’s my first foray into php. Half the credit, but none of the profit, goes to J-Walk.

Daily Dose Stat Graphs

You can download the php at ncommgraph.php.zip if you want to look at it.

Reading some Scoble on my vacation and came across this:

4.5 million MSN Spaces created in four months

Congrats MSN Spaces on getting to 4.5 million. Funny, now other teams are asking me to attack them like I attacked MSN Spaces.

Am I reading this right? Is Scoble taking credit for MSN Spaces success? Yikes. Two points: First, when is Robert going to get tired of “shocking us with a new business paradigm”? You know sometimes the old paradigm works too. Second, adding 4.5 million websites about how some chick hates the subway isn’t actually contributing to the common good. MS should figure out how I can upgrade my Office 97 for less than a paycheck rather than giving away blogging space to people who have nothing to say.

Speaking of having nothing to say, I’m going back in my hole.

Jan Karel recently added RSS to his excellent Excel site, ADS. I’ve subscribed via Bloglines. Clearly RSS is a terrific technology for websites that are updated frequently, like Excel Overdose Daily Dose of Excel. But it may be even more useful for static websites that are updated infrequently. ADS is not a very good example of that because Jan Karel is updating like a bat out of hell, but there are plenty of Excel sites out there that only add pages once per month or once per year. I think RSS would be invaluable on those kinds of sites.

Take The Spreadsheet Page as an example. It has outstanding content and I certainly would want to know if there was new or updated content on the site. Since it already has everything you’d want to know about Excel, it is understandably updated infrequently. I could check the first page every day or every week and any new new content would likely be listed there. Or I could open Bloglines, as I do everyday anyway, and in the course of reading all my favorite Excel blogs, I could scan the list of static sites for new content. If there’s only one new page a year, it costs me nothing extra to check it every time I use my RSS reader.

Jan Karel pointed to Making an RSS Feed as a nice tutorial. If you have an Excel site, static or dynamic, I encourage you to have an RSS feed. Right now I’m subscribed to JKP-ADS, Methods in Excel, JMT Forum, and several KB alerts sites, all of which I would consider static.

I’m not an expert on RSS, I just like to use it. There may be some downside to having of which I’m not aware. Maybe every time I open Bloglines, it loads your xml page and costs you bandwidth. I simply don’t know, but if anyone knows the downside to it, be sure to leave a comment here.