QueryTables.Add - Destination problem
I am trying to add columns of data based off of data with in Column A.
Ex. Column A will contain the userID (GUID) Based off of that, I would like
to
Pull the username, Phone number etc from the SQL server database table and
insert that information into the Excel Spreadsheet. For now I was just
trying to get the Username... I am getting the username correctly but having
troubles putting the data where I want it. In the below
ActiveSheet.QueryTables.Add call I Set the Destination to ("B3:B8") and it
adds the data w/i B3,C3,D3,E3 ... How do I specify the data to go into cell
B3, b4, b5, b6 based off of the userID
spreadsheet What I want it to look like
column A Col
B Col C
UserID
UserName Phone
{45BC3B68-EF24-4AFB-8FA7-A82FF70CB03A} Mark
999-999-9999
{45BC3B68-EF24-4AFB-8FA7-A82FF70CB03B} Tim
{45BC3B68-EF24-4AFB-8FA7-A82FF70CB03C} John
It currently is doing the following..
{45BC3B68-EF24-4AFB-8FA7-A82FF70CB03A} Mark Tim
John
{45BC3B68-EF24-4AFB-8FA7-A82FF70CB03B}
{45BC3B68-EF24-4AFB-8FA7-A82FF70CB03C}
below is the VBA
Dim cnn As ADODB.Connection
Dim strSQL As String
Dim strConnection As String
Dim UserIDCell As Variant ' use something beside variant
Dim CellAddress As Variant ' use something beside variant
Dim cell1 As Variant ' use something beside variant
'setup Connection String
strConnection = "ODBC;DSN=NewSportsWeb;UID=;PWD=;Database=NewSport sWeb"
For Each cell1 In Range(Range("A4"), Range("A10"))
If (cell1 < Empty) Then
' setup the select statement
strSQL = "SELECT UserName FROM UserList WHERE UserID = " & "'" &
cell1 & "'"
MsgBox ("sql = " & strSQL) ' tmhx
' 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
End Sub
Ideally I will like to pass in a variable for the range based on how many
UsersID exist in the spreadsheet (will vary)
Any help will be greatly appreciated
ToniS
|