3

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.

Community
  • 1
  • 1
trailmax
  • 34,305
  • 22
  • 140
  • 234
  • 2
    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: http://stackoverflow.com/questions/14491613/can-i-evaluate-an-excel-vb-constant-that-is-in-string-format/14492230#14492230 – mkingston Jan 25 '13 at 01:11
  • that's a great starting point! thanks! – trailmax Jan 25 '13 at 01:13
  • 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 Jan 25 '13 at 01:15
  • 2
    http://www.cpearson.com/excel/vbe.aspx is a useful resource for this topic – Tim Williams Jan 25 '13 at 01:21

1 Answers1

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();
Brett Wolfington
  • 6,587
  • 4
  • 32
  • 51
  • 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. – trailmax Jan 25 '13 at 02:44
  • 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. – Brett Wolfington Jan 25 '13 at 21:45