2 Replies Latest reply on Oct 22, 2014 3:33 AM by Arto Kvick

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

    Jack Berryman



      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