7 Replies Latest reply on May 25, 2017 11:11 PM by Patrick.Cashman

    How to run "Run Defined Query" Using VBScript

    karthikeyanarunachalam

      Hi,

      I'm working on output automation . I have able to open the Report writer using VB script but Couldn't able to identify how to run the "Run Defined Query" (We are having few defined query in Report writer) .  Please help to identify how to run the "Run Defined Query" under Query Tab in Report writer.

       

      I'm using the below VBscript to open the Report writer .

       

      Option Explicit


      Sub  Execute_ReportWriter  ()

      Dim resultMess
      Dim retBool,diaObj,Exiting
      Dim butObj, CloseObj

      retBool = app.Gui.ProcessCommand("Output->Report Writer",True)
      If (retBool = False) Then
      Call app.Gui.StatusBarText("HKP Export " + _
                                   Err.Description,epcbStatusFieldError)
        Exit Sub
      End If

      ' Find the dialog
      Set diaObj = app.Gui.FindDialog("Report Writer")
      If (Err Or (diaObj Is Nothing)) Then
        Call app.Gui.StatusBarText("Report Writer Generation not found " + _
                                   Err.Description,epcbStatusFieldError)
        Exit Sub
      End If

      ' Find the process button
      Set butObj = diaObj.FindButton("OK")
      If (butObj Is Nothing) Then
        Call app.Gui.StatusBarText("Report Writer Generation- Process button not found " + _
                                   Err.Description,epcbStatusFieldError)
        Exit Sub
      End if

      End Sub

      Dim app
      Set app = GetObject(,"MGCPCB.Application")
      Call Execute_ReportWriter

        • 1. Re: How to run "Run Defined Query" Using VBScript
          Patrick.Cashman

          I know this is not the direct answer to your question, but I suggest you use other methods to get information from your database directly.  I have not yet encountered any bit of data I wanted from the PCB design which could not be obtained with other automation functions. 

           

          If you can describe specifically what you are trying to get, perhaps we can help you find a faster and easier way that does not involve jumping through the hoops of using Report Writer.

          • 2. Re: How to run "Run Defined Query" Using VBScript
            guyjj

            I am also looking for a simialr script. 

            I want to open report writer, run a defined querry and output the results to Excel.

            The query is to extract a BOM. The results are exported to Excel where I have a macro to sort and format data.

            The idea is for a "one button" extract and BOM creation

            • 3. Re: How to run "Run Defined Query" Using VBScript
              Patrick.Cashman

              There is nothing in the Infohub help files under automation about Report Writer.  The help in Report Writer appears to be in an outdated format, so I can't review it.

               

              If you guys are really set on doing it this way, you can explore UI Automation. I don't recommend it as it's the very long way around, but when there is no other option it can work.

               

              There are other drawbacks to using Report Writer:

               

              1. It creates a huge pile of data in the PCB/Output folder.

              2. The date exported for Report Writer is static. It does not keep up with the current state of the design.  If you forget to recreate the output after making changes to the database, the data no longer matches the design.  This is a very good way to introduce errors into your output.

               

              But if you really want to, and can't get to the internal functionality of it, there's another option. Once the data has been created by the Output > Report Writer function, you can access all the files directly to get what you want.  I believe there is an MS Access format database file in there, as well as a bunch of text files.  You could use data access tools in .net or just read the text files and get what you need from them.  Everything Report Writer creates is included in those files. Hunt around in there and I bet you can find what you're after.

               

              We create BOMs from the Schematic.  The reason for this is that it's possible to create schematic symbols for parts that do not become part of the layout, but which are part of the PCB Assembly.  Such as mounting hardware - screws, nuts, heat sinks, things like that.  In order to be included on a BOM made from the PCB, these objects have to be in the PCB design somewhere.  Then do you put them on the board or not?  It's a clumsy way to do it.  The method we use of putting the parts on the schematic, setting them to not annotate to the PCB, and making the BOM from the schematic is very clean and trouble-free.  We make the reference designator of the schematic component the item number on the BOM, and add a property for quantity.  I have a BOM creation script that runs against the schematic which collects these parts and their appropriate properties and includes them in the BOM output. 

               

              Our BOMs are perhaps more complicated than most.  We separate SMT components from everything else, so we have 2 BOM files - SMT BOM and what we call the Assembly BOM.  All the thru-hole components, hardware, and anything else that's not an SMT part goes on the Assy BOM.  This way the SMT assembly equipment setup does not have to sort out the non-SMT parts.  We use an SMT/TH property in the parts database to help with this sorting. Finally, we have to format the BOM files for importing into SAP, which is a very particular format not supported by any BOM output Mentor is capable of generating.

               

              Throw all this together and it has made the most sense to make our own BOM export tool, and run it from the Schematic.  We've been doing it this way for years now with good success.

               

              I know I'm not answering your question directly, but I hope I'm giving you some useful information an possibly a couple of alternative ways of moving ahead.

              • 4. Re: How to run "Run Defined Query" Using VBScript
                karthikeyanarunachalam

                Hi Patrick,

                Mike requirement is as below.

                I want to generate a "connlst.rpc" file in report writer and two customer report one is for Pick and place and one more report . I have attached   the snapshot of the report files

                3_TIER_COMP_FILE.PNG

                PICKandplace.PNG

                 

                Is something you can help in VBS to generate these reports with or without report writer

                • 5. Re: How to run "Run Defined Query" Using VBScript
                  Patrick.Cashman

                  I don't know what the connlst.rpc file looks like, but the other two shown in your images are both very easy to create directly from an open Expedition PCB session.

                   

                  If you can email me a copy of the connlst.rpc file, I'll take a look at what it would take to re-create it.  I suspect it will not be difficult.  Send an email to patcashman@gmail.com, and I'll see.

                   

                  As for the other two, there are a couple of considerations to work out -

                  1. Do you want the output units for pick and place locations in the design units or always the same (th, mm, or inches) regardless of design units?

                  2. Some parts in the PCB may not have reference designators - such as ID and part number cells, ESD marks, company logos, and so on.  Do you want to only include packaged parts with reference designators or all parts?

                  3. For the mount type in the first report - how to describe the through-hole parts - TH?

                   

                  I will have some time later today to make example scripts for the above.  You'll see that they are very simple and straightforward, and ultimately require a lot less effort than going through the report writer.

                   

                  More to follow.

                  • 6. Re: How to run "Run Defined Query" Using VBScript
                    guyjj

                    I  found a script that partially does what i want without using report writer ( Expedition PCB: Bill Of Materials - Just another BOM script .) It outputs a file with Part Number and Reference desigator.  I need to add Description, Part Number, Part Name, Property Name, Property Value but don't what they are called to add them to the script. Is there a mentor script command list?

                    • 7. Re: How to run "Run Defined Query" Using VBScript
                      Patrick.Cashman

                      Sorry it's taken me so long to respond again.  I've been slammed at work with a board that had to get out. 

                       

                      So here are some example scripts.  Take note - these are done in vb.net.  I know you asked for vbscript.  With a small bit of effort, these could be translated to vbscript.  There are only minor differences you should be able to do yourself by looking at the example scripts that come with Xpedition and at the AATK scripts. 

                       

                      We assume that you have already in place the routines to connect to the Xpedition session and get the reference to the doc variable (MGCPCB Document).  If you do not know how to do that, you can get my automation template here.  It has startup routines for all the Mentor PCB Systems flow tools, currently at version VX.1.2.  If you do not know how to use vb.net, get the SharpDevelop IDE, get my template, copy the template with a new name, and open the solution (.sln) file.  For the basics of how to use the IDE, follow this hello world tutorial.

                       

                      Now that all that's out of the way, here's the code

                       

                      Sub create_3tier_comp_file()

                               Dim comps As MGCPCB.Components

                               Dim comp As MGCPCB.Component

                               Dim str As New StringBuilder

                               Dim refdes, pn, mnt_type, side As String

                              

                               str.AppendLine("RefDesignator" & vbTab & "PartNumber" & vbTab & "MountTYPE" & vbTab & "Side")

                               comps = doc.Components(0, -1, 4)    '0 = all components, -1 = all types, 4 = packaged cells only

                               For Each comp In comps

                                   If comp.RefDes = "" Or comp.RefDes = " " Then GoTo next_comp

                                   refdes = comp.RefDes

                                   pn = comp.PartNumber

                                   If comp.IsSMD Then

                                       mnt_type = "SMD"

                                   Else

                                       mnt_type = "TH"

                                   End If

                                   If comp.Side = 1 Then

                                       side = "TOP"

                                   Else

                                       side = "BOTTOM"

                                   End If

                                  

                                   str.AppendLine(refdes & vbTab & pn & vbTab & mnt_type & vbTab &  side)

                                  

                                   next_comp:

                               Next

                               set_filename = 1

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

                              

                           End Sub

                          

                           Sub create_comp_file()

                               Dim comps As MGCPCB.Components

                               Dim comp As MGCPCB.Component

                               Dim str As New StringBuilder

                               Dim refdes, pn, cell, side As String

                               Dim x, y, r As Double

                              

                               str.AppendLine("RefDesignator" & vbTab & "PartNumber" & vbTab & "TopCELL" & vbTab & "X" & vbTab & "Y" & vbTab & "Side" & vbtab & "Rot")

                               comps = doc.Components(0, -1, 4)    '0 = all components, -1 = all types, 4 = packaged cells only

                               For Each comp In comps

                                   If comp.RefDes = "" Or comp.RefDes = " " Then GoTo next_comp

                                   refdes = comp.RefDes

                                   pn = comp.PartNumber

                                   cell = comp.CellName

                                  

                                   x = comp.PositionX

                                   y = comp.PositionY

                                   r = comp.Orientation

                                  

                                   If comp.Side = 1 Then

                                       side = "TOP"

                                   Else

                                       side = "BOTTOM"

                                   End If

                                  

                                   str.AppendLine(refdes & vbTab & pn & vbTab & cell & vbTab &  x & vbTab & y & vbTab & side & vbTab & r)

                                  

                                   next_comp:

                               Next

                               set_filename = 1

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

                              

                      End Sub

                       

                      The save file routine I use looks like this:

                       

                      Function save_file(tmp as String, title as string, filename as String) as Boolean

                                  

                               'On Error GoTo finish:

                              

                               if filename = "" then

                                   If set_filename = 1 Then

                                       dlg_save.Title = title

                                       if dlg_save.ShowDialog() = dialogresult.OK then

                                           filename = dlg_save.FileName

                                       End If

                                   Else

                                       filename  = "new_" & filename

                                       filename = path & "\" & filename

                                   End If

                               end if

                              

                               if file.Exists(filename) then

                                   file.Delete(filename)

                               End If

                              

                               Using sw As StreamWriter = New StreamWriter(filename, False)

                               sw.Write(tmp)

                               sw.Close

                               End Using

                              

                               set_filename = 0

                              

                               finish:

                               return true

                                  

                      End Function

                      In order to use that, add a SaveFileDialog to the Design area (tab at the bottom of the main window) of the Main_Form, and name it dlg_save.  There is a yellow area at the bottom - just drag the SaveFileDialog thing from the tools pallet under Windows_Forms into that yellow area and it gets added to the form as a usable background object.  You don't see it on the form, but it's available for the form to use by any subroutine or function.

                       

                      This should get you a long way down the road to getting started with automation.  I hope you find it helpful  for many automation projects in the future.