For Pro's - Link, Macro in workbooks folders

[paludgnp]

Hello,

This one is pretty difficult.
I have a folder with many many workbooks, and I would like to determine automatically whether these workbook has some linked info or include macros.
In other terms, for people who prefer code as words:

for each WORKBOOK in FOLDER
if IsLink(WORKBOOK)=true then Msgbox "WORKBOOK" & WORKBOOK " has link"
if ISMacro(WORKBOOK)=true then Msgbox "WORKBOOK" & WORKBOOK " has Macro" next WORKBOOK

Good luck,
Pierre

 

[Juan Pablo González]

It's not that difficult...

For the second function to work you must set a reference to "Microsoft Visual Basic For Applications Extensibility"

Here are the functions:

Option Explicit

Function HasLinks(WbName As String) As Boolean
Dim W As Workbook
Dim a
On Error GoTo Exiting
Set W = Workbooks(WbName)
On Error GoTo 0
a = W.LinkSources
HasLinks = Not IsEmpty(a)
Exiting:
End Function

Function HasMacros(WbName As String) As Boolean
Dim W As Workbook
Dim WM As VBComponent
On Error GoTo Exiting
Set W = Workbooks(WbName)
For Each WM In W.VBProject.VBComponents
    If WM.CodeModule.CountOfLines > 2 Then HasMacros = True: Exit Function
Next WM
Exiting:
End Function

The .CountOfLines > 2 is that way because there may be an "Option Explicit" there, WHICH doesn't qualify as macro, but makes 2 lines.

You can use it like

?HasLinks(ThisWorkbook.Name)

or

?HasMacros("Testing.xls")

or in Excel

=HasMacros("OtherTest.xls")

The workbook MUST BE OPEN.