Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 328
Default vlookup to txt file or database

Is there a way to process a vlookup to a text file or access database.

I have very large access files that if needed i can convert to text files.
I need to pull a couple of columns to an excel file based on one column
possibly via a vlookup. This excel file data is constantly changing and i
need to pull data from a database.

What would be the best way get the data when needed into the excel file?
--
lmo
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default vlookup to txt file or database

Best way, with least work in the long run for you, is to probably use Excel's
Import Data features to create a 'query' into the Access database directly.

Start by using Excel's Help and enter
About exchanging data between Excel and Access
as the search subject. There's actually a topic of that title (Excel 2003).
Look at the section on "Bring refreshable Access data into Excel" for more
guidance and examples.

Hope this helps get you started -- you can always ask more questions as you
dig in to your solution.


"Lisa" wrote:

Is there a way to process a vlookup to a text file or access database.

I have very large access files that if needed i can convert to text files.
I need to pull a couple of columns to an excel file based on one column
possibly via a vlookup. This excel file data is constantly changing and i
need to pull data from a database.

What would be the best way get the data when needed into the excel file?
--
lmo

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 328
Default vlookup to txt file or database

Yes this suggestion is good the only issue I am having is when I choose the
Parameters area it only lets be pull data from one cell i need to pull data
from a range and it does not allow this; see my example below.

Based on the data in column A and B i need information queried to populate
columns C, D and E. Each column A and B combination information is unique.

Column
A B C D E
data1 data1 queried queried quiered
data2 data2 queried queried quiered
etc...

--
lmo


"JLatham" wrote:

Best way, with least work in the long run for you, is to probably use Excel's
Import Data features to create a 'query' into the Access database directly.

Start by using Excel's Help and enter
About exchanging data between Excel and Access
as the search subject. There's actually a topic of that title (Excel 2003).
Look at the section on "Bring refreshable Access data into Excel" for more
guidance and examples.

Hope this helps get you started -- you can always ask more questions as you
dig in to your solution.


"Lisa" wrote:

Is there a way to process a vlookup to a text file or access database.

I have very large access files that if needed i can convert to text files.
I need to pull a couple of columns to an excel file based on one column
possibly via a vlookup. This excel file data is constantly changing and i
need to pull data from a database.

What would be the best way get the data when needed into the excel file?
--
lmo

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default vlookup to txt file or database

Lisa,
Even a VLOOKUP() would not handle multi-field match, not without some smoke
and mirrors of some type, although a variation of SUMPRODUCT() might do the
trick.

If you'd like to try to work out a solution with me 'off-line', feel free to
get in touch with me at (remove spaces) Help From @JLatham site.com


"Lisa" wrote:

Yes this suggestion is good the only issue I am having is when I choose the
Parameters area it only lets be pull data from one cell i need to pull data
from a range and it does not allow this; see my example below.

Based on the data in column A and B i need information queried to populate
columns C, D and E. Each column A and B combination information is unique.

Column
A B C D E
data1 data1 queried queried quiered
data2 data2 queried queried quiered
etc...

--
lmo


"JLatham" wrote:

Best way, with least work in the long run for you, is to probably use Excel's
Import Data features to create a 'query' into the Access database directly.

Start by using Excel's Help and enter
About exchanging data between Excel and Access
as the search subject. There's actually a topic of that title (Excel 2003).
Look at the section on "Bring refreshable Access data into Excel" for more
guidance and examples.

Hope this helps get you started -- you can always ask more questions as you
dig in to your solution.


"Lisa" wrote:

Is there a way to process a vlookup to a text file or access database.

I have very large access files that if needed i can convert to text files.
I need to pull a couple of columns to an excel file based on one column
possibly via a vlookup. This excel file data is constantly changing and i
need to pull data from a database.

What would be the best way get the data when needed into the excel file?
--
lmo

  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 328
Default vlookup to txt file or database

The tables that I am pulling the information from is an ODBC database, but
other users are not authorized to have access to these tables. So my
solution was to create an access database that I would dump this information
into and they would have the ability to get the information needed from this
database via excel.

Like I stated previously the user would be responsible for typing in 2
columns (the combination of these 2 columns creates unique information) and
based on these 2 columns of information it would populate 3 additional
columns. A vlookup works great by combining the 2 columns in the excel file
and in the lookup information, but this information(database) is too large to
put into excel.

I dont know if this additional information makes it any easier for you to
make some suggestions.
--
lmo


"JLatham" wrote:

Lisa,
Even a VLOOKUP() would not handle multi-field match, not without some smoke
and mirrors of some type, although a variation of SUMPRODUCT() might do the
trick.

If you'd like to try to work out a solution with me 'off-line', feel free to
get in touch with me at (remove spaces) Help From @JLatham site.com


"Lisa" wrote:

