Excel SQLConcat Function Revisited

My original post on my SQLConcat function has received some traffic, but the code had a pretty serious error in it. This was due to my previous lack of understanding about VBA’s IsMissing() function.  I’ve also added some extra functionality to the code to allow for adding parenthesis along with quotes. The added functionality makes it easier to adhoc insert statements like this.

Insert into [tbl] ([col1],[col2])
Values (1,'hello'),(2,'world')

Excel SQLConcat Function

[Update 02/14/14]  

The code contained in this article contains a flaw. I have corrected the issue and explained it here. The new version of the function contains some extra functionality as well, so I encourage you to check it out. 

I do a lot of adhoc querying of SQL Server databases. Often, I find myself given a list of items to base the query on. While it’s simple enough to write a quick

SELECT foo

FROM bar

WHERE foo IN (1,3,27,52)

It quickly becomes down right arduous to type out that IN statement when it needs to be quoted and there are 20 items in the list you’re given. I’m going to show you how to create an custom Excel function that pivots that list into something we can use to query with.