Data Validation From Other Workbook Problem

I have an Excel workbook that references another Excel workbook for data validation lists. Instead of having both workbooks open, I use Microsoft Query to pull the external Excel table into the current workbook using data refresh. The problem is that the folder that holds the Master Data is pretty sensitive and we do not want people having more than Read only privilages. Only people with Read/Write access are able to refresh properly while those with read only access get an error saying "Unexpected External Database Driver Error ()". How can I make the refresh work for those with Read Only access work properly?
nirajkrishnaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
I don't think that it can be done. Therefore you would need a workaround. How about placing your lists in a dependent workbook with access by everyone which gets updated by action from the inaccessible master?
0
nirajkrishnaAuthor Commented:
Thanks for your answer. I think I understand the concept of what you are saying. Let me look into it and see if it works.
0
aikimarkCommented:
You can write an query and populate an ADODB recordset, pointing to a different workbook.  Then use the .CopyFromRecordset method to transfer the data to your local workbook.

If you need to join data from multiple workbooks, you will probably need to attach the workbooks to an Access database.

Alternatively, you can use the data in a recordset to do your data validation without transferring all the data into the local workbook.  For a similar similar client problem, I created some Excel functions, packaged into an Add-in, that did data validation relative to the database table(s).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
nirajkrishnaAuthor Commented:
We ended up giving write access to the users with a warning to not modify anything. Not as elegant as an add-in, but workable for this request. Now there is the issue of Excel for the Mac not having any ODBC drivers! Half the users are good and Mac users cannot do anything. Would have been nice to know the Mac requirement right off, so I cold just turn down the job:) No clue how to do anything for that group..........
0
aikimarkCommented:
You might want to check out the Mono project and a .Net implementation for the MAC side of the house.  It would be worth opening a new question in the .Net  and VB.Net zones, asking about VSTO in a MAC/Mono environment.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.