AnsweredAssumed Answered

Using VBA to Perform a Search in EPDM

Question asked by Shawn Pantzke on Jul 8, 2014
Latest reply on Jul 10, 2014 by Matt Shedlov

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

Outcomes