Yes this suggestion is good the only issue I am having is when I choose the
Parameters area it only lets be pull data from one cell i need to pull data
from a range and it does not allow this; see my example below.

Based on the data in column A and B i need information queried to populate
columns C, D and E. Each column A and B combination information is unique.

Column
A B C D E
data1 data1 queried queried quiered
data2 data2 queried queried quiered
etc...

--
lmo


"JLatham" wrote:

Best way, with least work in the long run for you, is to probably use Excel's
Import Data features to create a 'query' into the Access database directly.

Start by using Excel's Help and enter
About exchanging data between Excel and Access
as the search subject. There's actually a topic of that title (Excel 2003).
Look at the section on "Bring refreshable Access data into Excel" for more
guidance and examples.

Hope this helps get you started -- you can always ask more questions as you
dig in to your solution.


"Lisa" wrote:

Is there a way to process a vlookup to a text file or access database.

I have very large access files that if needed i can convert to text files.
I need to pull a couple of columns to an excel file based on one column
possibly via a vlookup. This excel file data is constantly changing and i
need to pull data from a database.

What would be the best way get the data when needed into the excel file?
--
lmo



  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,203
Default vlookup to txt file or database

Well, there are still options to explore. Some are potentially more labor
intensive than the other. I actually have Excel workbooks available that can
read large .csv files into Excel - either ones with more than 65536 rows OR
more than 255 columns (but not both). Just in case:
Import .csv files with more than 65K rows into Excel 2003:
http://www.jlathamsite.com/uploads/I...sOfData_R1.xls
and import .csv files with greater than 255 columns into Excel 2003:
http://www.jlathamsite.com/uploads/I...umnsOfData.xls
(right click and choose save target as)

Hopefully the 1st one would be of some help, but the problem then becomes
that you have to build a complex VLOOKUP() formula that would examine all the
sheets, returning the information you need from the sheet it finds it on.

However, we should be able to build a query in code and look directly into
the Access database and pull the information out of a table. But I'm a
little fuzzy on exactly what the user does: you say they type 2 values into
columns and then the information is returned and placed into columns next to
those. I understand that, but my question is whether they always type the
entries into the same row, or could they be entering several rows of data
pairs at one time?

A final option is to output the .csv file from Access and then come up with
a custom VBA routine to open the file, read it and pull out the data you need
from it. That can be done - I actually have done that at my dayjob on files
that a proprietary database creates: we can't dig directly into the Db, but
we can get it to output results to a .csv file and we read from that. It
just takes some time to write the code and to do that, the content and format
of the .csv/.txt file has to be well documented and understood.


"JLatham" wrote:

Lisa,
Even a VLOOKUP() would not handle multi-field match, not without some smoke
and mirrors of some type, although a variation of SUMPRODUCT() might do the
trick.

If you'd like to try to work out a solution with me 'off-line', feel free to
get in touch with me at (remove spaces) Help From @JLatham site.com


"Lisa" wrote:

Yes this suggestion is good the only issue I am having is when I choose the
Parameters area it only lets be pull data from one cell i need to pull data
from a range and it does not allow this; see my example below.

Based on the data in column A and B i need information queried to populate
columns C, D and E. Each column A and B combination information is unique.

Column
A B C D E
data1 data1 queried queried quiered
data2 data2 queried queried quiered
etc...

--
lmo


"JLatham" wrote:

Best way, with least work in the long run for you, is to probably use Excel's
Import Data features to create a 'query' into the Access database directly.

Start by using Excel's Help and enter
About exchanging data between Excel and Access
as the search subject. There's actually a topic of that title (Excel 2003).
Look at the section on "Bring refreshable Access data into Excel" for more
guidance and examples.

Hope this helps get you started -- you can always ask more questions as you
dig in to your solution.


"Lisa" wrote:

Is there a way to process a vlookup to a text file or access database.

I have very large access files that if needed i can convert to text files.
I need to pull a couple of columns to an excel file based on one column
possibly via a vlookup. This excel file data is constantly changing and i
need to pull data from a database.

What would be the best way get the data when needed into the excel file?
--
lmo

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
VB - non-interactive way of creating database from text file Hilton Excel Discussion (Misc queries) 1 October 9th 08 08:36 PM
Linking access database to an excel file PABHL Excel Discussion (Misc queries) 1 June 8th 06 08:35 PM
Database Connection Nested VLookup option needyouradvice Excel Discussion (Misc queries) 1 May 9th 06 01:09 AM
vlookup and Access Database Table finster26 Excel Discussion (Misc queries) 1 January 6th 06 11:17 PM
.csv file creation for importing into a database Ratbert66251 Setting up and Configuration of Excel 4 February 2nd 05 11:17 PM


All times are GMT +1. The time now is 06:13 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"