5 Replies Latest reply on Mar 27, 2018 3:09 AM by Husein Delic

    Run PDM task to open/read excel file

    Husein Delic

      Hi,

       

      I have a macro that opens and read from an excel file and adds custom properties to SW part. I am looking to integrate it in PDM task, but it looks like it needs

      to run it, which PDM task cant handle? I have also tired to use execution command to open the excel file which works, but still cant read from it. Any ideas or suggestions are welcome.

       

      Thanks!

       

      Here is the macro which I reduced to keep it simple:

       

      Dim swApp           As SldWorks.SldWorks

      Dim xlApp           As Excel.Application

      Dim xlSheet         As Excel.Worksheet

      Dim xlBooks         As Excel.Workbooks

      Dim swModel         As SldWorks.ModelDoc2

      Dim nErrors         As Long

      Dim nWarnings       As Long

       

       

       

      Sub main()

         

          Set swApp = Application.SldWorks

          Set swModel = swApp.OpenDoc6("<Filepath>", swDocPART, swOpenDocOptions_Silent, "", nErrors, nWarnings)

          swApp.Visible = True

       

          Set xlApp = CreateObject("Excel.Application")

          xlApp.Visible = True

          xlApp.Workbooks.Open "C:\EPDM\Projects\BOM_Part Number.xlsx"

          Set xlSheet = xlApp.ActiveSheet

             

      End Sub

        • Re: Run PDM task to open/read excel file
          Artem Taturevych

          You can use late binding. Just replace all of your specific Excel declarations to objects, i.e.

           

          Dim xlApp           As Excel.Application Object

          Dim xlSheet         As Excel.Worksheet Object

          Dim xlBooks         As Excel.Workbooks Object

            • Re: Run PDM task to open/read excel file
              Husein Delic

              Thanks for reply, but after the change its still not working.

              • Re: Run PDM task to open/read excel file
                Husein Delic

                Actually, "As Object" works only to open the excel document but cant finish. Here is the complete macro what I am trying to run via PDM task

                 

                Dim swApp           As SldWorks.SldWorks

                Dim xlApp           As Object

                Dim xlSheet         As Object

                Dim xlBooks         As Object

                Dim swModel         As SldWorks.ModelDoc2

                Dim swCustPrpMgr    As SldWorks.CustomPropertyManager

                Dim xlRange         As Range

                Dim vConfNameArr    As Variant

                Dim nErrors         As Long

                Dim nWarnings       As Long

                Dim i               As Integer

                Dim searchRes       As Range

                Dim name            As String

                Dim index           As Integer

                Dim title           As String

                 

                 

                Sub main()

                   

                 

                 

                    Set swApp = CreateObject("SldWorks.Application")

                    Set swModel = swApp.OpenDoc6("<Filepath>", swDocPART, swOpenDocOptions_Silent, "", nErrors, nWarnings)

                    swApp.Visible = True

                 

                 

                    Set xlApp = CreateObject("Excel.Application")

                    xlApp.Visible = True

                    xlApp.Workbooks.Open "C:\EPDM\Projects\BOM_Part Number.xlsx"

                    Set xlSheet = xlApp.ActiveSheet  

                   

                    Set swCustPrpMgr = swModel.Extension.CustomPropertyManager("")

                    Set xlRange = xlSheet.Range(xlApp.Cells(1, 1), xlApp.Cells(1000, 1))

                   

                    title = swModel.GetTitle()

                    index = InStrRev(title, ".")

                    name = Left(title, Len(title))

                    Set searchRes = xlRange.Cells.Find(name)

                  

                    If Not searchRes Is Nothing Then

                        LinkPrpToCell searchRes.row, 2, "Part No."

                        LinkPrpToCell searchRes.row, 3, "Vendor"

                        LinkPrpToCell searchRes.row, 4, "Material"

                       

                    End If

                   

                End Sub

                 

                 

                Sub LinkPrpToCell(row As Integer, col As Integer, prpName As String)

                 

                 

                    Dim value As String

                    Dim field As String

                   

                    field = prpName

                    value = xlApp.Cells(row, col).value

                       

                    swCustPrpMgr.Add2 field, swCustomInfoType_e.swCustomInfoText, value

                    swCustPrpMgr.Set field, value

                   

                   

                    vConfNameArr = swModel.GetConfigurationNames

                     For i = 0 To UBound(vConfNameArr)

                 

                 

                        sConfigName = vConfNameArr(i)

                            bShowConfig = swModel.ShowConfiguration2(sConfigName)

                            bRebuild = swModel.ForceRebuild3(False)

                            Set swCustPrpMgr = swModel.Extension.CustomPropertyManager(vConfNameArr(i))

                            swCustPrpMgr.Add2 field, swCustomInfoType_e.swCustomInfoText, value

                            swCustPrpMgr.Set field, value

                           

                        Next i

                   

                swModel.Save3 13, nErrors, nWarnings '13 = swSaveAsOptions_Silent + swSaveAsOptions_AvoidRebuildOnSave + swSaveAsOptions_SaveReferenced

                End Sub