Link to Access & Named Range

[Jessica]

Hi, I posted this over in Excel Links but I don't think it gets much traffic.

I have book1 that has a query that brings down a list from access (name=
Project). I need to use this list in a drop down box (validation). I also
need to update the list periodically.

The problem is that my users are opening the spreadsheet as a template and
may need updates while using it. They do not have Access so they can't do
the update.

So, I wanted to create book1 with the list & query. Then have the template
(book2) reference the list in book1.

I tried:
1) doing a copy/paste/named range macro but that just seems like a workaround
2) creating a named range in book2 that references book1 but it only works
when book1 is open.

Any suggestions?

 

[JCS]

Hi Jessica,

Not sure if I understand your problem completely. But if Book 1 is a result
of a query from an Access database and your user community doesn't have
Access why not create another query in Book 2 which references Bool 1 and
then your users can get the updates. Again, I'm not sure if I've captured
your requirements.

In XL 20003
Select Data
Select Import Data
Select New database query
Select Excel files
proceed with your query.

HTH,
John

 

[Shane Devenshire]

Hi,

I'm not clear on what exactly your problem is. If you are trying to use a
range from another workbook in a data validation list, try this:


Here is an example of applying data validation between workbooks:

1. In the one workbook name the range containing the items you want listed,
here I named it Months. (highlight the range and choose Insert, Name,
Define, type Months into the Names in workbook box and click OK).
2. Move to the workbook where you want to display the list and choose
Insert, Name, Define and in the Names in workbook box enter List. In the
Refers to enter something of the form
='10-21-2008.xls'!Months
3. Select the range where you want the list and choose Data, Validation, List
4. In the Source box enter =List

 

[Jessica]

Yeah, silly me didn't realize I could query excel!

But...

So I have LISTS.xls with my query to Access that brings down data and has a
named range of Projects.

When I try to query LISTS.xls I get this message...
This data source contains no visible tables.

 

[Jessica]

Hi Shane,

Yes, I tried this and it only works if the LISTS.xls is open.

LISTS.xls has a query to access with named range = Project

Log.xlt has named range ProjectList =
='I:\ADMIN\GRPADMIN\PROJREQ\Attendance\TimeLog\Phase2\[LISTS.xls]Sheet1'!Project

Data validation =ProjectList

 

[JCS]

Hi Jessica,

Not knowing what your data looks like, I tried a proto-type and named the
range in Book 1 Projects. When I created my new query, the Porkects range
name displayed and i was able to bring in my data or a subset thereof.

John

 

[JCS]

Hi Jessica,

Another thought that occured to me, was to in the Access database, create a
macro (very easy to do) and use the TransferSpreadsheet function. This will
transfer a table or the results of a query to an Excel spreadsheet.

John

 

[Jessica]

Yes, but my users are opening a xlt spreadsheet once per week and don't have
microsoft access.

 

[JCS]

Hi Jessica

Yes I understand that your users do not have Access, however, the
TransferSpreadsheet function in a macro can be run by you or whoever has
access to the db to produce the query results in a spreadsheet that can then
be used by your users vis-a-vis a query in that spreadsheet. Confusing isn't
it? This is the way I would do it.

John

 

[Jessica]

Ok, I'll try my best to explain here.

The user opens the .xlt file on Monday. a new project is added to the list
on wed. The user has already saved their weekly file as an xls on their hard
drive. I can't do a transfer spreadsheet to their individually saved files.
They only open a new template on Mondays.

I wanted them to have an update button that would allow a refresh sourced
from a central excel file that I can update mid-week.

 

[Jessica]

Ok found the solution!!!

Instead of using: Data | Import External Data | New Database Query

I used:
Data | Import Data... then chose my excel spreadsheet Links.xls and I am
able to choose "Lists$Project" from the list. Then when it's imported it's
acutally a query to the other excel spreadsheet. Now the users can update it
themselves.

Thanks for you help...pushed me in the right direction!

 

[JCS]

Hi Jessica,

This is the method I tried to articulate to you. I guess I did a lousy job.
Sorry for the mix-up. At any rate, I'm glad you found the solution

John