Add columns of data from SQL

[Guest]

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

 

[Guest]

show your code and someone can probably give better advice.

 

[Guest]

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=NewSportsWeb"

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:=strConnection,
Destination:=Range("B3:B8"), Sql:=strSQL)

.Refresh
End With
Else
Exit Sub
End If
Next cell1

 

[Guest]

Maybe just pull the data into another place in excel, lookup the information
you want, then delete the table:

'setup Connection String
strConnection = "ODBC;DSN=NewSportsWeb;UID=;PWD=;Database=NewSportsWeb"
'setup the select statement
strSQL = "SELECT UL.Name, ML.Store#, ML.StoreName " & _
"FROM UserList UL " & _
"INNER JOIN MemberList ML ON UL.MemberID = ML.MemberID "
' implement connection, run qury and place
'results to the spreadsheet
With ActiveSheet.QueryTables.Add(Connection:=strConnection, _
Destination:=Range("T1"), Sql:=strSQL)
.Refresh
End With
set rng = Range("T1").currentRegion
set rng1 = Range(Range("A4"),Range("A4").End(xldown))
for each cell in rng1
cell.offset(0,1).Formula = "=Vlookup(" & _
cell.Address & "," & rng.Address & ",2,False)"
Next
rng1.offset(0,1).Formula = rng1.(0,1).Value
'rng.EntireColumn.Delete

as a representative of how you might do it.

another approach is to put the information using ADO and use that to
populate the values

http://www.erlandsendata.no/english/index.php?t=envbadac

 

[Guest]

Thanks for the below suggestions, I will try them out!! Do you happen to
know what controls the headers being added to the excel spreadsheet? I would
like to not have them print. ex. 'Store#' and 'StoreName' prints, I would
like to add my own headers.

Thanks again
Tonis

 

[Guest]

On the below line I am getting a syntext error, I am not sure what the
problem
is,

thanks ahead of time

rng1.offset(0,1).Formula = rng1.(0,1).Value