3 Replies Latest reply on Jul 10, 2014 2:10 PM by Matt Shedlov

    Using VBA to Perform a Search in EPDM

    Shawn Pantzke

      Good morning all,

       

      I have a need to perform a search using vba within excel. I have the code working, kind of, but I search on the variable "ECN" and only get 12 of 34 results to populate.

       

      I am trying to populate a list of drawings affected by an ECN by searching the database and then returning various variables into a excel spreadsheet that is used for distribution of the ECN.

       

      Here is my code as of thus far. Hopefully someone can spot a silly misttake.

       

       

       

      Sub FindFiles()

      Dim eVault          As IEdmVault5
      Dim eSearch         As IEdmSearch5
      Dim eResult         As IEdmSearchResult5
      Dim eFile           As IEdmFile5
      Dim eFolder         As IEdmFolder5
      Dim EnumVar         As IEdmEnumeratorVariable5
      Dim Var             As Variant
      Dim strVar          As String
      Dim ConfigList      As EdmStrLst5
      Dim Configuration   As String
      Dim pos             As IEdmPos5
      Dim RowIndex        As Long
      Dim test            As Long
      Dim FileCount       As Long
      Dim vaultName       As String
      Dim adminPassword   As String


      RowIndex = 7
      FileCount = 0
      vaultName = "Insert name here"
      adminPassword = "Insert Admin Password Here"

       

          Set eVault = New EdmVault5
          eVault.Login "admin", adminPassword, vaultName
          Set eSearch = eVault.CreateSearch
         
          Var = Range(Cells(3, 23), Cells(3, 23))
          eSearch.AddVariable "ECN", "3842"
          Set eResult = eSearch.GetFirstResult
          While Not eResult Is Nothing
              FileCount = FileCount + 1
              'Debug.Print eResult.Name
              If LCase(Right(eResult.Name, Len(eResult.Name) - InStr(1, eResult.Name, "."))) = "slddrw" _
                  Or LCase(Right(eResult.Name, Len(eResult.Name) - InStr(1, eResult.Name, "."))) = "dwg" _
                  Or LCase(Right(eResult.Name, Len(eResult.Name) - InStr(1, eResult.Name, "."))) = "xls" _
                  Or LCase(Right(eResult.Name, Len(eResult.Name) - InStr(1, eResult.Name, "."))) = "xlsx" _
                  Or LCase(Right(eResult.Name, Len(eResult.Name) - InStr(1, eResult.Name, "."))) = "doc" _
                  Or LCase(Right(eResult.Name, Len(eResult.Name) - InStr(1, eResult.Name, "."))) = "docx" _
                  Or LCase(Right(eResult.Name, Len(eResult.Name) - InStr(1, eResult.Name, "."))) = "idw" _
                  Then
                 
                  'Debug.Print eResult.Name
                  Set eFile = eVault.GetFileFromPath(eResult.Path, eFolder)
                 
                  Set ConfigList = eFile.GetConfigurations
                  Set pos = ConfigList.GetHeadPosition
                  Configuration = ConfigList.GetNext(pos)
                 
                  Set EnumVar = eFile.GetEnumeratorVariable
                 
                  EnumVar.GetVar "ECN_Purchasing To Review", Configuration, Var
                  strVar = CStr(Var)
                  'Debug.Print "     PTR: " + strVar
                  Range(Cells(RowIndex, 2), Cells(RowIndex, 2)) = strVar
                 
                 
                  EnumVar.GetVar "ECN_Document Status", Configuration, Var
                  strVar = CStr(Var)
                  'Debug.Print "     Status: " + strVar
                  Range(Cells(RowIndex, 3), Cells(RowIndex, 3)) = strVar
                 
                  EnumVar.GetVar "Book", Configuration, Var
                  strVar = CStr(Var)
                  'Debug.Print "     Book: " + strVar
                  Range(Cells(RowIndex, 4), Cells(RowIndex, 4)) = strVar
                 
                  EnumVar.GetVar "Book Section", Configuration, Var
                  strVar = CStr(Var)
                  'Debug.Print "     Section: " + strVar
                  Range(Cells(RowIndex, 5), Cells(RowIndex, 5)) = strVar
                 
                  strVar = Left(eResult.Name, InStr(1, eResult.Name, ".") - 1)
                  'Debug.Print "     DWG/DOC: " + strVar
                  Range(Cells(RowIndex, 6), Cells(RowIndex, 6)) = strVar
                 
                  EnumVar.GetVar "Revision", Configuration, Var
                  strVar = CStr(Var)
                  'Debug.Print "     Rev: " + strVar
                  Range(Cells(RowIndex, 9), Cells(RowIndex, 9)) = strVar
                 
                  EnumVar.GetVar "REV Description", Configuration, Var
                  strVar = CStr(Var)
                  'Debug.Print "     Desc. of Change: " + strVar
                  Range(Cells(RowIndex, 10), Cells(RowIndex, 10)) = strVar
                 
                  RowIndex = RowIndex + 1
              End If
              Set eResult = eSearch.GetNextResult
          Wend
          Debug.Print FileCount

      End Sub