Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    check for macros (excel2000+)

    I am looking for some sort of script/batch file that will scan a list of spreadsheet files (.xls) and will output the names of all the files which contain macros or which don't contain macros.
    Many thanx
    Smbs

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: check for macros (excel2000+)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Smbs

    I have some questions that maybe needed to answer your question properly:

    1) Do you want to do this scanning from within MS-Excel, or as an external process?

    2) All these workbooks <<< spreadsheet files (.xls) >>> are in the same location, or all over the hard drive?

    3) <<< contain macros >>> There are many ways code can be included in a workbook. You have a code module, you have objects that can run code when the object is manipulated, there are class modules that can also contain code, and may other worksheet objects that may have code behind. Do you mean scanning for all these code places, or simply a code module?

    4) What do you want to do with the list, I mean why do you need to know what workbook has code?

    Thanks

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for macros (excel2000+)

    Wassim many thanx
    1) Not from within MS-Excel--an an external process is preferred.
    2) For simplicity -all these workbooks <<< spreadsheet files (.xls) >>> are in the same location- ie: same folder.
    3) Simply a code module is good enough at the moment.
    4) For security I don't want to allow mail files with excel file attachments containing macros floating around in our network.
    Smbs

  4. #4
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: check for macros (excel2000+)

    Here is an Excel macro that allows you to multi-select files and then it checks and reports whether each workbook has code. Just place this in an empty workbook. Don't forget to add the VBA reference. FYI It's not fast! HTH --Sam
    <pre>Option Explicit
    Sub listCode()
    ' Must add a reference to Microsoft Visual Basic for Applications Extensibility
    Dim list As Variant, sFilter As String, i As Long, iRow As Long
    Dim wb As Workbook, vbComp As VBIDE.VBComponent
    sFilter = "Excel Workbooks (*.xl?), *.xl?, All Files (*.*), *.*"
    list = Application.GetOpenFilename(filefilter:=sFilter, _
    Title:="Select Files to Check", MultiSelect:=True)
    On Error GoTo pressedCancel
    i = LBound(list) ' Check for Cancel
    On Error GoTo 0
    With ActiveSheet
    .UsedRange.EntireRow.Clear
    .[A1] = "File"
    .[B1] = "Code"
    iRow = 2
    For i = LBound(list) To UBound(list)
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(Filename:=list(i), UpdateLinks:=False, _
    ReadOnly:=True)
    .Cells(iRow, 1) = list(i)
    .Cells(iRow, 2) = "No"
    For Each vbComp In wb.VBProject.VBComponents
    On Error GoTo 0
    Select Case vbComp.Type
    Case vbext_ct_StdModule, vbext_ct_ClassModule, vbext_ct_MSForm
    .Cells(iRow, 2) = "Yes"
    Exit For
    Case Else
    If vbComp.CodeModule.CountOfLines > 0 Then
    .Cells(iRow, 2) = "Yes"
    Exit For
    End If
    End Select
    Next vbComp
    iRow = iRow + 1
    wb.Close False
    Application.ScreenUpdating = True
    Next i
    End With
    pressedCancel:
    End Sub
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for macros (excel2000+)

    See if this article offers any help:

    http://support.microsoft.com/kb/q224351/
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for macros (excel2000+)

    Thanx SammyB !
    Works just great but I have 1 problem----- Program only lets me choose from folder "Cocuments and SettingssmbsMy Documents " if I try and change to files in different folder the program hangs. I am trying to understand why but any help as how to fix this problem would be great. It is as u warned me a bit slow but it gets the job done.
    Great!!! once again many thanx.
    Smbs

  7. #7
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for macros (excel2000+)

    Thanx pieterse !!
    Will look it up
    Smbs

  8. #8
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: check for macros (excel2000+)

    Solved the problem --I had a mapped network drive which was not connected which caused excel to hang!!!
    Many thanx
    Smbs

Posting Permissions

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