VB and VBA Users Source Code: Modifying Workbook VBProjects programmatically (from code)
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Modifying Workbook VBProjects programmatically (from code)
By:
Philip Livingstone
Email (spam proof):
Email the originator of this post
Date:
Monday, October 01, 2001
Hits:
1724
Category:
VBA (Visual Basic for Applications)
Article:
Where I work, we have several thousand workbooks, all with VBA Macro/Code projects that from time to time have to have VB Code inserted, or have code deleted /replaced, updated etc... This was a mammoth task for the poor guy who had to actually do it...so I started looking at ways of automating this procedure... In the Application Object we have the VBE, which is how we can refer to the Visual Basic Editor from code. The Workbook object has an object called the VBProject, which we can use to reference all the objects, collections, and components within that (macro) project. The VBE itself contains an object called 'ActiveVBProject' which enables us to reference all the objects and collections and methods of the VBProject that is currently active. So, we have the background to get started. Say you need to insert a (generic) module of code to carry out a specific task in all workbooks for a certain department when those workbooks are saved by the users. (I ought to point out here that if the VBProjects are password protected, they'll have to be <I><B>individually</I></B> unprotected before you can update them...and there is <I><B>no way</I></B> to do this from code!) The first step is to insert the generic code module, deleting any existing one of the same name first (so we can replace it!) - and this code will do that, assuming the 'Workbook' variable references an open workbook, and 'sModule' is the name of the module to be replaced using the file given in sFileName! NB: sWorkbook should be loaded already! sFileName should be the filename of a Visual Basic Module (*.bas) including the path where it is... >>> Sub ReplaceModule(byVal sWorkbook as String, _ byVal sModule as String, sFileName as String) Dim v_VBComponent As Object On Error Resume Next ' just for testing...! ' delete the old module if it exists... Set v_VBComponent = Workbooks(sWorkBook).VBProject.VBComponents(sModule) If Not v_VBComponent Is Nothing Then ' it exists already, so remove it... Workbooks(sWorkBook).VBProject.VBComponents.Remove v_VBComponent End If ' now insert the new one... Workbooks(sWorkBook).VBProject.VBComponents.Import sFileName End Sub <<<< so what is going on here? First, we try to set a reference to an existing component of the name sModule. If it exists, we can use this reference to remove it from the VBComponents collection of the Workbooks VBProject by using the Remove method of the collection...we have used 'On Error Resume Next' in case it doesn't already exist in the VBProject... Next, we use the Import method of the VBComponents collection to import the new module into the VBProject of the workbook referenced by sWorkbook... ...so that's it, obviously error handling is needed, perhaps to test for whether the VBProject is password protected. You can test this by adding the Visual Basic for Applications Extensibility object-library to your project and testing for a property of the VBProject object... but this is something I hope to cover in a future article for VBUsers.Com, along with many other methods and properties of the VBProject object, the aim being eventually to build a generic project that you can use to update the code in your Excel macro projects programmatically. I hope this is helpful, and will hopefully be including another article soon. Philip Livingstone mailto:plivingstone@statestreet.com
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
