Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
move two columns of data to four columns | Excel Discussion (Misc queries) | |||
Sorting Data into columns without replacing the columns with data | New Users to Excel | |||
Help in code Steve G wrote to move data from 4 columns to 21 columns | Excel Programming | |||
Arrange data spanning 8 columns and 3 rows to 24 columns and 1 row | Excel Discussion (Misc queries) | |||
Transposing three columns into one row after manipulating data in columns | Excel Discussion (Misc queries) |