Extracting data from Access in Excel
 

Hello,

Wanted to know if there was any way of extracting data from Access by using
a "Vlookup type" function in Excel.

I have IDs in excel but want to enrich that data with information held in
Access..

Is this possible?

Extracting data from Access in Excel
 

Could be possible with the use of a UDF, but seems like it could
potentially add quite a bit of overhead. Is it possible for you to
have a hidden sheet that hits your database and refreshes whenever the
workbook is initially opened? Then you could use simple VLOOKUP
formulas to go against that sheet.
Shahid wrote:
Hello,

Wanted to know if there was any way of extracting data from Access by using
a "Vlookup type" function in Excel.

I have IDs in excel but want to enrich that data with information held in
Access..

Is this possible?

Extracting data from Access in Excel
 

The database is just over 200,000 records so pulling it into excel is a
pain....

" wrote:

On Oct 8, 10:26 am, Shahid wrote:
Hello,

Wanted to know if there was any way of extracting data from Access by using
a "Vlookup type" function in Excel.

I have IDs in excel but want to enrich that data with information held in
Access..

Is this possible?



I would imagine you would have to download a table from access to a
blank sheet then run a v-lookup against that table.

Extracting data from Access in Excel
 

Gotcha. Well, I've been wanting to throw this UDF together for a
while, and this just gave me an excuse. Paste the following code into
a regular module in your workbook.
Function databaseLookup(databaseFullName As String, _
databaseTableName As String, returnField As String, _
matchField As String, matchRange As Range)
Dim conn As Object, rs As Object, sql As String
sql = "SELECT [" & databaseTableName & "].[" & _
returnField & "] FROM `" & databaseFullName & _
"`.[" & databaseTableName & "] [" & _
databaseTableName & "] WHERE ([" & _
databaseTableName & "].[" & matchField & _
"]='" & matchRange.Text & "')"
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & databaseFullName
rs.Open sql, conn
databaseLookup = rs.Fields.Item(returnField)
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Function
The format of this function is:
=datbaseLookup(full path to database, full table name in that database
that you want to look in, the field to return if a match is found, the
field in that table to use as your match, the range to use as your
match criteria)

Then you can enter a formula such as below:
=databaseLookup("C:\database.mdb", "tbl", "return","match", A3)

In the above example, the function will look for the value in A3 in
the match field of the table named tbl in the C:\database.mdb
database. Once found, it will return the field named field within
that table.

As an option, you could store the name of the database in a cell and
reference it throughout your formulas.
In C1, place your database name. Then use the formula below to
reference that cell.
=databaseLookup($C$1, "tbl", "return","match", A3)

Shahid wrote:
The database is just over 200,000 records so pulling it into excel is a
pain....

" wrote:

On Oct 8, 10:26 am, Shahid wrote:
Hello,

Wanted to know if there was any way of extracting data from Access by using
a "Vlookup type" function in Excel.

I have IDs in excel but want to enrich that data with information held in
Access..

Is this possible?



I would imagine you would have to download a table from access to a
blank sheet then run a v-lookup against that table.

Extracting data from Access in Excel
 

ALso, keep in mind that, like I said earlier, this could add some
overhead to your workbook and I'm sure that the calculation could take
quite some time considering it is going through 200,000 records.

As an alternative to all of this, you could alwys import your
spreadsheet into Access and do a simple join query to produce the
desired results.

Good luck.
-Jeff-
JW wrote:
Gotcha. Well, I've been wanting to throw this UDF together for a
while, and this just gave me an excuse. Paste the following code into
a regular module in your workbook.
Function databaseLookup(databaseFullName As String, _
databaseTableName As String, returnField As String, _
matchField As String, matchRange As Range)
Dim conn As Object, rs As Object, sql As String
sql = "SELECT [" & databaseTableName & "].[" & _
returnField & "] FROM `" & databaseFullName & _
"`.[" & databaseTableName & "] [" & _
databaseTableName & "] WHERE ([" & _
databaseTableName & "].[" & matchField & _
"]='" & matchRange.Text & "')"
Set conn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & databaseFullName
rs.Open sql, conn
databaseLookup = rs.Fields.Item(returnField)
rs.Close
conn.Close
Set rs = Nothing
Set conn = Nothing
End Function
The format of this function is:
=datbaseLookup(full path to database, full table name in that database
that you want to look in, the field to return if a match is found, the
field in that table to use as your match, the range to use as your
match criteria)

Then you can enter a formula such as below:
=databaseLookup("C:\database.mdb", "tbl", "return","match", A3)

In the above example, the function will look for the value in A3 in
the match field of the table named tbl in the C:\database.mdb
database. Once found, it will return the field named field within
that table.

As an option, you could store the name of the database in a cell and
reference it throughout your formulas.
In C1, place your database name. Then use the formula below to
reference that cell.
=databaseLookup($C$1, "tbl", "return","match", A3)

Shahid wrote:
The database is just over 200,000 records so pulling it into excel is a
pain....

" wrote:

On Oct 8, 10:26 am, Shahid wrote:
Hello,

Wanted to know if there was any way of extracting data from Access by using
a "Vlookup type" function in Excel.

I have IDs in excel but want to enrich that data with information held in
Access..

Is this possible?



I would imagine you would have to download a table from access to a
blank sheet then run a v-lookup against that table.