Posted to microsoft.public.excel.programming
|
|
Add columns of data from SQL
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
"ToniS" wrote:
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
"Tom Ogilvy" wrote:
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=NewSport sWeb"
'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:=strConnect ion, _
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/...php?t=envbadac
--
Regards,
Tom Ogilvy
"ToniS" wrote:
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
|