Creating Shared Workbooks destroys hyperlinks?

[malik641]

Hey guys,

Does anybody have any experience with shared workbooks? I'm developing an application that everyone at my job can view, but only 3 people can edit the data inside. There is a lot of protection and I do have a bunch of VBA code inside. FYI, the code works fine with Worksheet protection and workbook protection. The workbook contains hyperlinks to other files and a lot of my code works with these. Once I share and protect the workbook all the hyperlinks get destroyed. Now, I have a button to repair / create hyperlinks, but they cannot be changed once a workbook has been "shared" (per the help docs...plus, I've tried).

I can't just programmatically unshare and re-share the workbook because when you unshare (and unprotect) the workbook it then saves itself, and that would (probably) interrupt the code. Not to mention it wouldn't be a good idea to do that in the first place because what happens if 2 people are using the workbook when I programmatically unshare and re-share it? So that idea's out.

Another idea is that I could modify all the code to function as if it's reading hyperlinks on the worksheet when there really are none (because I would just have them removed because what's the point of having the hyperlinks if they're destroyed?). But I was just curious to know if anyone has a reason the hyperlinks are destroyed. And by destroyed, I mean:

Good hyperlink:
\\Ntserver2\controlled doc's (released)\Drawings\Pending Drawings\7-Basic\7-8F00L0LZN-xx.cdr

Bad Hyperlink:
../../../controlled%20doc's%20(released)/Drawings/Pending%20Drawings/7-Basic/7-8F00L0LZN-xx.cdr

TIA

[Bob Phillips]

Lookup 'Limitations of shared workbooks' in help.

ANd if that doesn't stop you using those nasty shared workbooks, I will send Zack round to sort you out.

[Aussiebear]

Readng between the lines here... I'd say, that its one of your favourite objectives in Excel Bob.... but then I could be wrong.

[malik641]

Lookup 'Limitations of shared workbooks' in help.

Ok. Checked it out. It states:

Unavailable Feature -------- Alternatives
Insert or Change Hyperlink ------ Existing hyperlinks continue to work

Which is not true they don't work at all. Maybe because the path name is so long and it's on a server? I don't know.

That was a pretty big list, though. Although I could've done without almost all of that stuff in there, except this (obviously). So what other alternatives should I go with if I want to keep this as an Excel project? I'm not versed well enough with Access to create something decent (that's protected in the same way) without taking too much time.

ANd if that doesn't stop you using those nasty shared workbooks, I will send Zack round to sort you out.

Send him. You've made me curious

[malik641]

Bump. Any other ideas on why this happens?


Anyway, I've decided to develop this in MS Access. That ought to be a fun little project

[Jan Karel Pieterse]

Wise decision,shared workbooks are unreliable at best.

I've seen so many problems with them.

[malik641]

Thanks Jan.

I have wondered why I haven't seen much use of Shared Workbooks. Guess they're just more of a problem than I'm willing to tackle.

[Ken Puls]

Send him. You've made me curious