Unanswered: VB Script in Excel that Updates Worksheet from MS SQL
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.
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.
' 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 = _
' 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)
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.