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

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

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


"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

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/...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=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
>
>
> "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

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=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/...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=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
> >
> >
> > "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

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=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/...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=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
> > >
> > >
> > > "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