Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with hyperlinks when moving destination file in excel | Excel Discussion (Misc queries) | |||
QueryTables.Add and XML | Excel Programming | |||
Copy destination problem | Excel Programming | |||
QueryTables Add | Excel Programming |