14 Replies Latest reply on Nov 25, 2014 1:17 PM by Matt Finley

    VBA Excel to EPDM Search

    Matt Finley

      I'm trying to connect Excel to the EPDM vault so I can click on a cell with a part number and open the .sldprt in Solidworks. I've had limited success but am seeing a very weird thing happen. My script doesn't find the files until I manually find them in the vault AND CLICK ON IT TO START THE PREVIEW. Once I've previewed the file, my code can see it. I'm not creating a vault object, just using typical search code.


      Is someone else doing something like this and if so, is there a way to code VBA to give me the results without manually touching the file?

        • Re: VBA Excel to EPDM Search
          Matt Finley

          Here is the code I've got...

           

          Sub FileSearchEPDM()
          CellPointer = ActiveCell.Value
          If CellPointer = "" Then Exit Sub

          Dim FileNameWithPath As Variant
          Dim ListOfFilenamesWithParh As New Collection ' create a collection of filenames
          ' Filling a collection of filenames (search Excel files including subdirectories)
          Call FileSearcher(ListOfFilenamesWithParh, "C:\My Path to Vault\", CellPointer & "*.sldprt", True)

          For Each FileNameWithPath In ListOfFilenamesWithParh ' cycle for list(collection) processing
          'Debug.Print FileNameWithPath & Chr(13)

          Shell "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & (FileNameWithPath), vbNormalFocus

          Next FileNameWithPath
          If ListOfFilenamesWithParh.Count = 0 Then
          MsgBox "No file was found !"
          End If
          End Sub

          Private Sub FileSearcher(pFoundFiles As Collection, pPath As String, pMask As String, pIncludeSubdirectories As Boolean)

          Dim DirFile As String
          Dim CollectionItem As Variant
          Dim SubDirCollection As New Collection
          ' Add backslash at the end of path if not present
          pPath = Trim(pPath)
          If Right(pPath, 1) <> "\" Then pPath = pPath & "\"
          ' Searching files accordant with mask
          DirFile = dir(pPath & pMask)
          DoEvents
          Do While DirFile <> ""
          DoEvents
          pFoundFiles.Add pPath & DirFile 'add file name to list(collection)
          DoEvents
          DirFile = dir ' next file
          Loop
          ' Procedure exiting if searching in subdirectories isn't enabled
          If Not pIncludeSubdirectories Then Exit Sub
          ' Searching for subdirectories in path
          DirFile = dir(pPath & "*", vbDirectory)
          Do While DirFile <> ""
          ' Add subdirectory to local list(collection) of subdirectories in path
          If DirFile <> "." And DirFile <> ".." Then If ((GetAttr(pPath & DirFile) And vbDirectory) = 16) Then SubDirCollection.Add pPath & DirFile
          DirFile = dir 'next file
          Loop
          ' Subdirectories list(collection) processing
          For Each CollectionItem In SubDirCollection
          Call FileSearcher(pFoundFiles, CStr(CollectionItem), pMask, pIncludeSubdirectories) ' Recursive procedure call
          Next
          End Sub

           

          Sorry I couldn't find an insert option for VBA.

            • Re: VBA Excel to EPDM Search
              Lee CS Young

              If the file isn't in the local cache, it doesn't exist in the file system. You'll need to add code to get the latest version.

                • Re: VBA Excel to EPDM Search
                  Matt Finley

                  Thanks for the feedback. I'm not sure how I would code that. The macro I'm making opens the file in Solidworks I figured that would pull the latest version. Appreciate your help.

                    • Re: VBA Excel to EPDM Search
                      Jeremiah Davis

                      Hello Matt,

                       

                      Instead of searching Windows for this file, in your Excel macro code, add a reference to the EPDM API. Now you can use all of the functions available in the EPDM API including searching for the file, getting the latest version of the file and opening the file or file location.

                       

                      The help information for the EPDM API can be found in the installation directory of EPDM, typically C:\Program Files\SOLIDWORKS Enterprise PDM\API_GB.chm.

                       

                      I have actually written many examples to show how to add Excel into the EPDM API, but this is backwards, adding EPDM into an Excel Macro.

                       

                      Hopefully this help?

                        • Re: VBA Excel to EPDM Search
                          Matt Finley

                          Hi Jeremiah,

                           

                          I added a reference to PDMWorks Enterprise 2013 Type Library, is that the correct library?

                           

                          I checked the help file you listed and found some code in it for FindFiles, but it looks like it's for an API and I can't get it to run in my macro because of the reference to EdmVault5. I've never made an API and don't want to try and go down that road just yet. Is there some sample code you can send me to chew on?

                           

                          Thanks for your help!


                          Matt

                            • Re: VBA Excel to EPDM Search
                              Jeremiah Davis

                              Hello Matt,

                               

                              Depending on your version of EPDM, yes. Once you have added this as a reference, you should be able to call the EPDM commands.

                               

                              The sample code you see for a stand alone program will also work inside your macro. Admittedly the example shown for this is a bit involved, but this functionality can be used with only a small portion of that code.

                    • Re: VBA Excel to EPDM Search
                      Matt Finley

                      I'm still struggling with this and can't get anything to work via the EPDM API (it loads the resource with no errors but the macro won't run).

                       

                      I hate to ask for handouts but I've burned several hours on this and am out of ideas. Do you have a code sample that I can use to "touch" and get the latest version using the windows code above?

                      Thanks.

                        • Re: VBA Excel to EPDM Search
                          Jeremiah Davis

                          Hello Matt,

                           

                          Is it possible for you to post the spreadsheet you are working on? Or at least a representative sample of with your existing macro so that I might be able to modify it?


                          Tomorrow I am spending some time on a plane along with a 2 and a half hour layover in Atlanta. This might be a good time to try and play with this.

                            • Re: VBA Excel to EPDM Search
                              Matt Finley

                              Hi Jeremiah,


                              Hope you have an uneventful flight (I HATE flying, crashed at Avalon on Catalina Island while in High School).

                               

                              The spreadsheets I typically work on is very simple, they typically have a list of Part Numbers in a single column and I want to be able to click on the cell with the part number in it and open the model in Solidworks. I do this for files that are not in the vault, I do it for .pdf drawings that are in an unlocked file space. I just can't get to the stuff in the vault.


                              I use this code for non-PDM models:

                              Dim CellPointer As String
                              Dim sWS As String
                              Dim MyFile As String
                              Dim cPath, cAssyPath As String
                              Dim cLocalPath As String
                              Dim bInSandbox As Boolean
                              cLocalPath = "C:\CPDM Sandbox\"
                              cAssyPath = "N:\Solidworks\MASTER\Assemblies\" & Left(ActiveCell.Value, 3) & "\"
                              cPath = "N:\Solidworks\MASTER\Parts\" & Left(ActiveCell.Value, 3) & "\"
                              On Error GoTo Error
                              sWS = ActiveSheet.Name
                                  CellPointer = ActiveCell.Value
                              If CellPointer = "" Then GoTo Error
                              MyFile = dir(cLocalPath & Left(CellPointer, 7) & "*.sldprt")
                              If MyFile <> "" Then
                                  Shell "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & (cLocalPath & MyFile), vbNormalFocus
                                  MsgBox MyFile & " Found in Sandbox", vbInformation, "Loading SLDPRT from Sandbox"
                                  GoTo Error
                              End If

                              MyFile = dir(cPath & Left(CellPointer, 7) & "*.sldprt")
                              If MyFile <> "" Then
                                  Shell "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & (cPath & MyFile), vbNormalFocus
                                  MsgBox MyFile & " Found on N Drive", vbInformation, "Loading SLDPRT from N Drive"
                                  GoTo Error
                              End If

                              MyFile = dir(cAssyPath & Left(CellPointer, 7) & "*.sldasm")
                              If MyFile <> "" Then
                                  Shell "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & (cAssyPath & MyFile), vbNormalFocus
                                  MsgBox MyFile & " Found on N Drive", vbInformation, "Loading SLDASM from N Drive"
                                  GoTo Error
                              End If

                              Error:
                              End Sub


                              It uses the shell command to launch the part into Solidworks. Note that this method works inside the vault but ONLY on parts I've already cached.

                               

                              The code I've been trying for inside the vault is at the top and is very different because it is iterating through sub directories.

                               

                              Really appreciate any help you can give on this.

                                • Re: VBA Excel to EPDM Search
                                  Jeremiah Davis

                                  Ok, let's try this. And as mentioned earlier, make sure your excel macro adds the reference to the PDMWorks Enterprise 201x Type Library

                                   

                                  Sub FindFile()

                                   

                                  Dim CellPointer As String

                                  Dim sWS As String

                                  Dim MyFile As String

                                  Dim cPath, cAssyPath As String

                                  Dim cLocalPath As String

                                  Dim bInSandbox As Boolean

                                  Dim RetVal As Long

                                   

                                  Dim Vault As IEdmVault14

                                  Dim eFile As IEdmFile9

                                  Dim eFolder As IEdmFolder7

                                   

                                  cLocalPath = "C:\ACME\Projects\P-00002\CAD Files\"

                                  cAssyPath = "N:\Solidworks\MASTER\Assemblies\" & Left(ActiveCell.Value, 3) & "\"

                                  cPath = "N:\Solidworks\MASTER\Parts\" & Left(ActiveCell.Value, 3) & "\"

                                   

                                  On Error GoTo Error

                                   

                                  sWS = ActiveSheet.Name

                                      CellPointer = ActiveCell.Value

                                   

                                  If CellPointer = "" Then GoTo Error

                                   

                                  Set Vault = New EdmVault5

                                  Vault.LoginAuto "ACME", 0

                                  Set eFile = Vault.GetFileFromPath(cLocalPath & CellPointer & ".sldprt", eFolder)

                                   

                                  If eFile Is Nothing Then

                                  Else

                                      eFile.GetFileCopy 0, "", 0

                                  End If

                                   

                                  'MyFile = Dir(cLocalPath & Left(CellPointer, 7) & "*.sldprt")

                                  MyFile = Dir(cLocalPath & CellPointer & "*.sldprt")

                                   

                                  If MyFile <> "" Then

                                      Shell "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & (cLocalPath & MyFile), vbNormalFocus

                                      MsgBox MyFile & " Found in Sandbox", vbInformation, "Loading SLDPRT from Sandbox"

                                      GoTo Error

                                  End If

                                   

                                  MyFile = Dir(cPath & Left(CellPointer, 7) & "*.sldprt")

                                   

                                  If MyFile <> "" Then

                                      Shell "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & (cPath & MyFile), vbNormalFocus

                                      MsgBox MyFile & " Found on N Drive", vbInformation, "Loading SLDPRT from N Drive"

                                      GoTo Error

                                  End If

                                   

                                  MyFile = Dir(cAssyPath & Left(CellPointer, 7) & "*.sldasm")

                                   

                                  If MyFile <> "" Then

                                      Shell "RunDLL32.EXE shell32.dll,ShellExec_RunDLL " & (cAssyPath & MyFile), vbNormalFocus

                                      MsgBox MyFile & " Found on N Drive", vbInformation, "Loading SLDASM from N Drive"

                                      GoTo Error

                                  End If

                                   

                                  Error:

                                   

                                  End Sub