Updating OLE Links in a Visio Worksheet

[carpboy]

I have a Visio doc that has ~200 (OLE - I think) links to a collection of Excel spreadsheets. I have the spreadsheets but the Visio links point to a path that is inaccessible. I would like to change these. Using Visio Plan 2.

By a lot of experimenting the only way I could find was to open the doc, tell Visio to update the links, then when it failed go through the links in the convert dialog that appeared.

This dialog isn't very helpful as the file path is all jammed into a small text box with no resizing controls. Fortunately, I could see the end of the filename and that was sufficient for me to determine which file was for each link.

I had to edit each one of these entries individually as one can't group select and edit in this dialog.

Pain that it was, I did get the links hardcoded to another location, so at least that aspect works.

However, I need to have these as a relative location so that the vsd and xls files can travel together and any other user can open the vsd no matter the location, just so the vsd and xls are in the same folder.

I can't find a way to do this. I tried prepending the file name with '..\filename', Visio complained but I went through and edited all of them, saved, but it failed to find them when I re-opened the document.

How can I make a relative path in these? Is there an easier way to edit the links?

Thanks.

[Surrogate]

Cross-post

[Paul Herber]

Well, Nikolay did suggest posting here so I don't think that's a problem.

[Surrogate]

Well, Nikolay did suggest posting here so I don't think that's a problem.

I don't see a problem with users asking questions in third-party forums either. But when our users not knowing about the answers received on third-party sites, may once again give the same advice or not the best given the previous discussion in the cross-post

[Nikolay]

I was not sure what are these links and if they can be somehow processed, therefore suggested posting here... in case somebody here can understand that better.
It could be much easier though if we had a sample visio file that shows the problem.

@Surrogate, btw, I don't think he's talking about the links you showed in your video really? Or maybe you are right
As far as I understand, the video shows "data recordset" links (or not?), could it be that topicstarter meant something different?

[Surrogate]

As far as I understand, the video shows "data recordset" links (or not?), could it be that topicstarter meant something different?

It is not DataRecordset, it is linked embedded Excel workbook.

[Surrogate]

It is linked embedded Excel workbook.

Step zero: select some range into Excel's workbook and press keys Ctrl+C.
After this make these actions

[carpboy]

[...]
@Surrogate, btw, I don't think he's talking about the links you showed in your video really? Or maybe you are right
As far as I understand, the video shows "data recordset" links (or not?), could it be that topicstarter meant something different?

It looks sort of like @Surrogate video. Here are some snaps.

When I open the doc I get this, not surprising as the source location doesn't exist anymore:
https://imgur.com/EpIF1Ru

I click update links, and of course it fails:

https://imgur.com/AA5NGpK

Clicking on links yields the gibberish dialog. Fortunately, I can read just enough of it to see what the end of the filename is:

https://imgur.com/TcmYuTC

To repair, I have to fix each and every link singly, you can't group select and edit (in this example there are only 2 external files, one link on one file and all the other links on the other).

This works, but only by hardcoding the location of the Excel file.

Here is a small snippet of one page of the doc, the shape has been double clicked. All OK!

https://imgur.com/i40lYvj

Edit: The above does not demonstrate the connection to Excel. That object was a 'worksheet object', not a 'linked worksheet object'. The worksheet objects of course are always editable, the linked ones though do open in excel when context edit is chosen, after the links were manually corrected.

Finally, here is the shape selected and ctrl-k applied. The dialog does not list any existing link:

https://imgur.com/wvwSROd

My goal is twofold: Mainly, have the OLE connections relative so that the xls and vsd can travel together and work no matter the folder they are (both) in. Secondly, and less important, find an easier way to edit the links. I have ~40 docs and the number of links in each range from maybe 100 to as many as 400 in each doc.

In a worst case scenario, I can just recreate the pages with the links. I can do the painstaking editing of the links in the update links dialog shown above, and then for every single shape with a link, delete it and then reinsert the proper datarange from excel using the hyperlink object with the relative property selected.

Thanks for reading all this and assisting. Sorry for the url's in the post, the preview wasn't showing the img's I inserted.