Connect Excel cell to Database Table cell

[Bitto]

working on excel file , wants to connect excel cell to database table cell, I have already created report the MS query , wants to pulls the data in excel cell from database table

for example in excel file in cell there is Item # 10006111 wants to pull Price from database table for this cell only, when I connect to this cell the report I created it, some how it brings all the fields from the report on the excel file, I just want to show the price only, is this can be done, and wants to add with parameter range also, this report I have created is with parameter, parameter can add only I item # one time not a range,

does any one have any idea, if this is possible,

I will be really greatful for the help.

[Arkadi]

Sorry, Im afraid I 1-don't understand what exactly you're asking, 2-would need a sample worksheet with current code if possible.. If you simply want to look up the price for an item from a table, you probably don't need queries, and can use a macro to simply get the price and put the value in the cell you require.

[Bitto]

thanks for quick reply, see attached the sample report, excel file and MS query

in excel file I have some Item# and below MS query report created from database, wants to pull price for this item 10006111 $ 10500.00 in E3 , is there a way to connect these both, if I enter the item in MS query the price can show in excel file, you mention some thing about macro, this report some user will be using it, trying to make it easy way, any suggestion will be welcome.

and right now when I enter item # its not pulling price in E3, I don't know what else I can do

[Arkadi]

Ok so how are you getting the query to run from excel? How do you tell the query what Part number? will you want the macro to get you the one result? or work through the list? the code below should work for a single item, and can be adapted to work down the whole list of item#'s. I can't test as it would mean having to make a database with relevant fields.... try it and let me know.

Please Login or Register  to view this content.

[Bitto]

thanks for reply, we use the different excel spreadsheet every day with different item # than we have to add macro to each excel file correct ? please confirm , since this spreadsheet other user will be using it, so trying to figure it out to make it easy for them, if they enter item # cell can pull amount from database table cell,

right now I have parameter in MS query and I enter item and tell to return data to Microsoft excel and and which cell, it pull all the fields, I just want to connect to amount field,
right now using ODBC connection, is there any other connection can work to accomplish what I am looking for, any help will be great,

[Arkadi]

I don't know what your ms query looks like? usually in a query in Access you can specify which fields to display (checkbox) do you have that option? If your query only returns ONE parameter then that should be all that comes down? I can't actually see much of your query and am new to this, but I suggest you review what columns are included in your query, sounds like you have the whole table instead of limiting it to what you need.

That being said, yes with my macro you would need it on each excel file. Why do they need a new file each day? I don't know enough about your process to know what solution to suggest.

[Arkadi]

Which connection in your attachment is the one for the price query?

Does the attached file do any better? I modified a small part of Pricedisctable-1

[Bitto]

I am not sure if connection is working properly or not. why I can't just connect single cell data from MS query,
I have searched so much on internet, can you tell may me I am missing some link, am I suppose to enter any info on excel cell E3 that wil connect to MS query table cell amount ,
we create new pricing quote for customer, they are on new excel file that's why macro will
not work for us. if there is any other connection or any other way I can automate the
single cell with MS query pulling amount,

I will be greatful for any idea.

[Arkadi]

Bitto.. you didn't like my macro idea because you have a new excel file each day? But would that not be the same problem if you have to set up a new excel sheet with MSQuery?

[Bitto]

with MS query, we will just have to do connect, since query is already there, don't know if its going to work, I would like to give it a try, macro idea is good, because first I have \
not used macro before, even if I add the macro you send it to me , we have to add in every
excel file,

I really appreciate all your help,

can some one else suggest any idea, if they have done this or how can I achieve this.
thanks again

[Arkadi]

Bitto Im pretty sure when you set up the connection, and you pick a database, then you pick a table correct?
When you choose a table, the next part has all the fields listed in a window.... you need to remove all the extra fields, select field you don't want, and click REMOVE.... only keep on the list what you need. Then you will not get the extra fields that you don't want.

[Bitto]

I am trying to connect to MS query through ODBC connection, some how its not working, can you please help me with this below are
steps I am using Data > From other Sources > From Microsoft Query>
Database Queries Olap Database
1 New Data Source
2 Ax Live
3 dbase Files
4 Excel Files
5 Ms Access Database

which steps I should take it, so its connect to MS query through ODBC connection,

thanks again for your help,