Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to pull data from SQL server based on cell contents
Hello.
I'm trying to figure out exactly how to use VBA to look at the contents of a cell, then query a SQL server using that data and return results to several other cells. My server name is Goober and the table I want to pull the data from is Jeff. In ODBC, I have the Server connection called Testing basically I need to look at the contents of cell A1, and then run a statement that does the following: Output to cell A2 the results of the SQL query select CreatedDate from Goober..Jeff where InvoiceNumber = (Cell A1) Output to cell A3 the results of the SQL query select CreatedByUser from Goober..Jeff where InvoiceNumber = (Cell A1) I'm well versed in SQL, but I am a VBA novice, so I don't know how to set up my connections (with VBA code) or what the difference is between ADO and DAO, so could you please include that in your answer? Also, I'd need to know if I need to set any References. Thanks! Ken |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to pull data from SQL server based on cell contents
Hi Ken
Have a try with this, using ADO : Sub TEST() 'this requires to make a ref via tool 'to Microsoft ActiveX Data Object 2.x Dim cn As ADODB.Connection Dim rec As ADODB.Recordset Set cn = New Connection Set rec = New Recordset 'open the connection cn.Open "Provider=SQLOLEDB.1;Data Source=GOOBER", "userID", "password" 'open the recordset rec.Open "select CreatedDate from Jeff where InvoiNumber= '" & Range("A1") & "';", cn, adOpenForwardOnly, adLockOptimistic Do While rec.EOF = False Range("a2") = rec.Fields("CreatedDate").Value 'or to copy an entire recorset 'Range("A1").CopyFromRecordset rec 'optional for multiple records 'rec.MoveNext Loop rec.Close cn.Close Set rec = Nothing Set cn = Nothing End Sub Regards JY "Ken" wrote in message ... Hello. I'm trying to figure out exactly how to use VBA to look at the contents of a cell, then query a SQL server using that data and return results to several other cells. My server name is Goober and the table I want to pull the data from is Jeff. In ODBC, I have the Server connection called Testing basically I need to look at the contents of cell A1, and then run a statement that does the following: Output to cell A2 the results of the SQL query select CreatedDate from Goober..Jeff where InvoiceNumber = (Cell A1) Output to cell A3 the results of the SQL query select CreatedByUser from Goober..Jeff where InvoiceNumber = (Cell A1) I'm well versed in SQL, but I am a VBA novice, so I don't know how to set up my connections (with VBA code) or what the difference is between ADO and DAO, so could you please include that in your answer? Also, I'd need to know if I need to set any References. Thanks! Ken |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to pull data from SQL server based on cell contents
Thank you so much!
I had to massage the code a little bit because the SQL connect string wasn't exactly right for our server, but other than that, it worked great! "Jean-Yves" wrote: Hi Ken Have a try with this, using ADO : Sub TEST() 'this requires to make a ref via tool 'to Microsoft ActiveX Data Object 2.x Dim cn As ADODB.Connection Dim rec As ADODB.Recordset Set cn = New Connection Set rec = New Recordset 'open the connection cn.Open "Provider=SQLOLEDB.1;Data Source=GOOBER", "userID", "password" 'open the recordset rec.Open "select CreatedDate from Jeff where InvoiNumber= '" & Range("A1") & "';", cn, adOpenForwardOnly, adLockOptimistic Do While rec.EOF = False Range("a2") = rec.Fields("CreatedDate").Value 'or to copy an entire recorset 'Range("A1").CopyFromRecordset rec 'optional for multiple records 'rec.MoveNext Loop rec.Close cn.Close Set rec = Nothing Set cn = Nothing End Sub Regards JY "Ken" wrote in message ... Hello. I'm trying to figure out exactly how to use VBA to look at the contents of a cell, then query a SQL server using that data and return results to several other cells. My server name is Goober and the table I want to pull the data from is Jeff. In ODBC, I have the Server connection called Testing basically I need to look at the contents of cell A1, and then run a statement that does the following: Output to cell A2 the results of the SQL query select CreatedDate from Goober..Jeff where InvoiceNumber = (Cell A1) Output to cell A3 the results of the SQL query select CreatedByUser from Goober..Jeff where InvoiceNumber = (Cell A1) I'm well versed in SQL, but I am a VBA novice, so I don't know how to set up my connections (with VBA code) or what the difference is between ADO and DAO, so could you please include that in your answer? Also, I'd need to know if I need to set any References. Thanks! Ken |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Using VBA to pull data from SQL server based on cell contents
Hi Ken,
Glad it worked. You can also search on microsoft.support for different connection methods depending on the database such as oracle, sql, db, access or xl. If you made a ref to ado, also some explanation to find in help. Regards JY "Ken" wrote in message ... Thank you so much! I had to massage the code a little bit because the SQL connect string wasn't exactly right for our server, but other than that, it worked great! "Jean-Yves" wrote: Hi Ken Have a try with this, using ADO : Sub TEST() 'this requires to make a ref via tool 'to Microsoft ActiveX Data Object 2.x Dim cn As ADODB.Connection Dim rec As ADODB.Recordset Set cn = New Connection Set rec = New Recordset 'open the connection cn.Open "Provider=SQLOLEDB.1;Data Source=GOOBER", "userID", "password" 'open the recordset rec.Open "select CreatedDate from Jeff where InvoiNumber= '" & Range("A1") & "';", cn, adOpenForwardOnly, adLockOptimistic Do While rec.EOF = False Range("a2") = rec.Fields("CreatedDate").Value 'or to copy an entire recorset 'Range("A1").CopyFromRecordset rec 'optional for multiple records 'rec.MoveNext Loop rec.Close cn.Close Set rec = Nothing Set cn = Nothing End Sub Regards JY "Ken" wrote in message ... Hello. I'm trying to figure out exactly how to use VBA to look at the contents of a cell, then query a SQL server using that data and return results to several other cells. My server name is Goober and the table I want to pull the data from is Jeff. In ODBC, I have the Server connection called Testing basically I need to look at the contents of cell A1, and then run a statement that does the following: Output to cell A2 the results of the SQL query select CreatedDate from Goober..Jeff where InvoiceNumber = (Cell A1) Output to cell A3 the results of the SQL query select CreatedByUser from Goober..Jeff where InvoiceNumber = (Cell A1) I'm well versed in SQL, but I am a VBA novice, so I don't know how to set up my connections (with VBA code) or what the difference is between ADO and DAO, so could you please include that in your answer? Also, I'd need to know if I need to set any References. Thanks! Ken |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pull cell contents from one wksh to another if condition is met | Excel Worksheet Functions | |||
Reference Data Range based on cell contents | Charts and Charting in Excel | |||
I cannot select a single cell or pull down cell contents | Excel Worksheet Functions | |||
? how to pull data from another sheet based on value | Excel Programming | |||
Data searching based on cell contents using VBa | Excel Programming |