11 Replies Latest reply on Aug 23, 2017 8:04 AM by peter_festesen

    VB Script to Export Parts list information to Excel.

    charl.peters

      Hi,

       

      Does anyone know of a script that can export a Partslist (similar to xDxDesigner) from the xPCB Layout?

      I need Columns for Item Number, Refdes (Grouped), Value, Description etc.

       

      Regards,

       

      Charl Peters

        • 1. Re: VB Script to Export Parts list information to Excel.
          Patrick.Cashman

          I put this together.  It may need a few tweaks to get exactly what you want, but it's mostly what you are looking for:

           

          (note: assumes you already have a reference to doc (PCB document).  Also, this is done in vb.net; for vbscript you'll have to make some adjustments. Finally, the save_file routine will be up to you)

           

              Sub export_PL_bom()

                  Dim comps As MGCPCB.Components

                  Dim comp As MGCPCB.Component

                  Dim pl As New Dictionary(Of String, String())

                  Dim pn, value, desc As String

                  Dim str As New StringBuilder

                  Dim props As MGCPCB.Properties

                  Dim prop As MGCPCB.Property

                  Dim ary() As String

                  Dim row As String

                  Dim n, i As Integer

           

                  comps = doc.Components(0)

                  For Each comp In comps

                      If Not comp.RefDes = "" Then

                          pn = comp.PartNumber

                          props = comp.Properties

                          For Each prop In props

                              value = ""

                              If prop.Name = "Value" Then

                                  value = prop.Value

                              End If

                              If value = "" Then value = "value"

           

           

                              desc = ""

                              If prop.Name = "Description" Then

                                  desc = prop.Value

                              End If

                              If desc = "" Then desc = "description"

                          Next

           

           

                          If Not pl.ContainsKey(pn) Then

                              ReDim ary(3)

                              ary(0) = 1

                              ary(1) = value

                              ary(2) = desc

                              ary(3) = comp.RefDes

                              pl.Add(pn, ary)

                          Else

                              ReDim ary(3)

                              ary = pl(pn)

                              ary(0) = ary(0) + 1

                              ary(3) = ary(3) & ", " & comp.RefDes

                              pl(pn) = ary

                          End If

                      End If

                  Next

           

           

                  Dim kvp As KeyValuePair(Of String, String())

                  str.Length = 0

                  str.AppendLine("ITEM" & vbTab & "PART NO" & vbTab & "QTY" & vbTab & "REFS" & vbTab & "VALUE" & vbTab & "DESCRIPTION")

                  n = 1

                  For Each kvp In pl

                      row = n & vbTab & kvp.Key

                      ary = kvp.Value

                      For i = 0 To UBound(ary)

                          row = row & vbTab & ary(i)

                      Next

                      str.AppendLine(row)

                      n = n + 1

                  Next

           

                  save_file(str.ToString, "Save the BOM file:", "")

              End Sub

          • 2. Re: VB Script to Export Parts list information to Excel.
            charl.peters

            HI Patrick,

             

            Thank you for your help. I am not familiar with running the VB.net in Xpcb. Any pointers would be appreciated.

            • 3. Re: VB Script to Export Parts list information to Excel.
              jeffheidel

              There is an example script located at \SDD_HOME\standard\examples\pcb\Automation\Excel\ called ExcelCompList.xls that might help get you started.  The functions to pull the data from Expedition are in the Excel spreadsheet.

              • 4. Re: VB Script to Export Parts list information to Excel.
                charl.peters

                Thank you for your email. I am currently out of the office on a business trip with limited access to emails but will try and respond as soon as possible. I will be back on the 23rd  August 2017.

                For Urgent matters please contact the office on +27 11 315 8316.

                For Mentor Graphics Support related queries please visit Supportnet:

                https://support.mentor.com/

                 

                Best Regards,

                 

                 

                Charl Peters

                ASIC Design Services

                Tel:            +27 11 315 8316

                Fax:           +27 11 315 1711

                Email:        charl.peters@asic.co.za

                • 5. Re: VB Script to Export Parts list information to Excel.
                  charl.peters

                  Hi Jeff,

                   

                  Thanks for your response. I have tried running this script but keep getting an error "MGCPCB needs to be running!" although xPCB is running with design loaded. I am running VX2.1. Any idea as to what to the cause for this error is?

                   

                  Regards,

                   

                  Charl

                  • 6. Re: VB Script to Export Parts list information to Excel.
                    peter_festesen

                    Hi - I just tried it. AATK has (what seems to be) the same code. Both give the error. I looked at the code and realized that it does NOT have any ProgId in the code inside the xls sheet. In short, ProgId is used to identifi which version of xPCB you are running, and is absolutely needed when you have registered multiple versions at the same time. That was the case in my installation, where I had EEVX.2.1 AND EEVX.2.2 registered, with EEVX.2.2 being registered as the default (Current) version. I ran xPCB from VX.2.1 - and the Automation failed the same way as described above.

                     

                    One solution is NOT to have multiple VX versions registered. I de-registered VX.2.2, leaving only VX.2.1 registered, and the script runs, both from AATK and from 'c:\MentorGraphics\EEVX.2.1\SDD_HOME\standard\examples\pcb\Automation\Excel\'

                     

                    Second solution would be to make sure that the version you are using, is registered as the default - use the Release Switcher. The last registered always becomes the default (askerisk in front of the name in Release Switcher). If I Register VX.2.1 without de-registering VX.2.2 (making VX.2.1 the default (Current), as in '*C:\MentorGraphics\EEVX.2.1' note the askerisk, the Automation works with VX.2.1, but NOT with VX.2.2.

                     

                    The better solution would be to incorporate the use of ProgId in the Excel code, of course.

                     

                    Charl - could you check if you also have multiple versions registered (multiple entries in the top section of the Release Switcher) and if so, make sure that VX.2.1 is the Current default, then run the ExcelComplist again ?

                     

                    /Peter

                    • 7. Re: VB Script to Export Parts list information to Excel.
                      charl.peters

                      Hi Peter,

                       

                      thanks for the feedback. your solution worked. I do however need some edits to produce a different output as per the example below.

                      I do not know if someone can help with this.

                      • 8. Re: VB Script to Export Parts list information to Excel.
                        peter_festesen

                        Hi Charl

                        While the Excel based example in the install tree seems to work, it requires knowlegde of vba (as opposed to vbs) to modify. I do not have that deep vba  knowledge. But I found another piece of Automation that is pure vbs and thus (for me at least) easier to understand and modify.

                        Just like the example from the installation, this one lists per RefDes, so it does not have the format you asked for. It simply scans all 'components', and adds them to the list (if they are placed). As you require a different format, I would probably build a dictionary, using the part Number as the key (as in Patrick's example above)

                        I see in the comments that it came from Toby Rimes originally - over time, I have modified it to include various other properties. In this case I added 'Description'. I just verified it with VX.2.1

                         

                        You could also try to convert Patrick's .net example to standard vbs - it shouldn't be that difficult, I think.

                         

                        Anyway -  here is alternative #1

                        Note the construct in line 204 - 208 - here it finds the property 'Description' and adds it to the grid.  You would have to do that for each of the properties in your example.

                         

                        Have fun !!!

                        • 9. Re: VB Script to Export Parts list information to Excel.
                          peter_festesen

                          One more remark - I noticed that your example is pretty close to what is generated out of 'Output - Bill Of Materials' in xPCB - except that that output is not comma-separated, but looks like it is tab-separated. 

                          • 10. Re: VB Script to Export Parts list information to Excel.
                            jeffheidel

                            That dialog box through me for a loop because I didn't see it on my menu.  I had to enable the Fablink license  before I could see it.  Learned something new today sine I didn't even know that was there

                            • 11. Re: VB Script to Export Parts list information to Excel.
                              peter_festesen

                              Thanks for pointing that out, Jeff - I had not realized that I had the Fablink license active. But you are absolutely right.

                              /Peter