Hi,
Itīs my first forum post, so hi to everyone!
I have an access 2007 table and an excel 2007 worksheet. I would like to use the values from one field in the access table to populate the lookup values for a field in excel.
I have a macro which connects to my access accdb database and imports the table into excel but I need to one step further.
I can post the code if necessary, but I would first like to ask if this is possible.
Thanks for any replies,
Rob
when you talk about 'lookup values for a field in excel' do you mean you are using a data validation dropdown, or are you using a control (forms or activex)? if you are using a control, you don't even need the data on a worksheet but for data validation you do.
Josie
if at first you don't succeed try doing it the way your wife told you to
Hi,
I am using a data validation dropdown. I have managed to do this by importing the column into another worksheet and then using "named area" or whatever itīs called in english created the data validation list.
But I thought that I could maybe skip that step and create this with a macro.
Cheers,
Rob
if you setup a query that returns the one column in your access database then you can link to that query in Excel and it will automatically be a named range that you can use in DV. there's no code required at all.
ok...so I have to create a query in Access and then link in excel?
yeah, that's it.
how do I link to the query without actually importing the query results into a worksheet? I can only see how to import the data into a worksheet and then create the named range. I canīt seem to create a named range for data that isnīt present in excel!
you can't - you have to import them into the worksheet but that will create a named range that updates whenever you refresh the query.
I see....so exactly what I did in my 3rd post.
thanks
I guess I misunderstood what you were actually asking then. what exactly do you want assistance with-the code to add data validation to a cell?
I was trying to actually ask if there is a way to do what we have just achieved without actually having to load the data into a separate worksheet....ie load (via a macro) data from an access query into a named range
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks