how to export visio external data to excel

Question

I have an old VISIO file with external data on it. The source file of the data does not exist anymore. I have tried to copy and paste the data from visio to excel with no success.

Then I tried to access the data through VBA and I don't understand the results I get: (The amount of rows is currect, yet the data is sometimes good and sometimes not)

Sub test()
    Dim i, j As Integer
    Dim r As Variant
    Dim a() As Variant
    Dim rowSTR As String
    Dim vsoDataRecordset As Visio.DataRecordset
    For i = 1 To Visio.ActiveDocument.DataRecordsets.Count
        rowSTR = ""
        a = Visio.ActiveDocument.DataRecordsets(i).GetRowData(1)
        For j = 0 To UBound(a) - 1
            rowSTR = rowSTR & vbTab & a(j)
        Next j
        Debug.Print i & ")" & rowSTR
    Next i
End Sub

Do you know how to get this data from visio to excel?

Thanks Asaf

Update: Added the source and code output: I rechecked and have no clue where the data comes from... enter image description here

enter image description here

What do you mean with the data is correct and sometimes not? What is incorrect about the data sometimes? — K_B
Added screenshots - I believe my code is referencing the wrong place - only even rows have data and it reminds the original but nothing more. — Asaf Gilad

jarmst jarmst · Accepted Answer · 2015-08-11T13:04:32

This post is old but I ran into the same problem and have a solution. In your posted code, you're referencing each DataRecordSet and grabbing the first row, instead of finding the correct one and grabbing all rows.

We also have to avoid using i to count from 0 to ExternalData.Count; the Row IDs can skip numbers, so you have to use the real IDs from the correct DataRecordset.

The following code is not exactly pretty but it works. Note that the linked boolean is not truly part of the data set; it is, however, equivalent to the "chain" icon in the External Data window.

This was written for Visio 2013, but I believe it will work for other versions as well. After running this, you can import the file into Excel using % as the delimiter.

    Sub WriteDataSourceToFile()

    ' REQUIRES: Microsoft Scripting Runtime (C:\Windows\SysWOW64\scrrun.dll)

    ' Below we'll intentionally cause array length errors to test each Row
    On Error Resume Next

    ' Use this to put the drawing name in the first column of each row
    Dim DrawingLabel As String
    DrawingLabel = "DRAWING_NAME_HERE"

    ' Used for getting the External Data from a specific window
    Dim PagObj As Visio.Page
    Dim vsoDataRecordset As Visio.DataRecordset

    ' Used for grabbing all shapes with a link to the current Row
    Dim shapeIDs() As Long
    Dim testLong As Long

    ' Currently only using the above as a test (linked or not linked)
    Dim linked As Boolean

    ' Stores all Row IDs from the DataRecordset and loops through each
    Dim dataRowIDs() As Long
    Dim dataRowID As Variant

    ' Stores the actual Row information and appends to rowSTR for the delimited line
    Dim rowData() As Variant
    Dim rowDataInt As Integer
    Dim rowSTR As String

    ' Used for text file output
    Dim fso As FileSystemObject
    Set fso = New FileSystemObject

    ' Create a TextStream and point it at a unique filename (based on the active document)
    Dim stream As TextStream
    Set stream = fso.CreateTextFile("C:\Users\Public\Documents\GEN_" & ActiveDocument.Name & ".txt", True)

    ' Look through each window and find External Data (matches 2044)
    For Each win In Visio.ActiveWindow.Windows
        If win.ID = 2044 Then
            Set vsoDataRecordset = win.SelectedDataRecordset
            Exit For
        End If
    Next win

    ' Get each Row ID from the DataRecordSet
    dataRowIDs = vsoDataRecordset.GetDataRowIDs("")

    ' Use each Row ID as a reference
    For Each dataRowID In dataRowIDs
        linked = False

        ' Look through all pages and attempt to get Shape IDs linked to the active Row
        For Each PagObj In ActiveDocument.Pages
            PagObj.GetShapesLinkedToDataRow vsoDataRecordset.ID, dataRowID, shapeIDs

            ' Attempting to reference a 0-length array will throw an error here
            testLong = UBound(shapeIDs)
            If Err.Number Then
                Err.Clear
            Else
                ' If it didn't throw an error referencing the array, there's at least one linked shape
                linked = True
                Exit For
            End If
        Next PagObj

        ' Build the output
        rowSTR = linked

        ' Get the array of Row Data
        rowData = vsoDataRecordset.GetRowData(dataRowID)

        ' Go through each column and append the value to the output string
        For rowDataInt = 0 To UBound(rowData)
            ' Using % as a delimeter to prevent text with commas causing a separated column
            rowSTR = rowSTR & "%" & rowData(rowDataInt)
        Next rowDataInt

        'Output the string to the file, putting the label at the beggining of the row
        stream.WriteLine DrawingLabel & "%" & rowSTR
    Next dataRowID

    stream.Close
End Sub

how to export visio external data to excel

1 Answers