CODEWITHSUNDEEP
sqlexcelvbat-sql
ben800
ben800

Reputation: 15

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

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

Upvotes: 0

Views: 2064

Answers (1)

JNevill
JNevill

Reputation: 50119

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.

Upvotes: 1

Related Questions