Quey Access within Excel

[pkmoore]

I have a list of part numbers in excel that I want to get external data for. I wrote a VB script to go get the data, but it does it one cell at a time. Connects, fetches, returns data, then moves to next cell and connects, fetches, returns data, over and over until it reaches a blank cell.

I would like to pass it the entire list of part numbers (join with an external table) then return all the data at once!

Can you help?
I can cut and paste the list into access but don't want to.

 

[mmandk9]

pkmoore,

Not sure this will help, but this is how I would approach it. Using Access' DoCmd.TransferSpreadsheet command, import your whole list into a temp table in Access. Then run the SQL/query for the whole table at once. You should then be able to export all the query results (again using the DoCmd.TransferSpreadsheet command). The catch is when you export the results, I am pretty sure you have to export into a new spreadsheet. However, it becomes a simple copy and paste routine at that point...

Hope this helps...

MM

 

[ETID]

Have you tried to link to the spreadsheet from within access?

then your linked spreadsheet will emulate a table that virtually resides in access.

when changes are made to the xls they are automaticaly updated in access via the link...

...no code to write

it's like a window to your spreadsheet.


You can also link inversly from within excel to an access table or query

 

[pkmoore]

Thanks, for the replys!!

But here is my problem. I don't want the user (user of the spreadsheet) to even know that it is getting the data from Access.

Here is what I am doing today:

Part ID COST
------- ------
1234567
8277282
7728821
2099288
7782991

Now the user hits a button on the toolbar and the costs are fetched and put into the spreadsheet automatically for them.

The problem is the VB script goes out and connects to the data source runs the query and returns one (1) cost for the first item. Then it logs into the data source again and fetches the next one and so on. Very slow having to log in every time and rerun the query every time. I want to present the entire list of part id's and return ALL the costs at once.

Thanks for any sugestions.
Pat..

 

[ETID]

Create a live link to the table or query from within excel,

From excel click Data>Get External Data>New Database Query>....the choose msaccess databases* from the list...follow the wizard from there (to select your mdb, table or query)
then return your data to excel.

You can opt to use the wizard or MsQuery (a gui type interface, similar to access)at this point...
after your comfy with this...I would recommend the MsQuery option.


when data is returned,...you can right click in the data and select properties to refresh on open...auto fill formulas etc.

this is now a live link, any time the workbook is opened. your current / up to date access data comes in (if you set the refresh on open option)

YOU CAN ALSO HIDE THIS SHEET...SO END USERS ARE NOT AWARE OF IT EVEN BEING IN THE WORKBOOK.

Try it...there is a lot of flexibility built in so you can customize (through query parameters)what data comes in.