DatarecordSet with relative path to Excel worksheet

[Thomas Winkel]

Hi,

since years I add DataRecordsets with relative path to an Excel Sheet using the following (simplified) code:

Code Select Expand


sub DataRecordsetDemo()
    Dim strConnection As String
    Dim strCommand As String
    Dim drs As Visio.DataRecordset
    Dim vsoPrimaryKey(1 To 1) As String
   
    vsoPrimaryKey(1) = "ID"

    strConnection = "Provider=Microsoft.ACE.OLEDB.12.0;" _
        & "User ID=Admin;" _
        & "Data Source=data.xlsx;" _
        & "Mode=Read;" _
        & "Extended Properties=""HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;"";" _
        & "Jet OLEDB:Engine Type=34;"

    strCommand = "SELECT * FROM [data$]"

    Set drs = ActiveDocument.dataRecordSets.Add(strConnection, strCommand, 0, "data")
    drs.SetPrimaryKey VisPrimaryKeySettings.visKeySingle, vsoPrimaryKey
end sub

The Excel workbook (data.xlsx) is in the same directory as the visio document.
This always worked without problems with many different versions since Visio 2007.
Now, with Visio 2019 it connects the data, but gives the following error on refresh:

"Error 80004005: The Microsoft Access database engine could not find the object 'C:\Users\thomas\AppData\Local\Temp\VSO9DD3.xlsx'."

No idea, why Visio expects this strange Excel file in my temp folder...
When I connect the data by hand using the wizard it works as expected.
Analyzing the DataRecordset with the following code shows no difference in ConnectionString and CommandString.
But the hand connedted one has "data.xlsx" as FileName, the other one "".

Code Select Expand


Sub DrsInfos()
    Dim drs As DataRecordset
   
    For Each drs In ActiveDocument.DataRecordsets
        Debug.Print "##############"
        Debug.Print drs.Name
        Debug.Print "File: " & drs.DataConnection.FileName
        Debug.Print drs.DataConnection.ConnectionString
        Debug.Print drs.CommandString
    Next drs
End Sub


I tried to connect with absolute path (that works) and afterwards change the connectionString to relative path. But this doesn't work.

Can you reproduce this problem? Has someone an idea how to fix that?

Btw, just typing "data.xlsx" into the wizard does not work. I have to browse to the document and then remove everything but "data.xlsx", that works.

Best regards,
Thomas

[Croc]

