6 Replies Latest reply on Sep 5, 2018 6:07 PM by Chris Manger

    Search vault for filename from cell value in Excel

    Chris Manger

      I have an Excel sheet that has a list of PDF names.  I would like to:

      1. Search for these PDFs in PDM
      2. Get the PDF(local cache)
      3. Open the PDF
      4. Print the PDF

       

      I have the code working to do Steps 2-4 but only if I know the exact location of the PDF in PDM.   The problem is, I do not always know the exact location, so I would like to search and get the location.

       

      Does anyone happen to have VBA code on how to search for a filename in PDM?

        • Re: Search vault for filename from cell value in Excel
          Tim Webb

          Chris,

          Since your post refers to the API, I make the assumption you are looking for API code to help make the search so you can then get, open, and print the PDF files.

           

                      Dim Items As List(Of EdmSelItem2) = New List(Of EdmSelItem2)
                      
                      Dim Search As IEdmSearch6 = poCmd.mpoVault.CreateSearch()
                      If Search Is Nothing Then Return

                      Search.SetToken(EdmSearchToken.Edmstok_FindFiles, True)
                      Search.SetToken(EdmSearchToken.Edmstok_WorkflowName, "Default Workflow")
                      Search.SetToken(EdmSearchToken.Edmstok_StateName, "Approved")

                      'Uncomment and modify the following three lines to look for files
                      'with a specified card variable

                      'Dim varName As Object = "Card_Variable_Name"
                      'Dim varValue As Object = "Card_Variable_Value"
                      'Search.AddVariable(varName, varValue)


                      Dim SearchResult As IEdmSearchResult5 = Search.GetFirstResult()
                      While Not SearchResult Is Nothing
                          Dim SelItem As EdmSelItem2 = New EdmSelItem2()
                          SelItem.mlID = SearchResult.ID 'This is the FileID of the file that was returned
                          SelItem.mlParentID = SearchResult.ParentFolderID 'FolderID of the Parent Folder the file is in
                          SelItem.meType = SearchResult.ObjectType
                          SelItem.mlVersion = SearchResult.Version
                          Items.Add(SelItem)

                          'Add code here to get the PDF

                          'Add code here to open the PDF

                          'Add code here to print the PDF

                          SearchResult = Search.GetNextResult()
                      End While

           

           

          • Re: Search vault for filename from cell value in Excel
            Ulf Stockburger

            Hi Chris

             

            This should point you in the right direction.

            Dim vault As New EdmVault5
            Dim vSearch As IEdmSearch6
            Dim vSearchResult As IEdmSearchResult5
            vault.LoginAuto "<your vault>", 0
            Set vSearch = vault.CreateSearch
            vSearch.SetToken EdmSearchToken.Edmstok_WorkflowName, "<your workflow>"
            vSearch.SetToken EdmSearchToken.Edmstok_StateName, "<your state>"
            Set vSearchResult = vSearch.GetFirstResult
            While Not vSearchResult Is Nothing
                '<your code here>
                Set vSearchResult = vSearch.GetNextResult()
            Wend
            

             

            BiI SAP-PDM

              • Re: Search vault for filename from cell value in Excel
                Chris Manger

                Thanks Ulf Stockburger !

                 

                I modified the code, see below, to return the path to the file being searched for.  Hope this helps others who would like to do something similar.  I still need to do some tweaking, add my code to open and print the PDF.  Thanks again!

                 

                Sub SearchForPDF()
                
                Dim X As Integer
                Dim Y As Integer
                Dim vault As New EdmVault5
                Dim vSearch As IEdmSearch6
                Dim vSearchResult As IEdmSearchResult5
                Dim FileCount As Integer
                Dim NumRows As Integer
                Dim FilePath As String
                Dim Filename As String
                
                vault.LoginAuto "VAULTNAME", 0
                
                Y = 1
                X = 3
                
                NumRows = Range("A3", Range("A3").End(xlDown)).Rows.Count
                
                For Y = 1 To NumRows
                    FileCount = 1
                    Set vSearch = vault.CreateSearch
                    'Get filename from cells in Excel
                    Filename = Trim(Cells(X, 9).Value) & "-" & Trim(Cells(X, 10).Value) + ".pdf"
                    'Set folder that you want to search inside of
                    vSearch.SetToken EdmSearchToken.Edmstok_FolderID, 16861
                    'Set filename for which you want to search
                    vSearch.SetToken EdmSearchToken.Edmstok_Name, Filename
                    Set vSearchResult = vSearch.GetFirstResult
                    While Not vSearchResult Is Nothing
                        'MsgBox vSearchResult.Path
                        FilePath = vSearchResult.Path
                        FileCount = FileCount + 1
                        Set vSearchResult = vSearch.GetNextResult()
                    Wend
                    'Insert path of file in Excel sheet
                    Cells(X, 11).Value = FilePath
                    Set vSearchResult = Nothing
                    Set vSearch = Nothing
                    FilePath = ""
                    X = X + 1
                Next Y
                
                
                End Sub