vbadirectory

Open existing folder with command button


The code below is not recognizing existing folders.

I tested searching for Shop Order Number MSO-40550 (the code searches "40550" only).

Hovering over the line:

strFullPath = strP & strC & "\" & strGc & "\" & strT

showed the correct path to folder 40550:

\\cftanaus1fs01\ASO_MSO\40000-49999\40500-40599\40550

Shop Order Folders are organized in this hierarchy:

 Parent Folder Name: ASO_MSO

    Child Folder Name: 40000-49999
 
       Grandchild Folder Name: 40500-40599
    
         Target Folder Name: 40550 (used in this example)

Actions

  1. Open Userform
  2. Search for a Shop Order Number (example: MSO-40550)
  3. Click on a command button to open the corresponding folder in Windows Explorer 
Private Sub cmbOpenFolder_Click()
    Const strP = "\\cftanaus1fs01\ASO_MSO\" 'Parent folder
    Dim strC As String 'Child folder, ex: 40000-49999
    Dim strGc As String 'Grandchild folder, ex: 40500-40599
    Dim strT As String 'Target folder, ex: 40550
    Dim strFullPath As String 'Full path
    Dim fso As Object
    strC = Left(txtSuffix, 1) & "0000-" & Left(txtSuffix, 1) & "9999" 'Child folder, ex: 40000-49999
    strGc = Left(txtSuffix, 3) & "00-" & Left(txtSuffix, 3) & "99" 'Grandchild folder, ex: 40500-40599
    strT = txtSuffix 'Target folder, ex: 40550
    strFullPath = strP & strC & "\" & strGc & "\" & strT  'Full path
    Set fso = CreateObject("Scripting.FileSystemObject")   ' Create FileSystemObject
    If fso.FolderExists(strT) = True Then ' Check whether folder exists
       'MsgBox "Here you go!"
       Shell "explorer.exe " & strFullPath, vbNormalFocus  ' Open it
   Else
       MsgBox "This folder does not exist."
       'fso.CreateFolder strFullPath ' Code if you wanted to create a folder.
    End If
End Sub

Solution

  • Here's a different way to handle the folder grouping hierarchy:

    EDIT3: cleaned up and added check for partial folder name, fixed Dir() code

    Option Explicit
    
    Const FOLDER_ROOT As String = "\\cftanaus1fs01\ASO_MSO\" 'Parent folder
    
    Private Sub cmbOpenFolder_Click()
        
        Dim strFullPath As String, fso As Object, txt As String
        Set fso = CreateObject("Scripting.FileSystemObject")   ' Create FileSystemObject
        
        txt = txtSuffix 'get the user entry
        If Len(txt) > 0 Then
            strFullPath = VerifiedFolderPath(txt)
            Debug.Print "Path: " & strFullPath
            'safer to quote the folder path, in case it has spaces
            Shell "explorer.exe """ & strFullPath & """", vbNormalFocus  ' Open it
        Else
            MsgBox "Please enter a folder number", vbExclamation
        End If
    End Sub
    
    'construct and verify a folder path, checking for partial name
    Function VerifiedFolderPath(srch As String) As String
        Dim i As Long, flr As Long, n, f
        i = CLng(srch)
        VerifiedFolderPath = FOLDER_ROOT           'parent folder
        For Each n In Array(10000, 100)    'loop each level of grouping
            flr = Application.Floor(i, n)
            VerifiedFolderPath = VerifiedFolderPath& flr & "-" & (flr + (n - 1)) & "\"
        Next n
        'check for existing matched folder, including partial match
        f = Dir(VerifiedFolderPath & srch & "*", vbDirectory)
        If Len(f) = 0 Then                     'not found?
            MsgBox "No folder was found matching: " & vbLf & VerifiedFolderPath & "*", _
                   vbExclamation, "Folder not found"
            VerifiedFolderPath = ""                    'return empty string
        Else
            VerifiedFolderPath = VerifiedFolderPath & f & "\"  'found: add matched folder name and terminating \
        End If
    End Function