3 Replies Latest reply on Feb 23, 2014 8:50 AM by Deepak Gupta

    Get Custom Props from Excel-Write to Assy model

    Bernie Daraz

      OK, I hope I can ask for some help here as I've done as much as I can with my experience level.

       

      I'm trying to write a number of configuration specific custom properties to an assembly file for use in drawing automation. I'm pulling the data from an Excel file where I keep all the information on the models, part numbers, revisions, paint codes, ect. I currently have it working using the property tab builder but a bit more automation would help a lot and most likely prevent some transcription errors.

       

      I'm using SW 2012 for this project.

       

      I've copied and modified code from the web and have this:

       

      Sub main()

          Set swApp = Application.SldWorks

          Set swModel = swApp.ActiveDoc

          Set xl = GetObject(, "Excel.Application")

          Set xlsh = xl.ActiveSheet

          

          PARTNUMBER = xlsh.Cells(1, 1)

          Revision = xlsh.Cells(1, 2)

          PartCode = xlsh.Cells(1, 3)

          Description = xlsh.Cells(1, 4)

          INVPART1 = xlsh.Cells(1, 5)

          FINLIST1 = xlsh.Cells(1, 6)

          INVPART2 = xlsh.Cells(1, 7)

          FINLIST2 = xlsh.Cells(1, 8)

          INVPART3 = xlsh.Cells(1, 9)

          FINLIST3 = xlsh.Cells(1, 10)

          INVPART4 = xlsh.Cells(1, 11)

          FINLIST4 = xlsh.Cells(1, 12)

          INVPART5 = xlsh.Cells(1, 13)

          FINLIST5 = xlsh.Cells(1, 14)

          INVPART6 = xlsh.Cells(1, 15)

          FINLIST6 = xlsh.Cells(1, 16)

       

          'Add the Custom Property Name with value. Will not overwrite if there is any existing property.

          swModel.AddConfiguration3 "PARTNUMBER", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(PARTNUMBER)

            retVal = swCustPropMgr.Add2("PARTNUMBER", swCustomInfoText, PARTNUMBER)

            swCustPropMgr.Set "PARTNUMBER", PARTNUMBER

           

          swModel.AddConfiguration3 "Revision", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(Revision)

            retVal = swCustPropMgr.Add2("Revision", swCustomInfoText, Revision)

            swCustPropMgr.Set "Revision", Revision

           

          swModel.AddConfiguration3 "PartCode", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(PartCode)

            retVal = swCustPropMgr.Add2("PartCode", swCustomInfoText, PartCode)

            swCustPropMgr.Set "PartCode", PartCode

            

          swModel.AddConfiguration3 "Description", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(Description)

            retVal = swCustPropMgr.Add2("Description", swCustomInfoText, Description)

            swCustPropMgr.Set "Description", Description

         

          swModel.AddConfiguration3 "INVPART1", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(INVPART1)

            retVal = swCustPropMgr.Add2("INVPART1", swCustomInfoText, INVPART1)

            swCustPropMgr.Set "INVPART1", INVPART1

         

          swModel.AddConfiguration3 "FINLIST1", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(FINLIST1)

            retVal = swCustPropMgr.Add2("FINLIST1", swCustomInfoText, FINLIST1)

            swCustPropMgr.Set "FINLIST1", FINLIST1

       

          swModel.AddConfiguration3 "INVPART2", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(INVPART2)

            retVal = swCustPropMgr.Add2("INVPART2", swCustomInfoText, INVPART2)

            swCustPropMgr.Set "INVPART2", INVPART2

       

          swModel.AddConfiguration3 "FINLIST2", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(FINLIST2)

            retVal = swCustPropMgr.Add2("FINLIST2", swCustomInfoText, FINLIST2)

            swCustPropMgr.Set "FINLIST2", FINLIST2

       

          swModel.AddConfiguration3 "INVPART3", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(INVPART3)

            retVal = swCustPropMgr.Add2("INVPART3", swCustomInfoText, INVPART3)

            swCustPropMgr.Set "INVPART3", INVPART3

         

          swModel.AddConfiguration3 "FINLIST3", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(FINLIST3)

            retVal = swCustPropMgr.Add2("FINLIST3", swCustomInfoText, FINLIST3)

            swCustPropMgr.Set "FINLIST3", FINLIST3

       

          swModel.AddConfiguration3 "INVPART4", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(INVPART4)

            retVal = swCustPropMgr.Add2("INVPART4", swCustomInfoText, INVPART4)

            swCustPropMgr.Set "INVPART4", INVPART4

         

          swModel.AddConfiguration3 "FINLIST4", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(FINLIST4)

            retVal = swCustPropMgr.Add2("FINLIST4", swCustomInfoText, FINLIST4)

            swCustPropMgr.Set "FINLIST4", FINLIST4

       

          swModel.AddConfiguration3 "INVPART5", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(INVPART5)

            retVal = swCustPropMgr.Add2("INVPART5", swCustomInfoText, INVPART5)

            swCustPropMgr.Set "INVPART5", INVPART5

         

          swModel.AddConfiguration3 "FINLIST5", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(FINLIST5)

            retVal = swCustPropMgr.Add2("FINLIST5", swCustomInfoText, FINLIST5)

            swCustPropMgr.Set "FINLIST5", FINLIST5

       

          swModel.AddConfiguration3 "INVPART6", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(INVPART6)

            retVal = swCustPropMgr.Add2("INVPART6", swCustomInfoText, INVPART6)

            swCustPropMgr.Set "INVPART6", INVPART6

         

          swModel.AddConfiguration3 "FINLIST6", "", "-", 0

            Set swCustPropMgr = swModel.Extension.CustomPropertyManager(FINLIST6)

            retVal = swCustPropMgr.Add2("FINLIST6", swCustomInfoText, FINLIST6)

            swCustPropMgr.Set "FINLIST6", FINLIST6

       

          swModel.DeleteConfiguration2 "Default"

          End Sub

       

      It errors out and presents me with an Object varible error 91.

       

      I can see during debug that it successfully reads the Excel file and has the proper data associated with the custom property names but I see this at the break: (I imagine I would see this at every break.)

      Macro Error.jpg

      I must have some stupid error here and I'm ready to give up. I would appreciate any and all help! Thanks!

        • Re: Get Custom Props from Excel-Write to Assy model
          Deepak Gupta

          Bernie are you sure you want to add configuration names based on the revision property. Because this line is doing so

          swModel.AddConfiguration3 "PARTNUMBER", "", "-", 0

           

          Further this line connects to that configuration.

           

          Set swCustPropMgr = swModel.Extension.CustomPropertyManager(PARTNUMBER)

           

          And I believe since your property name, property value name and configuration name are same, the macro is getting confued.

           

          If above is correct i.e. you need to add configurations also then try this modified/simplified code and share how it goes with you.

           

          Sub main()

              Set swApp = Application.SldWorks

              Set swModel = swApp.ActiveDoc

             

              PARTNUMBER1 = "123"

              Revision1 = "ABC"

             

              'Add the Custom Property Name with value. Will not overwrite if there is any existing property.

              swModel.AddConfiguration3 "PARTNUMBER", "", "-", 0

                Set swCustPropMgr = swModel.Extension.CustomPropertyManager(PARTNUMBER)

                retVal = swCustPropMgr.Add2("PARTNUMBER2", swCustomInfoText, PARTNUMBER1)

                swCustPropMgr.Set "PARTNUMBER", PARTNUMBER

              

              swModel.AddConfiguration3 "Revision", "", "-", 0

                Set swCustPropMgr = swModel.Extension.CustomPropertyManager(Revision)

                retVal = swCustPropMgr.Add2("Revision2", swCustomInfoText, Revision1)

                swCustPropMgr.Set "Revision", Revision    

            

              swModel.DeleteConfiguration2 "Default"

              End Sub

          If not then share the exact need and I'll have the codes modified for you.

            • Re: Get Custom Props from Excel-Write to Assy model
              Bernie Daraz

              Thanks Deepak!

               

              I modified the code to your suggestion and it works. The only issue now I see is that it is writing to custom properties and not configuration specific properties:

              Macro 2 - Custom.jpg

              I still have the Property Builder version that I use when I revise older files and reset the drawing formats, the inserted 'properties' do not appear there.

               

              I imagine the 'names' used to store and manipulate the values will change.

               

              The revision is used for the drawing only. I use Pack and Go to store and archive versions. If you need to edit a part (all parts are saved as assemblies) it is accessed from the top level drawing. This eliminates changing part names when revisions occur, though each part has a revision level as a custom property as well.

               

              Obviously writing macros is not my cup of tea, thanks again for your help.

               

              ___________

               

              On second thought it might be better to have the macro ENABLE overwriting of the existing peoperties.

               

              Message was edited by: Bernie Daraz

                • Re: Get Custom Props from Excel-Write to Assy model
                  Deepak Gupta

                  Sorry Bernie, did read you original post clearly. The codes you had up there are for custom properties, so I had set them for custom properties only. Please try the following codes:

                   

                  Option Explicit

                   

                   

                      Dim swApp               As SldWorks.SldWorks

                      Dim swModel             As SldWorks.ModelDoc2

                      Dim swConfigMgr         As SldWorks.ConfigurationManager

                      Dim swConfig            As SldWorks.Configuration

                      Dim swCustPropMgr       As SldWorks.CustomPropertyManager

                      Dim PARTNUMBER1         As String

                      Dim Revision1           As String

                      Dim PARTNUMBER2         As String

                      Dim Revision2           As String

                   

                  Sub main()

                   

                   

                      Set swApp = Application.SldWorks

                      Set swModel = swApp.ActiveDoc

                      Set swConfigMgr = swModel.ConfigurationManager

                      Set swConfig = swConfigMgr.ActiveConfiguration

                      Set swCustPropMgr = swConfig.CustomPropertyManager

                     

                      PARTNUMBER1 = "123"

                      Revision1 = "ABC"

                   

                      swModel.AddCustomInfo3 swConfig.Name, "PARTNUMBER2", swCustomInfoText, PARTNUMBER1

                      swCustPropMgr.Set "PARTNUMBER2", PARTNUMBER1

                     

                      swModel.AddCustomInfo3 swConfig.Name, "Revision2", swCustomInfoText, Revision1

                      swCustPropMgr.Set "Revision2", Revision1

                       

                  End Sub