CIM Lab 6: Downloading Bill of Material from Solidworks into ParentChildRelations table in :
We will be adding some features to the form created in Lab 5. When this form is fully working, the user
will press a button on the form which will open a “File Open” dialog box. The user will then select a
SolidWorks assembly. Then the software will automatically read the name of the assembly and the
children. It will create part names for the assembly and the parts. Then it will create the parent child
relations between the assembly and the parts. Lastly, it will show the assembly in the
The steps are as follows:
Opening up the Database that was created in Lab 5
Open the form: ParentChildRelections in Design View
Add a Microsoft Common Dialog Box Control to the form: Under the Design tab select Active X
Controls and select Microsoft Common Dialog Box Control
Right click on the newly created common dialog control, select properties and change the name of the
control to : ComDia
Add a command button to the form, select Report Operation – Open Report
For the button’s caption, type in Read SolidWorks Assembly, then click Next.
Type anything in the command line, then click Finish
Change the name on the button to Read Solidworks Assembly:
Change the name of the Command Button to ReadAssembly:
Right click on the newly created command button.
Select Event| OnClick :
Remove the existing code for that event and replace with the following code on the On Click event for the
Dim swApp As SldWorks.SldWorks
Dim swModel As SldWorks.ModelDoc2 ' a SolidWorks model document
Dim fullAssemLoc As String
Dim longstatus As Long, longwarnings As Long
Dim docName As String ' the save name chosen by the user for the assembly without the ".sldasm"
Dim FinalDirLoc As String ' save-to location,
Dim fullDrawLoc As String
Dim swConf As SldWorks.Configuration
Dim swRootComp As SldWorks.Component2
Set dicComps = CreateObject("Scripting.Dictionary")
.CancelError = False
.InitDir = "c:\"
.Filter = "Assembly (*.sldasm)|*.sldasm"
fullAssemLoc = .FileName
Dim posn1 As Integer
Dim posn2 As Integer
If Not fullAssemLoc = "" Then
'txtLocation.Text = fullAssemLoc
fullDrawLoc = Mid$(fullAssemLoc, 1, Len(fullAssemLoc) - 6) + "slddrw"
posn1 = InStrRev(fullAssemLoc, "\")
posn2 = InStrRev(fullAssemLoc, ".")
FinalDirLoc = Left$(fullAssemLoc, posn1)
docName = Mid$(fullAssemLoc, posn1 + 1, (posn2 - posn1) - 1)
Set swApp = CreateObject("SldWorks.Application")
Set swModel = swApp.OpenDoc6(fullAssemLoc, swDocASSEMBLY, 0, "", longstatus, longwarnings)
Set swModel = swApp.ActivateDoc2(docName, False, longstatus)
Set swModel = swApp.ActiveDoc
Set swConf = swModel.GetActiveConfiguration
Set swRootComp = swConf.GetRootComponent
swApp.Visible = True
Add the following lines to the top of the code:
Dim dbs As Database
Dim sFileName As String, lPrevAssy(10) As Long, iDepth As Integer
Dim objACAD As Object, iDwgCount As Integer, bAssyIDHasFocus As Boolean
Dim swApp As SldWorks.SldWorks, swDoc As SldWorks.ModelDoc2
Dim dicComps As Object
The top of the code should now look like this:
At the bottom of the code, add the following two subroutines:
Sub TraverseComponent(swComp As SldWorks.Component2)
Dim vChildComp As Variant
Dim swChildComp As SldWorks.Component2
Dim swCompConfig As SldWorks.Configuration
Dim i As Long
vChildComp = swComp.GetChildren
For i = 0 To UBound(vChildComp)
Set swChildComp = vChildComp(i)
If Not swChildComp Is Nothing Then
Dim path As String
Dim curCount As Integer
path = swChildComp.GetPathName
If dicComps.Exists(path) Then
curCount = dicComps.Item(path)
dicComps.Item(path) = curCount + 1
curCount = 0
dicComps.Add swChildComp.GetPathName, curCount + 1
Dim keys As Variant
Dim i As Integer
keys = dicComps.keys
For i = 0 To UBound(keys)
Dim compName As String
Dim vStrings As Variant
vStrings = Split(keys(i), "\")
compName = vStrings(UBound(vStrings))
vStrings = Split(compName, ".")
compName = vStrings(0)
Debug.Print compName & " -> " & dicComps.Item(keys(i))
Open up visual basic and click on View | Immediate window.
Add the references: - note that the version of Solidworks will be based on the version installed on the
Add Solidworks Constant type library, Solidworks Type Library, sldFuncFeat Type Library, and
Solidworks Extensibility Type library (note that if it does not show up in the list, you can click browse and
The final references should look like this
Testing the code thus far.
Save and close the form. Open up a window showing the visual basic code and show the Immediate
Window (View | Immediate Window)
Open another window showing the form in Form View.Click on the Read SolidWorks Assembly button
and open a pre-existing assembly.
Switch over to the visual basic code and the names of the parts in the assembly should appear in the
immediate window. The names of the children should be showing up in the immediate window as
Now we are going to add functionality to the visual basic to create records in the Products table for the
assembly and each component in the assembly. Then we will create parent child relations between the
assembly and the parts in the ParentChildRelations table. Lastly, we will show the assembly in the
For this part, I will give you the lines of Visual Basic code, and you must figure out where to put them in
First, since we are going to be accessing the database directly using visual basic, we will need to
dimension a variable to represent the database. Make sure that the following line of code exists at the top
of the visual basic code – it should already be there from earlier work. This line creates a variable to
represent the database:
Dim dbs As Database
Second, we need to initialize the variable dbs with setting it to point to the current database using the
following line of code:
Set dbs = CurrentDb
This needs to be put somewhere in the subroutine ReadAssembly_Click before we try to write
anything to the database.
Third, we will create a record in the Products table corresponding to the assembly name with the
following line of code:
dbs.Execute "insert into Products (ProductName) VALUES ('" & docName & "');"
The function dbs.Execute will execute a line of SQL (Structured Query Language), which is the language
used to talk directly to databases. In this situation, we are using the insert into command. More
information about the insert into command is available at http://www.w3schools.com/sql/sql_insert.asp
This line of code needs to be put into the ReadAssembly_Click subroutine sometime after the
program has figured out a value for docName
Fourth, now that we have created a record with the information about the assembly, we need to get the
ID of that newly created record for later use when we add parent child relations. To do that, the following
two lines of code need to be added.
Dim ID As Long
ID = DLookup("[ProductID]", "Products", "[ProductName] LIKE """ & docName &
Fifth, we will need to pass this ID down to PrintData subrouting. Modify the call to the PrintData
subrourtine so it looks like this:
Sixth, the declaration for the PrintData subroutine needs to be modified to accept the variable we are
passing it as follows:
Sub PrintData(ParentID As Long)
This way the ID for the parent will get passed to the PrintData subroutine.
Seventh, we need to create records the Products table for each of the components (children) in the
assembly using the following line of code.
dbs.Execute "insert into Products (ProductName) VALUES ('" & compName & "');"
Make sure this line of code is inside a loop that goes through all the components and make sure it is after
the line that assigns a value to compName.
Eighth, for each recorded created in the Products table, we need to get the ID of the newly created record
so that we can create relations between the parent and the children. The following lines of code
Dim ID As Long
ID = DLookup("[ProductID]", "Products", "[ProductName] LIKE """ & compName &
Ninth, the ParentChildRelations table also stores information about the quantity of each component, so
we need to capture that information using that following lines of code.
Dim Quantity As Single
Quantity = dicComps.Item(keys(i))
Tenth, we need to create record in the ParentChildRelations table with the information about the parent,
the child, and the quantity using the following lines of code:
dbs.Execute "insert into ParentChildRelations (HufParent, HufChild,
Quantity) VALUES (" & ParentID & "," & ID & "," & Quantity & ");"
Eleventh, after we have looped through all the components, we can close out the connection to the
database using the following line:
Twelfth, the last thing we do now that all the information is stored in the database, is to show assembly in
our form using the following lines of code:
ParentComboBox = ID
Make sure you save your form as it will also save all the VB code
Now you can test this code by clicking on the Read SolidWorks Assembly button on the form.
To show that it is working, please demonstrate it to me. Also print out a screen shot of the form
with the assembly information contained, also print out your Visual Basic Code.