Change macros text in Excel programmatically

Question

I've got about thousand of Excel (xls, old format) files spread out across many folders on the hard-drive. These files have the same macros that connects to a database. And the macros contains connection string. Macros is password protected, but luckily I know the password.

Question: what is the best way to change the connection string in macros inside all the files?

I have experience of working with NPOI to create/modify xls files. I have seen Interop libraries unlocking password-protected Word files and doing some editing. But I have never seen examples of programmatically changing of the macros text inside Excel file. Is that even possible?

p.s. I don't have problems writing code. The problem is choosing the right tools.

I won't enter this as an answer, because I don't really have the time to put in the work to give you quality, but if you read my answer here it might give you some ideas: stackoverflow.com/questions/14491613/… — mkingston
– mkingston, Commented Jan 25, 2013 at 1:11
No prob, good luck :). Tag me in a comment on here if you get really stuck and I'll have a crack at it when I get a chance. Sounds like you'll probably get it yourself though! — mkingston
– mkingston, Commented Jan 25, 2013 at 1:15
cpearson.com/excel/vbe.aspx is a useful resource for this topic — Tim Williams
– Tim Williams, Commented Jan 25, 2013 at 1:21

Brett Wolfington · Accepted Answer · 2013-01-25 02:19:10Z

4

You might want use the following code as a starting point. This code uses COM Interop to extract the VBA script and perform a find-replace. I tried this out on a password-protected spreadsheet with a very basic script and it worked well. It is, admittedly, basic, but you may be able to extract what you need.

string filename = "Test.xls";
string password = "password";

Excel._Application app = new Excel.Application();
Excel._Workbook workbook = app.Workbooks.Open(Filename: filename, Password: password);

if (workbook.HasVBProject)
{
    VBProject project = workbook.VBProject;

    foreach (VBComponent component in project.VBComponents)
    {
        if (component.Type == vbext_ComponentType.vbext_ct_StdModule ||
            component.Type == vbext_ComponentType.vbext_ct_ClassModule)
        {
            CodeModule module = component.CodeModule;

            string[] lines =
                module.get_Lines(1, module.CountOfLines).Split(
                    new string[] { "\r\n" },
                    StringSplitOptions.RemoveEmptyEntries);

            for (int i = 0; i < lines.Length; i++)
            {
                if (lines[i].Contains("A1"))
                {
                    lines[i] = lines[i].Replace("A1", "D1");
                    module.ReplaceLine(i + 1, lines[i]);
                }
            }
        }
    }
}

workbook.Save();
workbook.Close();
app.Quit();

answered Jan 25, 2013 at 2:19

Brett Wolfington

6,6374 gold badges35 silver badges52 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

trailmax Over a year ago

Thanks for the code Brett. I kinda got almost to the same point myself. The trouble now, as soon as I try to access project.VBComponents, I get exception "Can't perform operation since the project is protected.". The VBA is protected, not the xls file. And these are different. Currently researching the workarounds of these issues. Application.SendKeys() seems to be the only possible solution.

Brett Wolfington Over a year ago

SendKeys() looks to be a potentially doable solution. You might also want to try a third-party macro program. AutoHotKey has been a tremendous productivity booster for me, and I have little doubt that it could be used to effectively automate this process.

Collectives™ on Stack Overflow

Change macros text in Excel programmatically

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related