Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: For Pro's - Link, Macro in workbooks folders

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Belgium
    Posts
    24
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
    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.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •