Importing Data Into Excel From SQL

Posted on 2009-02-24
Last Modified: 2012-05-06
I have an excel sheet that I need to add a column to. Basically, the excel sheet has a column of CustID. Since the workbook was given to me with formula's, formatting, etc. I have to pull the State into the excel sheet instead of exporting out of excel and then back in(becuase it looses it formatting. How can I import data from the SQL server into excel and populate each of the CustID's with the State. (**Note: There are some duplicate CustID's in the excel sheet)
Question by:aelliso3
    1 Comment
    LVL 11

    Accepted Solution

    OK .. .found a way to do it if anyone else runs into this:

    Dim adoCN As ADODB.Connection
    Dim strSQL As String
    Const DatabasePath As String = "C:\MyDatabase.mdb"  'amend as required
    'Function argument descriptions
    'LookupFieldName - the field you wish to search
    'LookupValue - the value in LookupFieldName you're searching for
    'ReturnField - the matching field containing the value you wish to return
    Public Function DBVLookUp(TableName As String, _
                              LookUpFieldName As String, _
                              LookupValue As String, _
                              ReturnField As String) As Variant
        Dim adoRS As ADODB.Recordset
        If adoCN Is Nothing Then SetUpConnection
        Set adoRS = New ADODB.Recordset
        strSQL = "SELECT " & LookUpFieldName & ", " & ReturnField & _
                 " FROM " & TableName & _
                 " WHERE " & LookUpFieldName & "='" & LookupValue & "';"
                 ' If lookup value is a number then remove the two '
        adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
        If adoRS.BOF And adoRS.EOF Then
            DBVLookUp = "Value not Found"
            DBVLookUp = adoRS.Fields(ReturnField).Value
        End If
    End Function
    Sub SetUpConnection()
        On Error GoTo ErrHandler
        Set adoCN = New Connection
        adoCN.Provider = "Microsoft.Jet.OLEDB.4.0"  'Change to 3.51 for Access 97
        adoCN.ConnectionString = "Data Source=" & DatabasePath & ";Jet OLEDB:Database Password=********;"
        Exit Sub
        MsgBox Err.Description, vbExclamation, "An error occurred"
    End Sub

    Open in new window


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
    MS Access 2003 or later To MySQL Migration Project Hello All, this is my second article in the category of MS-OFFICE Automation. In internet I am not able to find any comprehensive resource on the Migration of MS Access back-end to MySQL so I fin…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now