CODEWITHSUNDEEP
mysqlvbaexcel
Greg
Greg

Reputation: 426

SQL IN statement for multiple columns Excel

Using VBA, Excel 2007, and the following connectionString: 

"provider=Microsoft.ACE.OLEDB.12.0;Data Source=myFile.xlsx; Extended Properties=Excel 12.0"

I'm trying to perform the following query SELECT [col1], [col2] FROM [Sheet1$] WHERE ([col1], [col2]) IN ((val1, val2), (val3, val4), (val5, val6))

Alas, VBA errors specifying that it doesn't like the comma in the query expression.

Is there any syntax change I could perform to make this query run? Or another query that would do the same thing?

Below is the surrounding code per request

Sub testQuery()
   Dim conStr As String, sql As String
   conStr = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\me\Desktop\QueryMe.xlsx; Extended Properties=Excel 12.0"
   sql = "SELECT col1, col2 FROM [Sheet1$] WHERE (col1, col2) IN (('val1', 'val2'), ('val3', 'val4'), ('val5', 'val6'))"
   Dim objCon As Object, objRS As Object
   Set objCon = CreateObject("ADODB.Connection")
   Set objRS = CreateObject("ADODB.RecordSet")

   objCon.Open con
   objRS.Open sql, con
   objRS.Close
   objCon.Close
End Sub

Upvotes: 0

Views: 1049

Answers (1)

Joseph
Joseph

Reputation: 5160

You might be able to get away with this:

sql = "SELECT col1, col2 FROM [Sheet1$] WHERE col1+col2 IN ('val1val2','val3val4','val5val6')"

Upvotes: 1

Related Questions