Reference a table in a closed workbook… With a twist

[AskMyDog]

Hello everyone,

I need help with a new feature for a Excel based program I'm working on.

I currently have a macro that searches a list object table for words that match a target string and returns information that target string based off of which will matched in the table. The database table is located in a different sheet of the same workbook as where the macro looks for and returns the data. While using the macro, if an error is found, or a new entry in the database is needed, users are asked to update the table.

The problem is that multiple users use the workbook at the same time. The file is located on a shared drive, and every user, except for the first user, has to download a read only version. If they make any updates to the table, it isn't shared with any of the other users, and the new table is saved as a new in the shared drive, instead of being merged.

Is there any way to either move the table to another workbook and be referenced in such a way that workbook doesn't need to be opened (thus requiring a read only copy for additional users), or to move the table to another type of file altogether that allows for this functionality?

I'm open for any ideas, I have no idea how to tackle this problem.

Thanks again!

 

[dmt32]

Is there any way to either move the table to another workbook and be referenced in such a way that workbook doesn't need to be opened (thus requiring a read only copy for additional users), or to move the table to another type of file altogether that allows for this functionality?
I'm open for any ideas, I have no idea how to tackle this problem.

Hi,
Think you have probably more idea than you give yourself credit.
If your dataset / user base not too large you can, as you have already asked, simply place your table in a separate workbook and use it as your database - your user’s workbooks would become templates that read read/write to the database. This approach would require some careful planning & programming. Although will not provide real time updates for users, at a base level, could be a satisfactory solution for your need.

Have a look at Excel Online – never used it myself but may provide an answer.

Another way maybe would to use ADO – you can read more here:https://support.microsoft.com/en-us/kb/278973

And perhaps those here with more up to date skills than myself at connecting Excel to databases like Access for instance, can give you some guidance.

Hope Helpful

Dave