0

I have created an Excel workbook with lots of code behind the scenes. Occasionally I need to update the coding. Typically, I create a new version of the workbook and save it to each individual's personal network drive. In a perfect world, they would follow simple instructions for downloading new and delete the old version. But of course, that doesn't happen. Plus my staff is growing so copying these individual workbooks for each team member is becoming cumbersome.

Is there a way that I could create a program that would update the coding in the workbook? Then they should be able to run this patch program to update the coding within their workbook.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
exceldude2001
  • 141
  • 2
  • 2
  • 13
  • 3
    Save it into one shared location. – 0m3r Feb 18 '19 at 16:31
  • 1
    Or convert to a internal webapp. – JGFMK Feb 18 '19 at 16:41
  • Do your employees use this workbook as a template and save copies, or do they make changes directly to the file and save it as is? At any rate, I agree with JGFMK, it is probably better to make this an Access Database or an internal SQL-backed web application, if you have the means. – TylerH Feb 18 '19 at 18:45

1 Answers1

0

For Excel 2010, in the Trust Center, you can opt to allow programmatic access to the VBA object model by clicking the option "Trust access to the VBA project object model", the only option under Macro Settings>Developer Macro Settings. (A risk that needs to be fully undestood and if you are operating in a controlled IT environment and may run counter to your organization's security policies!)

Having done that, you can create a macro-enabled workbook "patcher" to open the target macro-enabled workbook. Then, for example, if you were to operate at the module level, the "patcher" application might to the following to remove the old code:

yourWorkbook.VBProject.VBComponents.Remove modYourModule

and then update it with new code by importing a replacement module:

yourWorkbook.VBProject.VBComponents.Import "c:\Users\Public\yoursource.bas"

Hope this helps point you in the direction you are looking for!

geoff cahill
  • 31
  • 5