Treat an Excel Workbook like a database with Visual Studio

I've been doing a lot of work with MS Excel 2003 lately. Specifically, I'm using it as a data source for an application. I recently learned Visual Studio 2005 can connect to an Excel workbook and treat it like a database. I thought I would share my findings. Here's how it's done:

1. Open Visual Studio 2005, and bring up the "Server Explorer" (View > Server Explorer)
2. Next, click the icon to "Connect to Database"
   
3. Choose "Microsoft Access Database File" as your data source
   1. We choose this because we want the .NET OLE DB Data Provider
   
4. Click "OK" to get to the "Add Connection" dialog
5. Browse to your Excel file
   1. You'll need to change your "Files of type" from "*.mdb" to "*.*"
6. Next click the "Advanced..." button in the "Add Connection" dialog
7. Locate "Advanced > Extended Properties"
8. Paste in "Excel 8.0;HDR=Yes;IMEX=1"
   
   According to www.ConnectionStrings.com:

   "HDR=Yes;" indicates that the first row contains column names, not data. "HDR=No;" indicates the opposite.

   "IMEX=1;" tells the driver to always read "intermixed" (numbers, dates, strings etc) data columns as text. Note that this option might affect excel sheet write access negative.

9. Click "OK" for the "Advanced Properties" dialog
10. Click “Test Connection” to verify if you have everything set up correctly.
11. Click "OK" for the "Add Connection" dialog
12. You're all set, your Excel file should now appear in your database list.

You can now run queries on your Workbook.