Wednesday, February 27, 2008

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.



(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.



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.