2 Replies Latest reply on Aug 4, 2014 10:08 PM by Damon Frashure

    SW/Excel find value

    Damon Frashure

      Hello,

       

       

      I'm working on a macro that should find a value in column A of the active Excel sheet, return the row number to the API, and then use that value to tranverse the row using the values to create custom properties.  I was able to write a very simple macro that will use a hard coded row number to make the custom properties, but I can't figure out the finding the part about finding the value programmatically.  Does anyone have any thoughts on how to do this?  Also, can anyone point me toward a resource about the different excel functions available in the SolidWorks API?

       

      Thanks in advance.

        • Re: SW/Excel find value
          Artem Taturevych

          Hello Damon,

           

          I guess you are looking for something like this:

           

          Dim swApp       As SldWorks.SldWorks

          Dim swModel     As SldWorks.ModelDoc2

          Dim xlsh As Object

           

          Sub main()

           

          Set swApp = Application.SldWorks

          Set swModel = swApp.ActiveDoc

          ' Attach to active Excel object

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

          ' Get  active sheet in Excel

          Set xlsh = xl.ActiveSheet

           

          ' Get  value from Excel cells

          row = GetRowIndex("PN9")

          column = 2

           

          If row <> -1 Then

              While xlsh.Cells(row, column) <> ""

                  swModel.AddCustomInfo3 "", xlsh.Cells(1, column), swCustomInfoText, xlsh.Cells(row, column) 'Add the Custom Property Name with value. Will not overwrite if there is any existing property.

                  column = column + 1

              Wend

          Else

              MsgBox ("Failed to lookup value")

          End If

           

          End Sub

           

          Function GetRowIndex(lookupValue As String, Optional colToLookup As Integer = 1) As Integer

           

              Dim rowInd As Integer

                 

              Dim val As String

              rowInd = 1

             

              Do

                  val = xlsh.Cells(rowInd, colToLookup)

                  If val = lookupValue Then

                      GetRowIndex = rowInd

                      Exit Function

                  End If

                 

                  rowInd = rowInd + 1

           

              Loop While val <> ""

           

              GetRowIndex = -1

             

          End Function

          Please note that there is no such thing as Excel function in SolidWorks API. SolidWorks doesn't propagate any Excel APIs to their interface. But you can use all Excel API function available in native Excel API as you are doing this now.

          ______________________________________________

          Regards, Artem Taturevych | Snr. Developer | IC3D ANZ

           

          IC3DSteel – New Steel Solution for SolidWorks

          LinkedIn - SolidWorks API Group