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

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