Template References

[eveCalypso]

Hello All,

My template documents references a central template which holds the majority of re-usable code.
I create a reference to this document in each template I create.
However...
Microsoft (rightly) uses a UNC reference to reference the central template.
We have two separate physical locations.
Site A has exactly the same file structures as site B, but under a different drive letter!

So, I can easily get my templates to save in two locations everytime I change and save them, but how can I get the reference to change?
I either need to be able to set a relative or absolute (non UNC) path, or...?
It will still work if I keep it as it is, but running the templates on site B will be extremely slow if it does not access a local copy of the central template.

Any advice or ideas would be welcome!!

Regards,
EvE

 

[VBAjedi]

Not entirely sure I understand your situation, but why can't you just embed the code you want to use into each template?

Relying on linking can be very problematic, especially when networks and/or multiple users are involved.

Just a thought. . .

VBAjedi

 

[eveCalypso]

Good Morning ;-)

No, that would be a bad idea. The code pertains to document automation - and I can not include it into each (of what might turn out to be 300 document) templates.
The idea is that if anything fundamental changes, it is in a central location/template.

It would work perfectly, if it wasn't for the fact that the two sites work off two seperate drives.

Any other suggestions?
Regards,
EvE

 

[VBAjedi]

Still looking for a simple solution here - have you considered hiding a copy of your master template on the drive that holds all the other templates?

If that isn't an option for some reason, we can play around with the VBA commands for returning the filepath/name of an open Excel file, but I'm not quite sure how you could use those to get what you're after.

Anyone else have ideas here?

VBAjedi

 

[eveCalypso]

Hello Again,

Well, here is how I see it.

a) I must either be able to set a reference to the master
template at runtime (which I think is not possible) OR
b) I must find a way to change the path of the referenced
file drive specific (i.e. not UNC)

The alternatives are to load the master template on startup as you would Normal, or change each and every document copy's reference to the master file it should point to (i.e. the one on the local shared drive....)

Not very nice, so if anyone else have some ideas....

Regards,
EvE

 

[VBAjedi]

Ok, this would be an odd workaround, but. . .

It seems to me that essentially what you need is a constant location to store the reference to the master template (which may not stay constant). So, if storing this reference in a file on "Drive B" is not an option, how about writing the reference to a hidden portion of each template (either a hidden sheet or an out-of-sight cell)?

If you put it in the same place on each template, you could write a simple macro to scan the drive for templates and update that link in each one anytime it changes.

VBAjedi