Finding Workbooks Containing Macros

Hi Brian, I too would be interested in seeing your workbooks. I recently tried using MZ Tools to analyse a workbook. Brilliant piece of kit but it seemed to make Excel a little unstable so I've taken it down for the moment.

Please contact me at Trevor at "excelaid dot co dot uk"

Thanks

Hi Peter,

The location of the various pieces of code that make up this procedure is important, so I will send you a complete workbook that does everything I mentioned.

I used the workbook by putting it into my XLSTART directory alongside Personal.xlsb so that it was automatically loaded each time Excel was started.

Much of the code and concepts is borrowed from various questions I asked in several Excel forums, and the final structure is derived from John Walkenbach's "Excel 2010 Power Programming" wthout which I would probably have got nowhere!

Expect the workbook email shortly.

Hi Brian,

I would be interested in seeing these files (or just the code it self)

Regards
Peter

Email Peter_Atherton AT hotmail.com

For my own personal interest, I have worked on this problem for the two weeks since it appeared and I finished my solution only yesterday evening.

Unfortunately it looks as if I misinterpreted the problem!

My solution is, in fact, a way to display the number of macros in every workbook when it is OPENED. It is derived from an earlier procedure that displays all the macros in any of the open workbooks and referenced addins. It includes SUBs and FUNCTIONs that Excel would not normally list.
If any reader is interested, I would gladly pass on either of the workbooks that contain these procedures.

Hi Trevor

The problems I found were, the msgbox only gave me about 90 files before it ran out of lines, and if the macro is in a saved file then the code closes it before completion.

I made the changes you suggested and copied the code into a new unsaved book and ran it from there. The only remaining problem is that it picked up a gif and a txt file. here the edited code.

Option Explicit

Sub FindMacros()
'xltips Jacques Raubenheimer, Roger Mason, Leonard Gordon, _
and Brandon Moxley
Dim sPath As String
Dim sFile As String
Dim sFoundFiles As String
Dim i As Long, sFType As String
Dim counter As Long
Dim sTime As Double, eTime As Double

' specify directory to use - must end in "" _
change to suit
sPath = "C:Documents and SettingsPeterMy Documents"

With Application
.DisplayAlerts = False
.EnableEvents = False
.ScreenUpdating = False
End With


sFile = Dir(sPath)
counter = 1

On Error Resume Next

sTime = Timer
Do While sFile <> ""
i = i + 1

bHasMacros = False
If InStr(sFile, ".xls") > 0 Then
Workbooks.Open (sPath & sFile)
If Workbooks(sFile).HasVBProject Then
bHasMacros = True
counter = counter + 1
If counter Mod 3 = 0 Then
sFoundFiles = sFoundFiles & sFile & vbCrLf
Else
sFoundFiles = sFoundFiles & "," & vbTab & sFile
End If
End If
Workbooks(sFile).Close (False)

End If
' Write this in your new book
Cells(counter, 2).Value = sFile
sFile = Dir ' Get next filename
Loop

If Len(sFoundFiles) = 0 Then
MsgBox "No workbooks found that contain macros"
Else
sFoundFiles = "The following workbooks contain macros:" & _
vbCrLf & vbCrLf & sFoundFiles
MsgBox sFoundFiles, vbInformation, counter & " files with macros found"
End If

With Application
.DisplayAlerts = True
.EnableEvents = True
End With

End Sub

Oops, heh heh, senior moment ... hadn't actually looked at the order of the posts.

Maybe you should just delete the last post and this one ...

Interesting that the time looks as though it's going backwards ...

Update: I also had a few problems with it not being happy with some workbooks ... not investigated why yet. This can be "glossed over" with On Error Resume Next but this might miss some workbooks out of the list. There was also a file with a "password to open" which paused the execution (and, off the top of my head, I couldn't remember the password).

And, unfortunately, the MshBox, as anticipated only copes with about 32-33 file names ... and I did have, literally, hundreds.

As the macro has to "live" somewhere, I'd suggest that it lists the file names in a sheet in the macro's home workbook and any issues could be highlighted in the next column.

But, for all it might look like I'm complaining, I think the code forms the basis of a useful tool and it wouldn't take much to address the issues identified so far ... apart from the passworded workbook :)

Thanks

Couple of concerns about this. One is a little cosmetic in that a number of the workbooks I processed displayed a message "This workbook contains links that cannot be updated" which paused the execution. This can probably be overcome using DisplayAlerts=False. The next is perhaps more serious: my understanding is that if you open a workbook under macro control, macros are automatically enabled for the workbook being opened. This means that w Workbook Open event can/will be executed. This can be avoided using EnableEvents=False before opening the workbook. And finally, I chose to run the macro on a workbook with hundreds of workbooks in it (probably a mistake) but the string being generated might be huge. I didn't find out because the code crashed because of the other problems :)