Macro to pull specific SQL data based on Cell Range

[GATA_Spread]

Looking to create a macro that will run a SQL query based on a range of cells in Column A and return the data back into the worksheet. The sheet will be used by multiple users, and the values in column A will change based on the user. Sometimes there may be 100 items Column A, sometimes only 2. The SQL DB has over 18 million rows, so it is too large to bring directly into the worksheet. I have googled this, but have been unable to find a solution.

 

[rpaulson]

i use the method described here:

VBA - working with data videos | Excel VBA Part 56.1 - Connect to SQL Server using ADO

www.wiseowl.co.uk

 

[GATA_Spread]

Thanks, but I don't see where it shows how to reference a range. Here is the code I've tried without success:

Private Sub SKU_Tracker()



Dim cn As ADODB.Connection

Set cn = New ADODB.Connection



Dim strConn As String



strConn = "Provider=SQLOLEDB;"



strConn = strConn & "server=<server name>;INITIAL CATALOG=<DB Name>;"



strConn = strConn & " INTEGRATED SECURITY=sspi;"



cn.Open strConn





ActiveSheet.Range("B2:F100").Clear

Dim SKUNumber As String



SKUNumber = Range("A2:A100").Value



Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset



SQLStr = "Select ONLINE_SKU, MODEL, BRAND, VENDOR, from <table name> WHERE RETAIL_SKU = " & ItemNumber & ""



rs.Open SQLStr, cn



rs.Close

cn.Close

Set rs = Nothing

Set cn = Nothing

 

[rpaulson]

I think you will need to build the where string to include you list of up to 100 items

example:

......
items =""
for r = 1 to Cells(Rows.Count, "A").End(xlUp).Row
   items=items & cells(r,"A") & "," 'build the list
next r

ItemNumber="in (" & items & ")" ' this will create the list       example:  "in (123,456,789,abc,def)"

SQLStr = "Select ONLINE_SKU, MODEL, BRAND, VENDOR, from <table name> WHERE RETAIL_SKU = " & ItemNumber & ""