Add columns of data from SQL
I have the following, It adds the column headers and the data, but it keeps
adding the information across column B3, C3, D3, E3, instead of down (b3,c3
,b4,c4, b5,c5). I even specify a range of B3:b8.. again I am pretty new with
all of this and not sure how to specify the placement of the data.
'setup Connection String
strConnection = "ODBC;DSN=NewSportsWeb;UID=;PWD=;Database=NewSport sWeb"
For Each cell1 In Range(Range("A4"), Range("A65536"))
If (cell1 < Empty) Then
' setup the select statement
strSQL = "SELECT UL.Name, ML.Store#, ML.StoreName " & _
"FROM UserList UL " & _
"INNER JOIN MemberList ML ON UL.MemberID =
ML.MemberID " & _ "WHERE UserId = " & " '" & cell1 & "'"
' implement connection, run qury and place results to the
spreadsheet
With ActiveSheet.QueryTables.Add(Connection:=strConnect ion,
Destination:=Range("B3:B8"), Sql:=strSQL)
.Refresh
End With
Else
Exit Sub
End If
Next cell1
"Tom Ogilvy" wrote:
show your code and someone can probably give better advice.
--
Regards,
Tom Ogilvy
"ToniS" wrote:
I have an excel spreadsheet with data in column A ONLY, based off of that
information I would like to add columns of data from SQL Server.
What would be the best method of doing that? Any examples would
be greatly appreciated, I am new to Excel/VBA. I was thinking of using
a Macro to execute the vba?
Ex. SpreadSheet
ID (colA) Store#(ColB) StoreName(ColC)
123 1 ABC Corp
333 3 Dan's Store
222 17 All Sports
I was able to create the connection sucessfully, and am familiar with how to
do
the Select statement, beyond that I am not sure how to add the data to the
Excel
Spreadsheet.
Again Column B and Column C would be added via the Macro based off
of the information in column A.
Any help would be greatly appreciated
ToniS
|