AnsweredAssumed Answered

I am Looking for a Macro that let me change the part number in an assembly and lets me read all the Dimensions?

Question asked by Gavin Crasto on Oct 17, 2014

This is the Current setup of my code, I need to be able read all dimensions of a part and change the part number, I was looking for an example of a macro that can do that:@

 

Dim xlApp As Excel.Application

Dim xlBook As Excel.Workbook

Dim xlSheet As Excel.Worksheet

Dim i As Integer

Dim j As Integer

Dim k As Integer

 

Private Sub UserForm_Initialize()

Set xlApp = CreateObject("Excel.Application")                       'Create Excel Application Object

Set xlBook = xlApp.Workbooks.Open("C:\Users\gcrasto\Downloads\Material.xlsx", , True)  'Opens Workbook in ReadOnly format

Set xlSheet = xlBook.Worksheets("Legend")                           'Specify Sheet for data to be pulled from

 

With xlSheet

    For i = 2 To 15                                     'Starts Loop at Row 2 and ends at Row 15 (Exception to be built in to ignore blank cells)

        If .Cells(i, 1).Value = "" Then                   'If the current cell in the loop is blank, exit the for loop

            Exit For                                     'Exits Loop

        Else

            ComboBox1.AddItem (.Cells(i, 1).Value)       'If the current cell in the loop is blank, exit the for loop

           

        End If

    Next i

End With

 

End Sub

 

 

Private Sub ComboBox1_Change()  'Event to fire whenever combobox1 selected index is changed

    ComboBox2.Clear 'Clears the contents of combobox2 whenever combobox1 changes selection

  

    With xlSheet

        For j = 2 To 30

        If ComboBox1.Text = "RB" Or ComboBox1.Text = "RT" Or ComboBox1.Text = "RP" Or ComboBox1.Text = "FB" Or ComboBox1.Text = "FS" Or ComboBox1.Text = "FP" Or ComboBox1.Text = "FD" Or ComboBox1.Text = "ST" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SC" Or ComboBox1.Text = "WM" Or ComboBox1.Text = "TR" Then   'Round Bar

            ComboBox2.AddItem (.Cells(j, 5).Value)

        Else

            'do nothing

        End If

         Next j

      End With

      

End Sub

 

 

Private Sub ComboBox2_Change()  'Event to fire whenever combobox1 selected index is changed

  

    With xlSheet

        For k = 2 To 31

        If ComboBox1.Text = "RB" Or ComboBox1.Text = "RT" Or ComboBox1.Text = "RP" Or ComboBox1.Text = "FB" Or ComboBox1.Text = "FS" Or ComboBox1.Text = "FP" Or ComboBox1.Text = "FD" Or ComboBox1.Text = "ST" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SC" Or ComboBox1.Text = "WM" Or ComboBox1.Text = "TR" Then

            ComboBox3.AddItem (.Cells(k, 10).Value)

        Else

            'do nothing

        End If

         Next k

      End With

  

      

End Sub

 

 

Private Sub ComboBox3_Change()

   With xlSheet

        For k = 2 To 63

        If ComboBox1.Text = "RB" Or ComboBox1.Text = "RT" Or ComboBox1.Text = "RP" Or ComboBox1.Text = "FB" Or ComboBox1.Text = "FS" Or ComboBox1.Text = "FP" Or ComboBox1.Text = "FD" Or ComboBox1.Text = "ST" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SC" Or ComboBox1.Text = "WM" Or ComboBox1.Text = "TR" Then

            ComboBox4.AddItem (.Cells(k, 11).Value)

        Else

            'do nothing

        End If

         Next k

      End With

End Sub

 

 

 

 

Private Sub ComboBox4_Change()

    With xlSheet

        For k = 2 To 7

        If ComboBox1.Text = "RB" Or ComboBox1.Text = "RT" Or ComboBox1.Text = "RP" Or ComboBox1.Text = "FB" Or ComboBox1.Text = "FS" Or ComboBox1.Text = "FP" Or ComboBox1.Text = "FD" Or ComboBox1.Text = "ST" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SA" Or ComboBox1.Text = "SI" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SW" Or ComboBox1.Text = "SC" Or ComboBox1.Text = "WM" Or ComboBox1.Text = "TR" Then

            ComboBox5.AddItem (.Cells(k, 12).Value)

        Else

            'do nothing

        End If

         Next k

      End With

End Sub

 

Private Sub CommandButton1_Click()

    Label3 = ComboBox1.Text & ComboBox2.Text & "-" & ComboBox3.Text & "x" & ComboBox4.Text & "x" & ComboBox5.Text

   

End Sub

 

 

Private Sub CommandButton2_Click()

Sub main()

Dim swApp As SldWorks.SldWorks

Dim swModel As SldWorks.ModelDoc2

Dim swAssy As SldWorks.AssemblyDoc

Dim swChildComp As SldWorks.Component2

Dim vChildComp As Variant

Dim boolstatus As Boolean

Dim a As Integer

 

 

Set swApp = Application.SldWorks

Set swModel = swApp.ActiveDoc

Set swAssy = swModel

 

 

vChildComp = swAssy.GetComponents(False)

 

 

For a = 0 To UBound(vChildComp)

Set swChildComp = vChildComp(a)

boolstatus = swChildComp.Select3(True, Nothing)

Next a

 

' NEED CODE HERE

 

End Sub

 

 

End Sub

 

 

 

 

Private Sub CommandButton3_Click()

ComboBox1.ListIndex = -1

ComboBox2.ListIndex = -1

ComboBox3.ListIndex = -1

ComboBox4.ListIndex = -1

ComboBox5.ListIndex = -1

Label3 = ""

 

 

End Sub

 

Private Sub UserForm_Terminate()    'This event is fired when the userform is closed

 

 

xlBook.Close (False)    'Closes the Excel Workbook and does not save changes

xlApp.Quit              'Closes the Excel Application from memory, otherwise each time you run the macro it will create another running process that is impossible to close! without killing it from the task manager or command prompt "tskill excel"

 

 

End Sub

Outcomes