Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Extracting data from Access Database password protected | Excel Programming | |||
Extracting data from Access | Excel Programming | |||
Extracting data from Access - error encountered | Excel Programming | |||
Extracting Excel data and uploading to access | Excel Programming | |||
Extracting Access data | Excel Programming |