Results 1 to 9 of 9

Thread: Excel Macro That searches for existence of Folder.

  1. #1

    Excel Macro That searches for existence of Folder.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I ran across Ken's code that will search for a folder. It works great , but i am curious if it could be adjusted a little. Currently the code looks for a folder entered into the code. In the code below it loks to see if C:\Template exists. I bad need to be able to enter text or data into say cell K10 and the code will look for a folder that contains any part of the data or text entered into this cell.

    Example:

    I will need code to start at this base folder. N:\Quotes

    we will say i have a subfolder named BR549. FULL PATH N:\Quotes\BR549

    i would like to be able to enter BR5 IN CELL K10 AND THE CODE will start at N:\Quotes and look for and list all folders that start with BR5.




    Code:
    Public Function FileFolderExists(strFullPath As String) As Boolean
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Check if a file or folder exists
        On Error GoTo EarlyExit
        If Not Dir(strFullPath, vbDirectory) = vbNullString Then FileFolderExists = True
     
    EarlyExit:
        On Error GoTo 0
    End Function
    Public Sub TestFolderExistence()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
     
     
        If FileFolderExists("C:\Template") Then
            MsgBox "Folder exists!"
        Else
            MsgBox "Folder does not exist!"
        End If
    End Sub

  2. #2

    Made progress with assining variable

    I figured out how to make a variable out a cell value . Can anyone help with the rest?



    Code:
    Public Sub TestFolderExistence()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
        iMyvalue = Range("B2").Value
    
        ChDir ("C:\")
        If FileFolderExists("C:\" & (iMyvalue)) Then
            MsgBox "Folder exists!"
        Else
            MsgBox "Folder does not exist!"
        End If
    End Sub

  3. #3
    Code:
    Public Sub TestFolderExistence()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
    Dim MyValue As String
    Dim msg As String
    Dim fldr As Object
    
        MyValue = Range("B2").Value
    
        ChDir ("C:\")
        If FileFolderExists("C:\" & MyValue) Then
            
            With CreateObject("Scripting.FileSystemobject").GetFolder("C:\" & MyValue)
            
                For Each fldr In .Subfolders
                
                    msg = msg & fldr.Name & vbNewLine
                Next fldr
            End With
            
            MsgBox msg, vbOKOnly, "All folders"
        Else
            MsgBox "Folder does not exist!"
        End If
    End Sub

  4. #4

    Use Wildcards with folder search

    Bob, that works perfectly. i need to add a little more functioality to it. i need to be able to enter just part of a folder name and it can use wildcards in the code to find all folders. say i have a folder i created named BR549AC4. Several months go by , i can't remember the full name but i do know that it starts with BR5. I would like to be able to enter BR5 and it would list all folders it finds that contain BR5 I listed the code below i have been experimenting with but have not had any luck. Can you help ?
    Thanks so much



    Code:
    Public Sub TestFolderExistence()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
    Dim MyValue As String
    Dim msg As String
    Dim fldr As Object
        MyValue = Range("B2").Value
        ChDir ("C:\")
        'If FileFolderExists("C:\" & MyValue) Then
     
       If FileFolderExists("C:\" & "*" & MyValue & "*") Then
          '"*" & (iMyvalue) & "*"
            With CreateObject("Scripting.FileSystemobject").GetFolder("C:\" & "*" & MyValue & "*")
     
                For Each fldr In .Subfolders
     
                    msg = msg & fldr.Name & vbNewLine
                Next fldr
            End With
     
            MsgBox msg, vbOKOnly, "All folders"
        Else
            MsgBox "Folder does not exist!"
        End If
    End Sub

  5. #5
    Code:
    Public Function FileFolderExists(strFullPath As String) As String
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Check if a file or folder exists
        On Error GoTo EarlyExit
        FileFolderExists = Dir(strFullPath, vbDirectory)
     
    EarlyExit:
        On Error GoTo 0
    End Function
    
    Public Sub TestFolderExistence()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
    Dim MyValue As String
    Dim subFldr As Object
    Dim msg As String
    Dim fldr As String
    
        MyValue = Range("B2").Value
        ChDir "C:\"
        fldr = FileFolderExists("C:\*" & MyValue & "*")
     
        If fldr <> "" Then
        
            For Each subFldr In CreateObject("Scripting.FileSystemobject").GetFolder(fldr).Subfolders
     
                msg = msg & subFldr.Name & vbNewLine
            Next subFldr
     
            MsgBox msg, vbOKOnly, "All folders"
        Else
            MsgBox "Folder does not exist!"
        End If
    End Sub
    Note that I havce changed both procedures.

  6. #6

    Almost There

    Bob, that is working great. Can you see if you could make a slight adjustment. It is so hard at times to explain correctly what you need code to do. I'll do my best to explain below. Thanks so much for your help.

    Say i have this folder structure ( all the br* folders are subfolders of C:\Quotes)

    C:\Quotes\br549
    br540
    br54786
    br6ty6
    br8hr34


    I'll set the path in the code to C:\Quotes
    if i enter br5 into Cell B2 it will list all these folders

    br549
    br540
    br54786

    i need to search all subfolders of C:\Quotes for all folders that contain br5 and it list them.

  7. #7
    Is this what you mean?

    Code:
    Public Sub TestFolderExistence()
    'Author       : Ken Puls (www.excelguru.ca)
    'Macro Purpose: Test if directory exists
    Dim MyValue As String
    Dim subFldr As Object
    Dim msg As String
    Dim fldr As String
    
        MyValue = Range("B2").Value
        ChDir "C:\"
        fldr = "C:\test"
     
        If Dir(fldr, vbDirectory) <> "" Then
        
            For Each subFldr In CreateObject("Scripting.FileSystemobject").GetFolder(fldr).Subfolders
     
                If subFldr.Name Like "*" & MyValue & "*" Then msg = msg & subFldr.Name & vbNewLine
            Next subFldr
     
            MsgBox msg, vbOKOnly, "All folders"
        Else
            MsgBox "Folder does not exist!"
        End If
    End Sub

  8. #8

    That's It !

    That is exactly what i need !

    Thanks so much for your help.

  9. #9
    If I could piggyback on this I could use the help. I've got a good grasp of what the code is doing, Bob, but wonder if it can be tweaked a bit.

    I'm trying to accomplish something similar. For my code, I want to be able to search a specific folder that will be specified in the code. The folder has multiple subfolders and I want to search those subfolders for another subfolder within based on a name that the code will also know.

    How might that code look? I'm not trying to verify if a folder exists, but rather have the code locate the folder within a couple tiers of subfolders. The reasoning is that the folder I'm looking for is saved in a temporary internet folder that can change names, although the main outer folder path is always the same.

    Thanks

Posting Permissions

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