I tried changing the absolute path to the file to relative. Previously, I successfully performed such an operation (long ago).
In Visio 2019, the file name is contained in two places in the visio/data/connections.xml file:
DataConnection.FileName
DataConnection.ConnectionString
However, the Replace(drs.DataConnection.ConnectionString... operation succeeds, but Replace(drs.DataConnection.FileName... fails because it is a readonly property.
Thus, the "software repair" of DRS did not work.
Trying to enter a relative file path using the wizard doesn't work either. I was able to write a relative path to DataConnection.ConnectionString but DataConnection.FileName remains absolute.
I assumed that during programmatic work and when using the wizard, Visio performs some kind of checks that prevent us from achieving the desired result.
But recently in the topic http://visguy.com/vgforum/index.php?topic=9945 we discussed the possibility of modifying a Visio drawing in XML. This method worked. That is, manually changing the visio/data/connections.xml file (in two places) allowed us to get a DRS with a relative path to the data file.

[Thomas Winkel]

Thanks, Croc. I confirm, that manipulating connections.xml in the Visio document works.
But now, the chaos is bigger as before... 
After that worked I again tried to add new data connections using the code above and it always worked.
I tried with existing documents, with new documents, same code, same conditions as before, but it always worked.
So, I was not able to reproduce the refresh problem again.
This morning I tried again and sometimes the problem occurs, sometimes not.
When the problem occurs it always helped to save and close the document. After reopen refresh always works, regardless if I add the FileName to connections.xml or not.

So, summarized my current findings are:
* Adding the relative connection by hand, using the wizard works always. (Possible with the proceeding I described above.)
* Adding the same connection by code works sometimes, and sometimes not. No idea why.
* If the refresh fails, save, close, reopen the document always helps.
* Adding the connection by code using an absolute path works always.
* Making the path relative afterwards by code (in DataConnection.ConnectionString) is not a solution because it makes the same problem.
* DataConnection.FileName is only set with the wizard, never with the code.
* Possible to set DataConnection.FileName by hand in the visio document (visio/data/connections.xml), but this has no effect to the refresh problem.
* Adding the connection is not the problem, but a refresh afterwards.
* In error case Visio tries to access the not existing file "C:\Users\User\AppData\Local\Temp\VSOxxxx.xlsx"

I guess this is a bug that should be fixed by MS.

[Yacine]

Hi Thomas,
long time no see. Nice to have you back. 

Would this link help? https://bvisual.net/2015/03/13/updating-the-file-path-of-data-linked-excel-tables-in-visio/

[Thomas Winkel]

Hi Yacine,

thanks, nice to see that you are still active here

The link is interesting, but not an option for me.
Our Excel workbook is very similar to the one in the link: Many sheets, formatted as table, first column is "ID".
We automate the process of adding sheets, filling them with data and connect them to Visio with a .NET Addin.
A project always consist of the Visio document and a "data.xlsx" in the same directory.
This is simple and always worked perfect since more than 10 years, now.

I guess most of our users use older Visio versions than 2019.
At least nobody complained about this problem, so far.
Maybe it is not a Visio problem, but a problem of my system.
Today I tested with two documents, that both worked last week.
And one of them did not work any more.
So, I copied both documents (+ data.xlsx) in a new sub directory for further analysis.
But there, both worked without any change.

Now, I stop investigation and wait what happens

[Croc]

I'm not sure if anyone will be interested, but I'll say it just in case
I made for myself a small program that translates the absolute DRS connection into a relative one.
An example of work is shown in the attached animated gif.
If interested, I can post here DRScorrector.exe or program text (C#).
If not, then I will not litter the forum.

[Yacine]

@Croc,
please litter.

The code (just the core as plain text) would be more useful than the exe, so one can translate it to VBA or other languages.

[Croc]

OK
I remind you that I did this for myself, so I did not achieve a beautiful and correct text. Just to make it work.

[Thomas Winkel]

Hi Croc, Thanks for sharing!

Some notes:
* You replace the complete path to the file, leaving only the filename.
* So, this will only work for files in the same directory as the Visio document.
* Relative paths like './subFolder/data.xlsx' will not work.
* You only replace "FileName", not "Data Source". I guess because you can replace that via VBA in DataRecordset.DataConnection.ConnectionString, right?
* You can achieve the same by going the manual way using the Custom Import wizard.
* When I add DataRecordsets by code FileName is always empty (FileName='').
* But this seems not to be the problem, because the error above appears random.

[Croc]

Hi Thomas!

* You replace the complete path to the file, leaving only the filename.
* So, this will only work for files in the same directory as the Visio document.
* Relative paths like './subFolder/data.xlsx' will not work.

Yes you are right. This was enough for me. If desired, you can complicate the replacement algorithm.

* You only replace "FileName", not "Data Source". I guess because you can replace that via VBA in DataRecordset.DataConnection.ConnectionString, right?

No. I am looking for a path in FileName, but replace it in the entire string, including Data Source.

* When I add DataRecordsets by code FileName is always empty (FileName='').
* But this seems not to be the problem, because the error above appears random.

In this program, I only correct the path. This is not a solution to your problem

[Croc]

No. I am looking for a path in FileName, but replace it in the entire string, including Data Source.

You can do the opposite - look in the Data Source, and substitute a new path in both FileName and Data Source. But that still won't solve the problem. After all, you are probably trying to add a DataRecordset with a macro, that is, from an open Visio file. And my program requires a closed Visio file in order to work with the package

[Thomas Winkel]

I am looking for a path in FileName, but replace it in the entire string, including Data Source.

Ah, yes I see

This is not a solution to your problem

By now I guess that this is only a local problem of my installation.
First I was scared that this a general problem because big parts of our concept rely on that feature