Getting data from Access into Excel

[PhilipLawrence9]

I have a problem getting data from Access into Excel....

We have a spreadsheet at work which we use for quoting customers. the problem is that now we have taken on board some new products the list for items is now up to 91,200 which is too many lines for a spreadsheet. I've exported all the data into an Access database and now want to link this back to the spreadsheet so when I type in a part number (for example, ABC123) it goes and looks for that number in the database and brings back a description and price and code for that particualr item into specific cells on the spreadsheet. With me so far? I've used vlookup in the past when it was all in the spreadsheet but am uncertain how to do the link to a database.

I look forward to hearing from all you extremely clever people out there in Excel Land.

Cheers,

 

[Hayley Baxter]

File menu, Get External Data, Link tables. In the link dialoug box choose excel. In the look in box choose the path for your spreadsheet. You can choose to link all the data or a selected range of cells.

Hay

 

[Graham T]

Philip

You could run a new database query from Excel to return filtered information dependant on criteria required.

If you go toData >> Get External Data >> Create New Query (Excel 97) or New Database Query (Excel 2000).

You will then choose the Data Source (Access Database) >> Select the database to query >> Choose the table or columns to query >> Apply the filter using the criteria you wish to search on.

You can Save this query to run again at any time.

HTH

Post back if you need further info.

Graham

 

[PhilipLawrence9]

Thanks for the replies. I've looked at the data//import external data/new database query stuff and it seems to bring back all of the data in the spreadsheet.

To give some more background on what I want to do.....

I need to enter a value in a cell in an excel spreadsheet and for the next cell along to query what I've entered and bring back a value specifically related to that information. I know i'm sounding a bit thick on this but queries are new to me....

If you want a cut down copy of the database then please let me know.

Regards,

Phil

 

[Graham T]

Philip

You can apply a criteria to the import once you get to the filter section of the wizard.

For example in your Part Number field you can return a value equal to ABC123.

HTH

 

[PhilipLawrence9]

Hi there,

have looked at the filters and there's not one that allows me to reference a cell in the worksheet which is what i need to do to pull the information from the database. Hope this makes sense and I look forward to hearing from you soon.

Cheers,

Phil

 

[Graham T]

Philip

Would you like to attach a copy of the database (zipped) to this thread.

I will then take a look at it for you.

Graham

 

[PhilipLawrence9]

here's a compressed cut down version of the database. only got 17 lines in it whereas the live version has 91200!!

Cheers,

Phil

 

[Graham T]

Phil

Unfortunatly, the test database does not contain any records or tables....

Can you repost

Graham

 

[PhilipLawrence9]

try this one. sorry for the error last time

 

[Graham T]

Phil

Please see attached document for details.

I have had to remove a lot of the pictures as the size exceeded the limit.

Hopefully this should show you enough for you to go along with. If not I'll have to e-mail you the full document.

It may actually be as quick to do this whole process in Access, although you may have a reason not to.

HTH

Graham

 

[PhilipLawrence9]

Graham,

think we're getting there on this. On pic #2 where you have entered the "equals" "xxxxx" i need this to be a cell reference, i.e A2 on the spreadsheet where I want the data to be......

Regards,

Phil

 

[Graham T]

Phil

The filter will return only a Part Number (Or whatever you wish to filter by) from the list of Parts in the database.

Are you saying that you want to include a cell reference in this list or are you wanting to return the actual results to Cell A2 in the worksheet?

Graham

 

[PhilipLawrence9]

Hi Graham,

I need to value I enter into A2 on the spreadsheet to be the part number in the Access database and for the query to pull back the description, price, etc. into adjoining fields on the spreadsheet.

Any clearer???

Regards,

Phil

 

[Graham T]

Phil

I'm sorry, but I don't think there is anyway that you will be able to do that using this feature.

Basically you are wanting the functionality of the VLookup, but don't have the space to store all of the data.

Sorry but you may need to look at alternative options.

Graham

 

[PhilipLawrence9]

Graham,

Thanks for you help on this even though we didn't manage to get it done.

hope you have a good weekend and maybe see you again on here.

Cheers,

Phil