+ Reply to Thread
Results 1 to 9 of 9

Update VBA code in multiple workbooks

  1. #1
    Registered User
    Join Date
    03-04-2011
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Update VBA code in multiple workbooks

    I have a ton of workbooks that cannot be run under Office 2010 because they connect to a MS-Access database and the connection string in each of the workbook references the Jet 4.0 engine.

    What I need is way to scan these workbooks and replace the Jet reference to the newer ACE 12.0 reference. There are just too many to do this one by one. I have tried to figure out how to get to the VBA code programmatically but without success. Anyone have any thoughts or solutions?

  2. #2
    Forum Contributor PingPing's Avatar
    Join Date
    02-19-2010
    Location
    London, England
    MS-Off Ver
    2007
    Posts
    158

    Re: Update VBA code in multiple workbooks

    This is able to be done, but it is not a trivial exercise.
    You will need to create a reference to "Microsoft Visual Basic for Applications Extensibility 5.x" (in Tools > References). This will give you access to the VBA IDE objects (VBComponents, CodeModules, etc).
    Then it's a case of opening up each workbook, one by one, and searching each VBComponent for your string and replacing it.
    I'd recommend also referencing "Microsoft Scripting Runtime" which will give you access to the FileSystemObject which will be useful for looping through files in a directory as well as giving you access to the TextStream object.

    Again, it's not an easy assignment and you'll learn the hard way why it is important that configuration information (eg. a connection string, server name, file path, etc.) is NEVER encoded. You should really use a separate 'Configuration' file (workbook, text file, XML file, whatever - even an SQL Server table) that contains this information and have your application code query this file.

  3. #3
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Update VBA code in multiple workbooks

    This should give you a start.
    Note that the Microsoft Visual Basic for Applications Extensibility library has to be open.
    Please Login or Register  to view this content.
    _
    ...How to Cross-post politely...
    ..Wrap code by selecting the code and clicking the # or read this. Thank you.

  4. #4
    Registered User
    Join Date
    03-04-2011
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Update VBA code in multiple workbooks

    Thanks for the code. This works great as long as the code is within the same workbook. Now what I need to do is to and I want to do this in VB.NET

    * read a folder and create an array of all files with the file type of .xls
    * loop through the arrary opening each workbook referenced
    * execute the code

    I am a VB.NET programmer and getting the VBIDE reference into my application is being a challenge. I have the reference added to my project however, I cannot seem to be able to resolve the reference to the type CodeModule. Any clues for me?

  5. #5
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Update VBA code in multiple workbooks

    You can use syntax like
    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    03-04-2011
    Location
    Richmond, VA
    MS-Off Ver
    Excel 2010
    Posts
    12

    Re: Update VBA code in multiple workbooks

    I tried that but VB.NET throws a conversion error in that it cannot convert "Module2" string to integer. I tried an integer value like 1 and it doesn't seem to find the code module.

  7. #7
    Forum Expert mikerickson's Avatar
    Join Date
    03-30-2007
    Location
    Davis CA
    MS-Off Ver
    Excel 2011
    Posts
    6,229

    Re: Update VBA code in multiple workbooks

    You could loop through all the VBComponents in a project, they each have a code module
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-12-2012
    Location
    West Midlands
    MS-Off Ver
    Excel 2007
    Posts
    1

    Re: Update VBA code in multiple workbooks

    This is really useful but I would like to remove some vba code from multiple workbooks. How could I modify the above?

    The code I wish to remove is as forllows:

    Please Login or Register  to view this content.
    Any help would be greatly appreciated.
    Last edited by arlu1201; 09-14-2012 at 10:26 AM.

  9. #9
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: Update VBA code in multiple workbooks

    Angelamoss,

    Welcome to the Forum, unfortunately:

    Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.

    Also,

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature below this post.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1