VB Script in Excel that Updates Worksheet from MS SQL

tetros · #1 (**permalink**) 03-24-11, 16:18

Hello all. I am new to databases and am learning Visual Basic. What I'm basically trying to do is update an existing Excel Worksheet using Visual Basic and populate a field from a MS SQL database.

Here's my example of what I'm trying to do.

On my Excel sheet, Column1 & Column2 have the people's first and last name, and Column3 has the company they're from. I would like to use the combined data from the first 3 columns (which all exist in the same data-table in my database) and reference our Database to collect their unique ID and write it onto the Excel sheet in Column 4.

While I'm waiting for a response I will be actively working on figuring out how to do this and if I happen to find the code I need I will make sure to update this thread.

tetros · #2 (**permalink**) 03-25-11, 11:38

Just an update. I managed to figure out a basic connection to data transfer SQL Server contents into my Excel file with a Visual Basic Script!

Here's my code.

Code:

Sub Retrieve()
' Declare the QueryTable object
Dim qt As QueryTable
 
' Set up the SQL Statement
sqlstring = "select Column1, Column2 from TableName"
 
' Set up the connection string, reference an ODBC connection
connstring = _
 "ODBC;DSN=servername;UID=id;PWD=pw;Database=dbname"
 
' Now implement the connection, run the query, and add
' the results to the spreadsheet starting at row A1
With ActiveSheet.QueryTables.Add(Connection:=connstring, Destination:=Range("A1"), Sql:=sqlstring)
.Refresh
End With


End Sub

Basically it takes the columns of the table [TableName] (column1 and column2) and inserts it into the Excel columns A1 and B1.

The next step is to find out how to use 3 excel columns as a "Key" in order to extract the correct data from the SQL Server database. Any help would be welcome, however I'll continue to update this thread regardless.

Internet Services

Web Development

Digital Marketing

Consumer Tech