Visio Guy

Visio Discussions => Shapes & Templates => Topic started by: AyEff on July 04, 2016, 04:29:20 PM

Title: Relative links to external data
Post by: AyEff on July 04, 2016, 04:29:20 PM
Hello,

Does anyone know if it is possible to use relative links to an external data source?

I have a VSD file located in a folder with an XLS of metadata in the same folder.
(NB using Visio 2010 btw).

Each time these files are moved, the links are broken because they are absolute.
If you could specify "use relative links" - the problem would be solved.

Many thanks in advance.

Cheers,


Andy

Title: Re: Relative links to external data
Post by: JuneTheSecond on July 05, 2016, 05:27:10 AM
Same kind of a question is not yet answered since 2011.
    http://visguy.com/vgforum/index.php?topic=2814.msg11941#msg11941
If you can get no answer, please google some hints in the web by yourself.
For example.
1. bVisual Updating the file path of data linked Excel tables in Visio
   https://blog.bvisual.net/2015/03/13/updating-the-file-path-of-data-linked-excel-tables-in-visio/
2. Visio Guy Moving Linked Data Sources in Visio 2007
   http://visguy.com/vgforum/index.php?topic=2134.msg9531#msg9531
3. Automate moving data connected Visio diagrams
   https://blogs.msdn.microsoft.com/chhopkin/2013/06/10/automate-moving-data-connected-visio-diagrams/
4. About linking shapes to data
   https://msdn.microsoft.com/en-us/library/office/ff766177.aspx
Title: Re: Relative links to external data
Post by: Croc on July 05, 2016, 07:28:20 AM
You can try to replace absolute links to relative with a macro.
An example below does this for the first DataRecordSet.
Sub ttt()
    s = ActiveDocument.DataRecordsets(1).DataConnection.ConnectionString
    Pos1 = InStr(1, s, "Data Source=") + Len("Data Source=")
    Pos2 = InStr(Pos1, s, ";")
    Pos3 = InStrRev(s, "\") + 1
    If Pos3 > Pos1 Then
        s2 = Mid(s, Pos1, Pos3 - Pos1)
        s3 = Replace(s, s2, "")
        ActiveDocument.DataRecordsets(1).DataConnection.ConnectionString = s3
    End If
End Sub
Title: Re: Relative links to external data
Post by: JuneTheSecond on July 07, 2016, 02:32:40 AM
How would you like manually change the data source after moving?
Press button of change data source in "Configure Refresh" window.
As is written in the document nVisual Updating the file path of data linked Excel tables in Visio.
https://blog.bvisual.net/2015/03/13/updating-the-file-path-of-data-linked-excel-tables-in-visio/
Title: Re: Relative links to external data
Post by: JuneTheSecond on July 09, 2016, 11:23:59 PM
I've studied Croc's macro, and found really cool to change the path to external data to relative from absolute.

I used to store my Visio drawings in the folder, Documents\Visio Drawings, and Excel books in Documents\Excel Books.

So I changed just a line of Croc's macro,
from

s3 = Replace(s, s2, "")

to

s3 = Replace(s, s2, "..\Excel Books\")


After I run the macro and confirmed the relative path is really applied as

Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=..\Excel Books\HLG300x81Weight History.xlsx;Mode=Read;Extended Properties="HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False


Really got "Data Source=..\Excel Books\HLG300x81Weight History.xlsx".
Of course, you can refresh data, and relative path is kept after that.
Very cool!
Title: Re: Relative links to external data
Post by: sychik on January 30, 2020, 10:09:50 AM
Just write .\Datasource.xlsx in Data selector wisard.
Or ..\File in parent folder.xlsx if needed.
Your data is now updated from file, kept with drawing.