Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 412
  • Last Modified:

Importing Data Into Excel From SQL

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)
1 Solution
aelliso3Author Commented:
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


Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now