Ease the pain of excel OLEDB and ODBC connections

Excel’s ODBC and OLEDB connectivity can be a slow an laborious, completing fields repeatedly with little or no real control over the end result until more menu surfing and button clicking is done, which is where my most recent code snippet has come from.  Although I am a fairly heavy user of Excel, producing reports and analysing statistics from our service desk, it’s nothing compared to the volume of work my colleague Carly Bond puts through it.

Between the two of us we came up with the idea of a simple but powerful Excel Add-in, one that not only took the hassle out of creating the database connections but also saves entering the same text repeatedly.

Here’s a code snippet of my add-in, The Add-in was developed using Visual Studio.Net.  To use this code, create a new form, on the form add controls for the user to input the server, database, table names and a box for the SQL statement.  In my example below I use comboboxes as my source for the information.  In a later revision of the code I have also added logic to save the previously entered values, so if a user repeatedly enters the same values over and over again this should help save time.

Upon publishing, we also decided to remove the connection after extracting the data but this is personal preference.

If you would like to add the ability to save the values, use Visual Studio’s built in ‘my.settings’ logic and store the values in application settings upon execution.

This is an Add-in in it’s very early stages, and as the code matures I will post updated versions.

Hope this is of use to someone.

Mike Hudson

Mike Hudson is a Lead Cyber Security Analyst living and working in Kingston Upon Hull. With extensive experience in Microsoft and Apple technologies, ranging from desktop OS’s to Server OS’s and hardware. By day working as part of an infrastructure team, and by night ridding the world of IT issues through blog posts..

%d bloggers like this: