Reference a table in a closed workbook… With a twist

AskMyDog

New Member
Joined
Nov 13, 2015
Messages
16
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!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,658
Office Version
  1. 2019
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,260
Messages
5,674,682
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top