How to Replace VBA Code from Excel/Word

Have you ever had a situation where you wanted to replace the existing VBA code from Excel/Word and did not want to open up each file and change it? Well, remember that Excel and Word files are nothing but a zip file. You can easily create a program to replace the file from “zip” file. Here is quick solution with sample executable and source code.

Change the file extension to zip and you will see that under “xl” or “word” folder, you will see “vbaProject.bin” file. It contains all the source code even if you have it password protected. Here are the steps to replace VBA code.

• Update the VBA code in one file.
• Once the code is tested and ready, change the file extension to “.zip” file.
• Double click on zip file and open “xl”(for Excel file) or “word” (for Word file) folder within the zip file.
• Here you will see “vbaProject.bin” file. Extract this file on your location machine.
• You may either use the program that I created (available for download below) or you may write your own program to replace the “vbaProject.bin” from Excel or Word file. Here is quick C# code snippet which requires SharpZipLib library

ZipFile zipFile = new ZipFile(@"Excel or Word file full path");
// Begin updating file
zipFile.BeginUpdate();
// Replace the vba code
zipFile.Add(@"location of your updated vbaProject.bin file", "xl/vbaProject.bin");
// Commit update
zipFile.CommitUpdate();
// Close the zip file
zipFile.Close();

Download Replace VBA Source Code Utility (Visual Studio 2010 Solution).

If you want to only run the program, unzip the files and go to “ReplaceExcelWordCode\ReplaceExcelWordCode\bin\Debug” and double click on “ReplaceExcelWordCode.exe”.