ransomedbyfire
Board Regular
- Joined
- Mar 9, 2011
- Messages
- 121
I am using VBA to pull data from an SQL database. I would like to run multiple queries back to back, looping through a column and plugging the value in each cell into an SQL query each time. Something like this:
But when I run the above code, Excel repeats the header row for the SQL query every time. How can I stop this from happening so that I can just have the data displayed neatly, row after row?
Code:
dividendscount = Sheet5.Range("a:a").Find(what:="", after:=[a1]).Row - 2
If dividendscount = 0 Then
GoTo nexti
End If
For i = 2 To dividendscount + 1
searchsymbol = Sheet5.Cells(i, 3)
stSQL = "SELECT DISTINCT Stuff FROM Tables WHERE LinkTransactions.LinkSymbol='" & searchsymbol & "';"
With Sheet2.ListObjects.Add(SourceType:=0, Source:=Array( _
"OLEDB;Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Data Source=.\portfoliocenter;Use Procedure for Prepare=" _
, _
"1;Auto Translate=True;Packet Size=4096;Workstation ID=LAPTOP;Use Encryption for Data=False;Tag with column collation when possib" _
, "le=False;Initial Catalog=PortfolioCenter"), Destination:=Sheet2.Range("$A" & (2 * i) - 3)). _
QueryTable
.CommandText = stSQL
.Refresh BackgroundQuery:=False
End With
nexti:
Next i