6 Replies Latest reply on Apr 2, 2018 1:32 PM by David Watkins

    Get file variable values from PDM to Excel using VBA

    Martin Solem

      Hi all,

      I have an Excel file containing a list of file names and I want to create an Excel macro that will loop through all file names and send variable values to cells in Excel.

      I have never been a programmer, so even VBA is on the advanced side, but i have some sample macro codes to help me. Problem is they are all based on one thing; the folder path is already known, as a static value, or part of the Excel data. Since i have only file names (i assume unique file names in vault), i can't seem to find a valid folder object. I know there are more than a few programming savvy members here, so i'm reaching out...

       

      I have something like this:

       

           Dim oFile As EdmLib.IEdmFile7

           Dim oFolder As EdmLib.IEdmFolder6

           Set oVault = New EdmLib.EdmVault5

           Dim sFileName as String

       

      I can log in fine, and i can read my Excel Sheet

           sFilename = Range("B" & i).Value

       

      But then my struggle begin, how do i set oFile and oFolder objects using the information i have in sFileName?

       

      Thank you in advance.

        • Re: Get file variable values from PDM to Excel using VBA
          Michael Dekoning

          Martin,

          You will need to search for the file in the vault using EdmLib.IEdmSearch5 then loop through the result(s) using EdmLib.IEdmSearchResult5 to get the path. Although newer API documentation no longer includes VBA, you can look at the examples for VB.Net which will be close.

          • Re: Get file variable values from PDM to Excel using VBA
            Lee CS Young

            I would export a list of files from the vault using the Search tool and do a LOOKUP to get the path. Michael's solution will work of course, but it may be more overhead than is required. Once you have the path you can get the file using oVault.GetFileFromPath.

            • Re: Get file variable values from PDM to Excel using VBA
              Martin Solem

              Thank you so much Michael Dekoning and Lee CS Young for your input.

              I went with the search method, and after a few many, many hours of coding, i now have a fully working macro.

               

              M

              • Re: Get file variable values from PDM to Excel using VBA
                David Watkins

                I am in need of something like this as well. I have a excel VBA macro working, searching for parts numbers and getting results. Now what I need, and can't find a way to do, is to get the variable & values for a file. I would like to get all variables and then I can loop through for what I need in VBA. I looked at the results but did not find any variable info. I tried Search.AddVariable but I never got it to work and feel it is not really the correct way to go about this. Would someone post VBA code on how they have done this?

                 

                Thanks,

                David

                  • Re: Get file variable values from PDM to Excel using VBA
                    Ulf Stockburger

                    Hi David

                     

                    Here is a macro that gets all variables in all configurations and writes them into a sheet. You can use it for starting with variables and modify it to your needs.

                     

                    ' Use the 'PDMWorks Enterprise xxxx Type Library'

                    ' e.g. C:\Program Files\SOLIDWORKS Corp\SOLIDWORKS PDM\EdmInterface.dll

                    Option Explicit

                    ' Sheet name in which the variables and values will be written

                    Const sheetName = "<sheet name>"

                    ' Vault name

                    Const epdmVault = "<vault name>"

                    ' File name from which the variables will be read

                    Const fName = "<file name with path>"

                    Sub main()

                        ' Variables in loops

                        Dim currentRow As Long

                        Dim currentColumn As Long

                        currentColumn = 1

                        Dim i As Long

                        ' Log into the vault

                        Dim vault5 As EdmVault5

                        Set vault5 = New EdmVault5

                        If Not (vault5.IsLoggedIn) Then

                            vault5.LoginAuto epdmVault, 0

                        End If

                        ' Objects for the specified file and folder

                        Dim file5 As IEdmFile5

                        Dim folder5 As IEdmFolder5

                        ' Gets the object for the file

                        Set file5 = vault5.GetFileFromPath(fName, folder5)

                        ' Objects for the variables

                        Dim var7 As IEdmEnumeratorVariable7

                        Dim ppoRetVars() As Variant

                        Dim ppoRetConfs() As String

                        Dim poRetDat As EdmGetVarData

                        Dim var6 As IEdmVariableValue6

                        ' Gets all variables in all configurations from the database

                        Set var7 = file5.GetEnumeratorVariable

                        var7.GetVersionVars 0, folder5.ID, ppoRetVars, ppoRetConfs, poRetDat

                        ' Objects for the configurations

                        Dim configs As EdmStrLst5

                        Dim pos5 As IEdmPos5

                        Dim config As String

                        ' Gets the configurations for the current file

                        Set configs = file5.GetConfigurations()

                        Set pos5 = configs.GetHeadPosition()

                        ' Loop over all configurations

                        While (Not pos5.IsNull)

                            currentRow = 1

                            ' Gets the current configuration name

                            config = configs.GetNext(pos5)

                            Sheets(sheetName).Cells(currentRow, currentColumn).Value = config

                            'Gets all variables and values for the current configuration and writes them into the sheet

                            For i = LBound(ppoRetVars) To UBound(ppoRetVars)

                                currentRow = currentRow + 1

                                Set var6 = ppoRetVars(i)

                                Sheets(sheetName).Cells(currentRow, currentColumn).Value = var6.VariableName

                                Sheets(sheetName).Cells(currentRow, currentColumn + 1).Value = var6.GetValue(config)

                            Next

                            currentColumn = currentColumn + 2

                        Wend

                    End Sub

                     

                    BiI SAP-PDM