Excel vba create array and run sql query from given values multiple times

Question

I have a little bit problem with the following vba code:

Sub ADOExcelSQLServer()


Dim Cn As ADODB.Connection
Dim Server_Name As String
Dim Database_Name As String
Dim User_ID As String
Dim Password As String
Dim SQLStr As String
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset

Server_Name = "EXCEL-PC\EXCELDEVELOPER" ' Enter your server name here
Database_Name = "AdventureWorksLT2012" ' Enter your database name here
User_ID = "" ' enter your user ID here
Password = "" ' Enter your password here
SQLStr = "SELECT * FROM [SalesLT].[Customer] WHERE CountryID = 1" ' Enter your SQL here

Set Cn = New ADODB.Connection
Cn.Open "Driver={SQL Server};Server=" & Server_Name & ";Database=" & Database_Name & _
";Uid=" & User_ID & ";Pwd=" & Password & ";"

rs.Open SQLStr, Cn, adOpenStatic
 ' Dump to spreadsheet
With Worksheets("sheet1").Range("a1:z500") ' Enter your sheet name and range here
    .ClearContents
    .CopyFromRecordset rs
End With
 '            Tidy up
rs.Close
Set rs = Nothing
Cn.Close
Set Cn = Nothing
End Sub

I've got the following Vba code, and I would like to run the query from given values from an array. For example the User inputted the following values into the "A" column 1 2 3 4 5 6 under one another, after that I want to insert the result under another.For example I've got 26 result with the first query run, and the 2nd give me 50 so after the 2nd query I need to have 76 rows. Sorry for my bad English.

Also I've found the following code which helped me to get all the values into an array: https://social.msdn.microsoft.com/Forums/en-US/f5e97f3d-9857-469f-8255-18aa20512ba4/reading-a-range-into-vba-array?forum=isvvba

JNevill · Accepted Answer · 2018-05-10 20:28:04Z

1

You can Join() the array (turning from an array into a string delimited by some character) into your SQL statement

SQLStr = "SELECT * FROM [SalesLT].[Customer] WHERE CountryID IN (" & Join(YourArray, ",") & ") ORDER BY countryID;"

We use the IN condition to supply a list of countryids to the WHERE clause and ORDER BY CountryID so they are loaded into your sheet in order.

answered May 10, 2018 at 20:28

JNevill

50.6k4 gold badges46 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel vba create array and run sql query from given values multiple times

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related