7 Replies Latest reply on Jul 22, 2016 2:54 PM by pthivierge

    How to remove VBA Code, Modules and References from a PI ProcessBook file programatically?

    BrentAWebster

      Good afternoon all,

       

      I have multiple modules to create multi state objects based on an excel spreadsheet.

      There is one sheet per module per display.  The multistates are created for up to 50 different objects with up to 40 states.

       

      The modules work great on their own.  I just want to either attach reference and modules programatically and remove when done or create some sort of addin that has the excel reference and code all avalible

       

      Has anyone programatically added a reference to excel and imported modules then deleted modules and the reference to excel

       

      Message was edited by: Patrice Thivierge - Changed the question title from "multiple modules for creating multi state objects" to "How to remove VBA Code, Modules and References from a PI Processbook file programatically?".  To make it easier to find later on. The question should also be: How to generate PI ProcessBook files programmatically and remove all code content from the generated file?

        • Re: multiple modules for creating multi state objects
          pthivierge

          Hello Brent,

           

          I am uncertain that I understands properly.  What I understand is that you have created VBA Modules in PI Processbook that creates multistates objects on a display.  The multistates creation is based on data provided in a spreadsheet.

          You would like to add a VBA reference dynamically ( I am not sure if this is what you meant). And also dynamically insert code.  And from this I understand that you would like to automate the creation of your PI Processbook files without leaving the code inside the PI Processbook displays. Is this correct?

           

          If this is correct, I'd propose another approch than an addin, because I believe the usage of this code will be used always in the same way and does not require to be accessible after its development.

           

          Proposed Methodology

          • Create a .PDI file that will serve as a template.  It will contain all the code necessary to generate your displays.
          • When you need to create a new display you create a copy of the template.
          • Then you configure (or not) the name of the input file required. ( I would probably make the code peek the configuration file from the same directory, and keep the config file name always the same.)
            • Directory example
              • config.txt
              • PDITemplate.pdi
          • When you open PDITemplate.pdi, you click a button that lauches the display creation process based on the configuration file.
          • At the end of the script, everything related to code and button will be removed.

           

          I made a small example attached to this post. And here is the code it contains, the main logic is inside the generatePDI sub.

          You will also need to adapt the RemoveReferences part, and add a line for each reference you need to remove.  You may use the PrintReferences method to show the name of your currently references libraries.  This is the only way I found to get the name that can be passed tp the Remove method.

          Warning: this is the type of code that does not forgive.  Make sure you always work on a copy of your current code, because when you execute this code, your code will be deleted!

           

           

          Public Sub generatePDI()
          
          ' creates all multi states objects
          ' ...
          
          ' remove the code
              RemoveCode
          
              ' remove the references
              RemoveReferences
          
          
          
          
              ' removes the button we named Button1 - button is only visible in script when enable scripting option is enabled on the object in design mode.
              ThisDisplay.Symbols.Remove "Button1"
          
          
              ' save the PDI - use save or save as as needed
              ThisDisplay.Save
          
          
              MsgBox "Display creation completed and saved"
          
          End Sub
          
          Public Sub RemoveCode()
          
          ' get the instance of the VBEditor
              Set Editor = Application.VBE
          
              Dim iType As Variant
          
              ' goes over all Classes, Modules or Forms in the Active VB Project
              For i = Editor.ActiveVBProject.VBComponents.Count To 1 Step -1
                  iType = Editor.ActiveVBProject.VBComponents(i).Type
          
                  If iType = 1 Or iType = 2 Or iType = 3 Then    ' 1=Module, 2=class module, 3=Microsoft Form, 11=active X designer, 100=document module.
                      ' remove the Module, class or form
                      Call Editor.ActiveVBProject.VBComponents.Remove(Editor.ActiveVBProject.VBComponents(i))
                  End If
          
              Next i
          
          End Sub
          
          Public Sub RemoveReferences()
          
              Set ReferencesObj = Application.VBE.ActiveVBProject.References
          
              ' removes reference to excel
              Call ReferencesObj.Remove(ReferencesObj("Excel"))
          
          End Sub
          
          ' use this method to print the name of the currently selected references in the immediate window.
          ' then use the name as the key in the remove routine.
          Public Sub PrintReferences()
          
              For Each reference In Application.VBE.ActiveVBProject.References
                  Debug.Print reference.Name, reference.GUID, reference.fullpath
              Next reference
          End Sub
          

           

           

          Let me know if this helps!

          2 of 2 people found this helpful