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

x
  • 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)
0
aelliso3
Asked:
aelliso3
1 Solution
 
aelliso3Author Commented:
OK .. .found a way to do it if anyone else runs into this:
=DBVLOOKUP(TableName,LookupFieldName,LookupValue,ReturnField)

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"
    Else
        DBVLookUp = adoRS.Fields(ReturnField).Value
    End If
    adoRS.Close
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=********;"
    adoCN.Open
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation, "An error occurred"
End Sub

Open in new window

0

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