February, 2008 articles

DLookup for Excel  

I had to do a couple of ad-hoc Excel jobs today and found that whilst Excel has a VLookup function for spreadsheet/ranges it doesn’t have one for databases.

It’s been a while since I touched VBA and Access but the DLookup function was quite useful so here it is for Excel. Read the warnings below before you use it!

Public Function DLookup(expression As String, domain As String, criteria As String, Optional connectionString As String)

    Dim con As ADODB.connection
    Set con = New ADODB.connection
    If IsMissing(connectionString) Then
        con.Open "Driver={SQL Server};Server=abc;Database=def;Trusted_Connection=Yes;"
        con.Open connectionString
    End If
    Dim rs As ADODB.Recordset
    Dim sql As String
    sql = "SELECT TOP 1 " + expression + " FROM " + domain + " WHERE " + criteria
    Set rs = con.Execute(sql)
    If Not rs.EOF Then DLookup = rs(0)
    Set rs = Nothing
    Set con = Nothing

End Function

Copy and paste the code above into an Excel module and replace the “Driver=… with what you want to be your default connection.

Some examples of use:

To select the Name column from the Customers table where the numeric ID is held in column 2.

=DLookup("Name","Customers","ID=" & A2)

To return Forename and Surname columns joined together with a space from the Contacts table in the crm database using the postcode from column 5

=DLookup("Forname + ' ' + Surname","crm..Contact","PostCode='" & A5 & "'")

To return Description from a Products table held in another connection using a partial like match from column 1:

=DLookup("Description","Products","OrderCode LIKE '" & A1 & "%'", "DSN=SecondaryDSN")

I think you get the idea!

There are a few caveats you need to be aware of using this function:

  • It only returns the first value of the first row
  • DLookup’s are not efficient opening a connection each time it is referenced. This could put a lot of strain on your server.
  • DLookup syntax is open-ended and has some attack vectors.

If that doesn’t scare you then give it a shot. As usual no warranty expressed or implied!


Hibernate presentation in Guernsey  

My good friend Steve Streeting is giving a talk about the Hibernate object-relational mapper at the Guernsey Software Developer Forum tomorrow night. Hibernate is a very successful ORM for Java which has been ported to .NET under the moniker NHibernate.

The event, as always, is open to everyone and commences at 6pm at the Guernsey Training Agency above the old Post Office in Smith Street, St. Peter Port. They normally run for around 1 hour followed by an optional 30 minutes in the pub for those who can’t get enough (code, not alcohol).

In related news I have been liasing with Microsoft who have agreed to provide support to host a VS 2008 InstallFest event to celebrate the launch of Visual Studio 2008. The idea is you bring along your laptop (if you have one) and install one of the copies of Visual Studio 2008 Professional we will be giving away (full, not-for-resale copies) as well as enjoying some food, demos of the new features of .NET 3.5 and a bit of Xbox 360 guitar hero.

We’re just looking for a venue for this event (and potentially future GSDF meetings) as the Post Office has now been sold and will not be available from the end of February :(


Microsoft opens Office binary file format specifications  

Microsoft have released the binary file format specifications to their Office suite (the XML ones are already published) under their Open Specification Promise.

I am not a lawyer but as far as I understand this means you are free to implement the standards with a promise that Microsoft will not use any patents under its control that are required to implement the specification against you.

Hopefully Apple will now address Keynote’s PowerPoint support bug so exported PPT’s works with Office 2007.

Now that the .NET Framework 3.5 source is available (for reference) and Scott Guthrie (now VP) announcing the MVC framework will be user-buildable and patchable (not redistributable) and include project templates for a number of open-source testing frameworks the future is looking very rosy.


Humane theme for TextMate and Xcode  

My Humane theme for Visual Studio is getting a fair bit of traffic today courtesy of Scott Hanselman. Given I have been messing with Mac development lately I thought it was worth porting to TextMate and Xcode 3.

Panic Sans coding font

My Envy Code R programming font isn’t great on the Mac yet so I have configured these to use the excellent but overlooked Panic Sans in 12 point which unlike Monaco is available in bold, italic and bold italic variants. (I love my comments to be italics)

To install this font you must:

  1. Download Panic Software’s Coda application
  2. Navigate to the Coda application and choose Show Package Contents
  3. Navigate to the Contents/Resources folder
  4. Double click on the Panic Sans.dfont and press Install Font
  5. Panic Sans is now available to other applications too


Screenshot of the Humane Theme and Panic Sans 12 point inside TextMate

Download Humane theme for TextMate (5 KB)

Launching the downloaded .tmTheme file will cause it to copy to ~/Library/Application Support/TextMate/Themes
Select Humane from the Preferences > Fonts & Colors pane in the drop-down list box

Xcode 3

Screenshot of the Humane Theme and Panic Sans 12 point inside Xcode 3

Download Humane theme for Xcode (4 KB)

  1. Copy to ~/Library/Application Support/Xcode/Color Themes
  2. Select Humane from the Preferences > Fonts & Colors pane in the drop-down list box

Porting themes

Until somebody comes up with an IDE-independent theme format or cool converter we’ll have to do it by hand. The easiest way I have found is:

  1. Install Hex Color Picker on the Mac to allow entering hex into the standard color picker
  2. Open the Visual Studio theme .vssettings file in a text editor
  3. Open up the Fonts & Colors preferences pane up in your Mac IDE
  4. Go through each one and choose the nearest match in the .vssettings
  5. Transcribe each color by reading the VS colour pairs backward, e.g. 00631409 becomes #091463