Macro to run SQL queries in Excel

Question

I have a bunch of SQL queries that I have created in Excel by using cell references as parameters. All the queries are stored as strings in one column. Rather than use the Microsoft Query to create a connection for each SQL query, is there a way that I can have a macro run down the column and execute each one of the queries from each cell and output the result in the cell directly to the right of it?

Thanks

Answer 1

How about using ADODB? I assumed SQL is placed in A1 and below:

Const MY_CONNECTION_STRING = "Driver={SQL Server};Server=X;Database=Y;Trusted_Connection=Yes"
Sub RunSQL()
Dim cnn, c, rs
Set cnn = CreateObject("ADODB.Connection")
cnn.Open MY_CONNECTION_STRING
For Each c In Range("A1:A" & UsedRange.Rows.Count)
    Set rs = cnn.Execute(c.Value)
    c.Next.Value = rs.collect(0)
    rs.Close
Next c
cnn.Close
End Sub