Using Access database to "populate" Excel Sheets

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

I have been trying for 5 days now trying to use a DBVlookup function to populate fields in Excel. I used examples from 4 different forums using this function, but I cannot get any of them to work. I am somewhat new 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 as follows:

Column A is called "Account Number"..... Column B is called "Looked up 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 is a table called "tblAccountMapping". Within the table are two columns. Column A is called "AccountNumber"; Column B is called "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 "Microsoft 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 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
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 Access 97
adoCN.ConnectionString = "C:\Documents and Settings\Mike\My 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 in 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 anyone living in NYC who would be willing to assist me in setting this up. I work in the Financial District by Wall Street.

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

"maacmaac" wrote:

>
> Please help!!! I am willing to PAY anyone who can get this to run for
> me.
>
> I have been trying for 5 days now trying to use a DBVlookup function to
> populate fields in Excel. I used examples from 4 different forums using
> this function, but I cannot get any of them to work. I am somewhat new
> 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 as
> follows:
>
> Column A is called "Account Number"..... Column B is called "Looked up
> 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 is
> a table called "tblAccountMapping". Within the table are two columns.
> Column A is called "AccountNumber"; Column B is called
> "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 "Microsoft
> 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 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
> 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 Access
> 97
> adoCN.ConnectionString = "C:\Documents and Settings\Mike\My
> 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 in
> 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 anyone
> living in NYC who would be willing to assist me in setting this up. I
> work in the Financial District by Wall Street.
>
>
> --
> maacmaac
> ------------------------------------------------------------------------
> maacmaac's Profile: http://www.excelforum.com/member.php...fo&userid=2959
> View this thread: http://www.excelforum.com/showthread...hreadid=468677
>
>