Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    6

    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.

  2. #2
    Join Date
    Mar 2011
    Posts
    6

    Update

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •