AnsweredAssumed Answered

List Referenced Files Macro - How do I get the description variable to be listed too?

Question asked by Jack Berryman on Oct 15, 2014
Latest reply on Oct 22, 2014 by Arto Kvick



I have the following macro that I have taken from Wayne Matus' "Automating ECN's process using XML" presentation.


Sub UpdateAffectedList()
    Dim xlWS As Worksheet
    Dim rAffects As Range
    Dim i As Integer
    Dim strDocumentPath As String
    Dim oVault As New EdmVault5
    Dim oFile As IEdmFile6
    Dim oFolder As IEdmFolder6
    Dim projName As String
    Dim oRef As IEdmReference5
    Dim msg As String
    Dim pos As IEdmPos5
    Dim ref As IEdmReference5
    Dim xx As String
    Set xlWS = ThisWorkbook.Worksheets("Sheet1")
    Set rAffects = xlWS.Range("affects")
    strDocumentPath = ThisWorkbook.FullName
    oVault.LoginAuto oVault.GetVaultNameFromPath(strDocumentPath), 0
    Set oFile = oVault.GetFileFromPath(strDocumentPath, oFolder)
    If Not oFile Is Nothing Then
        rAffects.Value = ""
        i = 1
        Set oRef = oFile.GetReferenceTree(oFolder.ID, 0)
        Set pos = oRef.GetFirstChildPosition(projName, True, True, 0)
        While Not pos.IsNull
            Set ref = oRef.GetNextChild(pos)
            xx = "conisio://" & oVault.Name & "/open?projectid=" & ref.FolderID & "&documentid=" & ref.FileID & "&objecttype=1"
            xlWS.Hyperlinks.Add Anchor:=rAffects.Cells(i, 1), Address:=xx, TextToDisplay:=ref.File.Name
            i = i + 1
    End If
End Sub


It's works brilliantly and lists the referenced FILE NAMES within a named range in excel (called affects)


Is it possible for it to list the 'DESCRIPTION' variable for each file in the cell to the left of each file name?


So instead of this:


It would paste the description of each file next to it like this:


Thanks in advance for any help, this could be a real time saver for our ECN process.


Cheers, Jack