Add columns of data from SQL
 

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

Add columns of data from SQL
 

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

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

Add columns of data from SQL
 

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

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