Searching VBA Projects

Posted on by Dick Kusleika

A lot of the work I do in Excel uses external data that’s linked to the accounting system. When I create a query, I save the username and password so I don’t have to type it in for every refresh. It’s not the most secure setup, but the convenience makes it worth the risk.

I need a way to find all of the external data tables in all the workbooks and change the username and password. That way, when they delete my username from the accounting system, they won’t be getting errors that nobody will understand. So I changed my password in Timberline (accounting software), which raises an error while I’m still here instead of after I’ve left. Whenever the error is raised, I put the owner’s uid and pword because he won’t quit and he can’t be fired. That seemed like a good plan, but there’s a catch.

I have some VBA that uses ADO (ActiveX Data Objects) to access the databases. It’s the same principal as the external data tables, but it doesn’t produce the same error. Oh, it produces an error all right, but it doesn’t present the user with the easy fix like external data tables do. The uid and pword are hardcoded in the VBA and need to be changed there.

My quest, therefore, is to track down every workbook that has a macro with the text “uid=dick” in it. I know there are plenty out there, but I don’t know how to find them. Windows Search turns up nothing. Copernic desktop also finds nothing. Does anyone know how I can identify these files?

As an aside, I wonder if the new XML format will make this kind of thing easier?

Posted in Uncategorized

22 thoughts on “Searching VBA Projects”

  1. Hi Dick,

    I’m probably not going to help you with your immediate problem, but since I had something similar a few years back I nowadays always use Integrated Security approach to connecting to MS SQL Server. This avoids the need for hardcoded passwords in my VBA nowadays.

    However, the drawback is that the user running the code needs to have DB access/account – but it sure does get around the issue (for SQL Server).

  2. Hi Dick
    I agree with WillR on the integrated security, safer as well as easy to maintain.
    On the VBA front, bad news I think. The VBA part of .xls files is not documented and I don’t think any indexing services can index them. It looks like this may continue to be the case in Excel 12 too.
    There is open source c code floating around if you want to get technical, otherwise I think you’ll have to open every wb in excel and check the VBA project, if there is one.
    I’d do it in 2 passes, once read only on all the files to find the ones to change (prob in groups of 500, then restart excel). Then read/write just on those that need changing (may do this manually rather than bother writing and testing the code, if there aren’t too many!). Ideally you want to do it without triggering wb_open and auto_open code.
    (you’ll want to borrow someone else machine to do this, especially if its over a network)
    Someone else may have a slicker approach?

    cheers
    Simon

  3. Don’t know about XML, but another way to make settings like these searchable through files is to keep them in a hidden worksheet. The tools that can search through worksheets without opening files exist I believe.

    And using the integrated security is preferrable if possible, I agree.

  4. >As an aside, I wonder if the new XML format will make this kind of thing easier?

    Nope, the VBA part of the workbook is not XML; it retains its old binary format.

    MZ Tools lets you search all open and unlocked VBA projects for a text string. It’s not as easy as searching a directory of closed files, but it beats doing it one-by-one.

    – Jon

  5. Hi,

    I hope some of this helps.

    The text “uid=dick” will not exist as a text string in any excel file. VBA seems to tokenise things before they are stored. What you will find is “uid” and “dick”. Also “dim uid” as long as it is at module level and not Procedure level.

    To find files that contain “dick” or whatever your password is/was you could use the following at the command prompt or within a batch file….remember those!
    Findstr /I /M /C:”dick” C:whatever directory*.xls >>Match.txt

    The file Match.txt will contain a list of files containing your search string.

    Regards
    Bob Bridges

  6. Perhaps you’ll like to write an add-in that will automatically open files, iterate through VBA modules, and search for the string :)

  8. Nope, the VBA part of the workbook is not XML; it retains its old binary format.

    Yes, I remeber reading about this too. I thought at the time what a pisser it was! I guess it also means that UFD’s are out of the picture for xml workbooks.

    I’ll be checking out “Agent Ransack”!

  11. hi rob…

    i ran your macro and it opened and read all files in the specified directory and said at the end:

    error: type mismatch to every one of them…

    please advise…

    thank you very much…

    wolfgang

  13. Wolfgang,

    Remove the line “On Error GoTo errwkb”
    That will cause the code to break on error, identifying the faulty line of code.

    Also, doublecheck you have a Reference to “Microsoft Visual Basic for Applications Extensibility”
    From VBA’s Tools > References menu.

  14. If you are searching for text in a VBA module could you modify and enhance Chip Pearson’s macro that “exports all of the modules in a workbook to text files.”?
    Loop thru all xls files in a directory, export the VBA to text files, and then search the text files? (maybe import to an Excel sheet then search)

    http://www.cpearson.com/excel/vbe.htm

  15. Well i think that the Agent Ransack program wil be the fasted and easiest way for dick to go. It realy is very good, Ive used it twice already, and it run perfectly from my usb pen, which is also very cool

  16. hi rob…

    thank you very much for your answers…

    i did as you suggested and the code breaks at:
    For Each vbc In wkb.VBProject.VBComponents

    with run-time error ’13’ type mismatch…

    i also checked the references …

    i run on xp-pro and office 2003, both us-versions…

    best,
    wolfgang

  22. @rob…

    i looked at it approx. 500 times and could have sworn any oath that i set the reference right…

    ..which di’dn’t, of course…

    i had:’Microsoft Visual Basic 6.0 Extensibility’
    instead of
    ‘Microsoft Visual Basic for Applications Extensibility’

    thanks to your patience and friendly help i managed to correct it without any additional support needed…

    poooooooooohhhhhh…

    apologies for not being able to see the forest before the trees, rob

    thank you very much again for your time…

    best,
    wolfgang

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.