Testing the new Twitterfeed.

Oooooo...I'm gettin' all fancy now, I guess. Just another venue for my followers to received updates. Also, I think I might have a solution, now, for a past client looking for a way to regularly update his followers via email. Here's what I mean.

Later,

Excel_Geek

2008 March Madness NCAA Basketball Bracket

Selection Sunday is over. I've updated my handy little bracket spreadsheet with the selected teams. Those who'd like to download and use it, here's the drill:

1. Click here to send me $3.00 via PayPal.
2. Once you've made the payment, you should land back on a page to download the file. If for some reason that doesn't work properly, click here to download the file.
3. Open the file, making sure you can enable the macros -- that is, your Macro security is set to Medium at highest (Tools --> Macros --> Macro Security). Take note of the "lock code" it gives you.
4. Email me your lock code, and I'll send you back an associated "key code" that unlocks the file for your use. This code is specific to you and the computer you are using. If you want to use the file on numerous computers, let me know, and I'll see if I can accommodate you.

Good luck with your picks!

Excel_Geek Insiders, if you'd like to use the file, simply download it, and email me your "lock code", I'll send you the "key code" as part of your subscription.

Later,

Excel_Geek

Prepopulating a Userform

Just recently I banged out a quick little example for a reader who wanted to know how to quickly and easily store values from a userform created using VBA, and then prepopulate the userform the next time it's brought up with those stored values. Basically, I created a new worksheet in the file that would remain hidden and had a named ranged in that worksheet for each field in the userform. Then, when the userform was called up, I grabbed the values from those named ranges and put them in the inputs in the form. Then, when the userform was submitted, I changed the values in those named ranges to those currently in the userform. Pretty slick.


Like so many things Excel+VBA, it's easy when you know how. I thought my Excel_Geek Insiders would appreciate this simple example.

Later,

Excel_Geek

(how about that, three posts in one day after two months with nothing...can you tell I've been busy?)

A Way Around Web Query Limitations

The other day I got another request for a project that involved pulling data from a website into a spreadsheet. It was a situation where data needed to be posted to a form on the web page to get to the data that was desired. In this case, the user needed to post a start date and end date to the form to return a table of data the he wanted to pull into the spreadsheet. It was a situation that on many web pages would have been super easy using Excel's built-in web query tool and the little known ".posttext" property.

However, due to the way in which this page was built this method wasn't working. A web developer friend speculated it was due to the fact that the page was built using ASP.NET, which complicates a simple form with all kinds of behind the scenes javascript. I couldn't even get the form to post using the POST method of the MSXML.ServerXMLHTTP object (see this old post).

In the end, what I had to do was actually invoke an Internet Explorer session using the "InternetExplorer.Application" object. Once the session was created and I programatically navigated to the desired URL, I was able to identify inputs in the form by ID and put the data needed into each one, then programatically "click" the submit button on the form. Once the new page with the data table loaded, I could select the table of data again by ID, and go through each row and column in the table and pull the data into the spreadsheet. This was actually slicker than if I used my old MSXML.ServerXMLHTTP object method and used Regular Expressions to parse out the data.

This was a fun little learning experience, and I hope you Excel_Geek Insiders enjoy this code. It can be very helpful.

Later,

Excel_Geek

Conditional Indenting

I recently did a quick $50 Project for a long-time client who wanted a certain spreadsheet to have the feature where if there was any value in column B, the formatting of column A in the same row would be indented by 1. Since the built-in conditional formatting cannot do this, it took a few lines of VBA.

Simple and straightforward.

Enjoy the file, Excel_Geek Insiders.

Later,

Excel_Geek

2007-2008 College Football Bowl Spreadsheet

Hey, anyone remember that college football bowl pool spreadsheet I did last year?

Well...Merry Christmas! Here's the same one updated for this season's match-ups. My gift to you.

0708_College_Bowl_Pool_worksheet.xls

Speaking of Christmas gifts, be sure to check out the Excel_Geek line of merchandise I've got up on Cafe Press. This guy's got one...man does he look cool.



Later,

Excel_Geek

ExcelGeek.com blog updates look!

Did you notice? If you didn't, you should get your eyes checked or drink more Redbull.

I stayed up late the other night and completely redesigned the blog! Well, actually, the reason I did it was to have one of the new(er), fancy XML Blogger templates, as opposed to the old(er) HTML templates, so I could have access to a host of better administrative features.

I started with "Rounders 2" (by Douglas Bowman, www.stopdesign.com), but significantly modified it, changing colors to be more reminiscent of the old look (see here).

I uncluttered it significantly, added in a fancy label cloud (thanks to phydeaux3), and even made it so that viewers can hide the Meebome Chat window if they're moderately (or more) annoyed by it (thanks to Craiggers for withstanding my numerous idiotic javascript questions).

Anywho...hope you like it.