Results 1 to 8 of 8
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Searching Workbooks that contain code (XP)

    I have a number of workbooks, some of which contain VBA, some which do not. Wondering if it is possible to develop a worksheet with VBA that would search through all workbooks in a common folder and then list the names of those workbooks which have VBA coding into the worksheet.

    What I am hoping for is to be able to take all workbooks which contain code and place them in a separate folder without having to open each work book, check for code and then move.

    Hope this is clear.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Searching Workbooks that contain code (XP)

    What exactly do you mean by "have VBA coding"?
    If a workbook contains a completely empty module, does it have VBA code?
    If a workbook contains a module with just a few blank lines in it, does it have VBA code?
    If a workbook contains a module with only a line "Option Explicit" and some blank lines, does it have VBA code?

    Also: all workbooks have code modules associated with the workbook itself (ThisWorkbook) and with each sheet. If Require Variable Declaration has been turned on (as it should), each of these contains a line "Option Explicit".

  3. #3
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching Workbooks that contain code (XP)

    Code that I have created in individual workbooks. I want to be able to take the code from these individual workbooks and put all the code into personal.xls. For some reason the code which I did have in personal.xls have vanished.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Searching Workbooks that contain code (XP)

    The code in the attached text file will list the number of standard and class modules in each workbook in a folder. Change the constant strPath to the path of the folder containing the workbooks.
    You can use the code as starting point and modify it to suit your purposes.
    Attached Files Attached Files

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching Workbooks that contain code (XP)

    Does this do what you want?

    <code>
    Public Sub GetWBWithCode()
    Dim oWBList As Range
    Dim vFN As Variant
    Dim strPath As String, strFile As String
    Dim oWB As Workbook
    Dim obComponent As Object, obCode As Object
    Dim I As Long
    Application.ScreenUpdating = False
    Set oWBList = ThisWorkbook.Worksheets(1).Range("A1")
    I = 0
    vFN = Application.GetOpenFilename("XL Workbooks (*.xls), *.xls", , "Select a file in the Directory to be scanned")
    If vFN = False Then Exit Sub
    strPath = Left(vFN, InStrRev(vFN, ""))
    strFile = Dir(strPath & "*.xls")
    If strFile = "" Then Exit Sub
    Do While strFile <> ""
    Application.DisplayAlerts = False
    Set oWB = Workbooks.Open(Filename:=strPath & strFile)
    Application.DisplayAlerts = True
    If Not oWB Is Nothing Then
    For Each obComponent In oWB.VBProject.VBComponents
    Set obCode = obComponent.CodeModule
    If obCode.CountOfLines > 0 Then
    oWBList.Offset(I, 0).Value = oWB.FullName
    I = I + 1
    Exit For
    End If
    Next obComponent
    oWB.Close
    End If
    strFile = Dir
    Loop
    Application.ScreenUpdating = True
    End Sub
    </code>
    Legare Coleman

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Searching Workbooks that contain code (XP)

    When I run this code, it lists *every* workbook in the folder. This is because I have "Require Variable Declaration" turned on, so the ThisWorkbook module and all worksheet modules will have at least the line "Option Explicit" even if I have never opened them, let alone entered code.

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Searching Workbooks that contain code (XP)

    Yes, that would be a problem. DOes this work better:

    <code>
    Public Sub GetWBWithCode()
    Dim oWBList As Range
    Dim vFN As Variant
    Dim strPath As String, strFile As String
    Dim oWB As Workbook
    Dim obComponent As Object, obCode As Object
    Dim I As Long
    Application.ScreenUpdating = False
    Set oWBList = ThisWorkbook.Worksheets(1).Range("A1")
    I = 0
    vFN = Application.GetOpenFilename("XL Workbooks (*.xls), *.xls", , "Select a file in the Directory to be scanned")
    If vFN = False Then Exit Sub
    strPath = Left(vFN, InStrRev(vFN, ""))
    strFile = Dir(strPath & "*.xls")
    If strFile = "" Then Exit Sub
    Do While strFile <> ""
    Application.DisplayAlerts = False
    Set oWB = Workbooks.Open(Filename:=strPath & strFile)
    Application.DisplayAlerts = True
    If Not oWB Is Nothing Then
    For Each obComponent In oWB.VBProject.VBComponents
    Set obCode = obComponent.CodeModule
    If (obCode.CountOfLines - obCode.CountOfDeclarationLines) > 1 Then
    oWBList.Offset(I, 0).Value = oWB.FullName
    I = I + 1
    Exit For
    End If
    Next obComponent
    oWB.Close
    End If
    strFile = Dir
    Loop
    Application.ScreenUpdating = True
    End Sub
    </code>
    Legare Coleman

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Searching Workbooks that contain code (XP)

    Yes, that version works fine on my test folder.

Posting Permissions

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