Using Access database to "populate" Excel Sheets

[maacmaac]

Please help!!! I am willing to PAY anyone who can get this to run fo
me.

I have been trying for 5 days now trying to use a DBVlookup function t
populate fields in Excel. I used examples from 4 different forums usin
this function, but I cannot get any of them to work. I am somewhat ne
to VBA so forgive me if I am not making sense in my questions.

I have Excel Spreadsheet called "Account_Number". It is set up a
follows:

Column A is called "Account Number"..... Column B is called "Looked u
description in Access".
Account Number Description
5
=DBVlookup(..........)
8
=DBVlookup(..........)
2
=DBVlookup(..........)
4
=DBVlookup(..........)

Actual code I am using in B2 is as follows:

=DBVlookup("tblAccountMapping","AccountNumber",A2,"AccountDescription")

Actual code I am using in B3 is as follows:

=DBVlookup("tblAccountMapping","AccountNumber",A3,"AccountDescription")


I have Access Spreadsheet called "Account_Item" and within that file i
a table called "tblAccountMapping". Within the table are two columns.
Column A is called "AccountNumber"; Column B is calle
"AccountDescription".

The steps I have taken to create are as follows:

1. create excel spreadseet
2. create access database
3. open VBA using Alt+F11; Go to Tools|References|& check "Microsof
ActiveX Data Objects 2.8 Library
4. while still in VBA; Go to Insert|Modules
5. once in modules, I am pasting the following code:

Dim adoCN As ADODB.Connection
Dim strSQL As String

Public Function DBVLookUp(TableName As String, LookUpFieldName A
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 numbe
then remove the two '
adoRS.Open strSQL, adoCN, adOpenForwardOnly, adLockReadOnly
DBVLookUp = adoRS.Fields(ReturnField).Value
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 Acces
97
adoCN.ConnectionString = "C:\Documents and Settings\Mike\M
Documents\Account_Item.mdb" 'Change to your DB path
adoCN.Open
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation, "An error occurred"

End Sub



These are the steps I have taken. I keep receiving the same error i
Excel "#VALUE!"

I don't know what to do at this point.

Like I mentioned above....I am willing to give pay a fee to anyon
living in NYC who would be willing to assist me in setting this up.
work in the Financial District by Wall Street

 

[Guest]

Assuming that there is only one match for lookupvalue:

Dim adoCN As ADODB.Connection
Dim strSQL As String

Public Function DBVLookUp(TableName As String, LookUpFieldName As String,
LookupValue As String, ReturnField As String) As String
Dim adoRS As ADODB.Recordset
If adoCN Is Nothing Then SetUpConnection

If Isnumeric(lookupvalue) then
' If lookup value is a number then remove the two ' WHAT TWO?? beginning/end?
end If

Set adoRS = New ADODB.Recordset
strSQL = "SELECT " & ReturnField & " FROM " & TableName & " WHERE " &
LookUpFieldName & _
"='" & LookupValue & "';" adoRS.Open strSQL, adoCN, adOpenForwardOnly,
adLockReadOnly
DBVLookUp = adoRS.getrows(1)
' OR to force a String return DBVLookUp = Cstr(adoRS.getrows(1))
adoRS.Close
End Function