Excel: Sharing a Workbook for a Reference Table 1

[kjv1611]

We have a setup of Excel workbooks that use a centralized XLA file for the majority of their VBA code.

I'd like to use a centralized standard excel workbook (hidden) to store a reference table instead of storing a copy in every related workbook.

Has anyone here seen any real life issues in sharing a reference list with multiple Excel workbooks at the same time?

The updates to the shared list/table would be very rare. It is for a list of usernames and associated information.

Thanks for any thoughts.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

Yes. It can be referenced via MS Query.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Thanks.

Always goes back to MS Query.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[SkipVought]

It's in and out. I've made functions with argument(s) to get/return a specific data element. Use ADO.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[kjv1611]

Thanks for the tip

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Gavona]

Has anyone here seen any real life issues in sharing a reference list with multiple Excel workbooks at the same time?

None at all and we do it a lot. We have some quite large shared lists (downloaded from our ERP) which are regularly updated and stored as named ranges in XLSX workbooks.
Typically the required info is pulled from them using lookup functions. (End users are more comfortable with lookups than with Query). The tips I would make if following this approach are:

If you are just performing a small number of lookups then the reference table can often be in a closed workbook. However for heavier use the workbook is much better open and the performance will be slightly better if you import the reference table into your workbook (which is where Query comes into the picture).

Use a named range so that the person maintaining the reference table can maintain as the data changes shape
Set the reference list workbook to be read only in windows explorer
(Both the above are easily accomplished in VBA code)

If you do want to Do not use Offset to create a range that automatically changes with added/deleted rows and do not use structured tables in place of a named range. Both of these require the workbook to be open when you are doing a lookup.

If appropriate put the date when the reference table was last updated in cell A1 so you can access that info easily.

Your XLA can have code that will open the reference workbook, and create/apply the formulae and convert to values then close the reference book.

That said I keep meaning to make the time to convert some of our procedures to using Skip's recommended approach.




Gavin