Identify Excel files with specific string in thier VBA module

I need help with a VBA code, to search through all excel files in specified folder and if those excel files contains specific string in thier VBA module, then return their file name in a new excel file.

for example in a folder named   PRODUCT in C drive i am searching more than 25 files with xlsm and xlsb extentions that has macros in them and i am looking for those files that thier VBA code contains  string "5107971177"

any help is appreciated.
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Is this what you need?

a) Looping through the combined file specification of "C:\PRODUCT\*.xlsm" & "C:\PRODUCT\*.xlsb", opening each matching workbook file in turn, & then searching throughout all the VBA code (modules) returning the filename of the workbook that contains the string ("5107971177") to a summarised list?


Have you already exported all the code modules to (ASCII) text files, & hence...

b) Searching through the previously exported code modules from the same set of files that have been saved locally for the same string?

Are any of the workbooks password-protected (at the file level &/or the VBA Project[s])?
excelismagicAuthor Commented:
thanks Fanpages for looking into this.

you got this right it is the a)

a) Looping through the combined file specification of "C:\PRODUCT\*.xlsm" & "C:\PRODUCT\*.xlsb", opening each matching workbook file in turn, & then searching throughout all the VBA code (modules) returning the filename of the workbook that contains the string ("5107971177") to a summarised list?
excelismagicAuthor Commented:
Dear Fanpages,
just to answer your last comment,

none of these workbooks or worksheets are protected.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

[ fanpages ]IT Services ConsultantCommented:

Thanks for the reminder your question was still here! :)

I do intend to look at this later today (Wednesday).  Hopefully the delay has not caused you too much inconvenience.

Just for clarity to make sure I address your requirements; what would you like within the summarised list?

Folder/Filename of the matching workbook file, the name of the module, & the line where the string was found, or simply just the folder/filename details?

Thanks again for your patience.
excelismagicAuthor Commented:
thanks Fanpages.

if possible i would like   FolderName/FileName of workbook  file and module

if it is too complicated then  only Folder/Filename of the matching workbook file

thanks alot
[ fanpages ]IT Services ConsultantCommented:

Sorry, I have not forgotten about you.

I will endeavour to find time to get back to addressing your question within the next couple of hours.
If you pass this routine the path where your workbook files are located and the string you want to find, it should populate the active worksheet with the names of the workbooks and the modules where that string can be found.
Sub Q_28702996(ByVal parmPath As String, ByVal parmFind As String)
    Dim strFilename As String
    Dim strPath As String
    Dim wkb As Workbook
    Dim lngLoop As Long
    Dim lngLineCount As Long
    Dim rngTgt As Range
    strPath = parmPath
    If Right(strPath, 1) <> "\" Then
        strPath = strPath & "\"
    End If
    Set rngTgt = ActiveSheet.Cells(1, 1)
    strFilename = Dir(strPath & "*.xls?")
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Do Until Len(strFilename) = 0
        If LCase(strFilename) Like "*.xls[mb]" Then
            Set wkb = Workbooks.Open(strPath & strFilename, False, True)
            For lngLoop = 1 To wkb.VBProject.VBComponents.Count
                lngLineCount = wkb.VBProject.VBComponents(lngLoop).CodeModule.CountOfLines
                If lngLineCount <> 0 Then
                    If InStr(1, wkb.VBProject.VBComponents(lngLoop).CodeModule.Lines(1, lngLineCount), parmFind, vbTextCompare) Then
                        rngTgt.Value = wkb.Name
                        rngTgt.Offset(0, 1).Value = wkb.VBProject.VBComponents(lngLoop).Name
                        Set rngTgt = rngTgt.Offset(1)
                    End If
                End If
            Application.StatusBar = wkb.Name
        End If
        strFilename = Dir()
    Application.DisplayAlerts = True
    Application.StatusBar = vbNullString
    Application.ScreenUpdating = True
End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
[ fanpages ]IT Services ConsultantCommented:

Looks like I don't need to now.

Oh well.  I will stick my solution aside for use at a later date!
A couple of things I encountered while testing:
* if you have password protected code, the routine will encounter an error
* if the workbook has code that automatically updates content, or has links that may automatically update the content, you may need to add a False parameter to the wkb.Close statement.
* you may have workbooks that behave differently than those on my hard drive, causing errors that I did not encounter.
excelismagicAuthor Commented:
thanks very much aikimark.

excuse my ignorance but i do not know how to pass this to another macro.

i paste this in the module and try to run it but it did not run, i assume, i need to run this from another macro.

path where my workbook files are located C:\CONTENTS\EXCELISMAGIC

string i want to find is   "5107971177"
1. Go to your code environment (Alt+F11)
2. Go to the Immediate Window (Ctrl+G)
3. type the following and press enter
Q_28702996 "C:\CONTENTS\EXCELISMAGIC", "5107971177"

Open in new window

If it does what you need, you can put that line into another sub/function routine.

how to pass this to another macro
Not sure what you want to do.  The code updates the active worksheet.
excelismagicAuthor Commented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.