Using FolderObj = FileSystemObj.GetFolder("Folderpath") the Folder path as through Folder picker

[Shaan Bhasin]

Hi All,

I want to use the below line in my excel vba :
FolderObj = FileSystemObj.GetFolder("Folderpath")

However i want the Folder path to be Dynamic that's it will ask the user to choose the folder through Folder picker or some other command.

Any help how i can achieve the same.

Thanks in adavnce.
Shaan

 

[Darren Bartrup]

Something like this:

Public Sub ToUse()

    Dim FolderObj As Object
    Dim FolderPath As String
    
    'FolderPath = CStr(GetFolderString())  'This will use ThisWorkbook.Path as starting folder.
    'OR
    FolderPath = CStr(GetFolderString("C:\Documents and Settings"))
    
    If FolderPath <> "" Then
        Set FolderObj = GetFolderObject(FolderPath)
    End If
    
    
End Sub


Public Function GetFolderObject(FolderPath As String) As Object
    
    Dim mFSO As Object
    Set mFSO = CreateObject("Scripting.FileSystemObject")
    
    Set GetFolderObject = mFSO.GetFolder(FolderPath)
    
End Function


Function GetFolderString(Optional startFolder As Variant = -1) As Variant
    Dim fle As Object
    Dim vItem As Variant
    
    Set fle = Application.FileDialog(4)
    With fle
        .Title = "Select a folder"
        .AllowMultiSelect = False
        If startFolder = -1 Then
            .InitialFileName = ThisWorkbook.Path
        Else
            If Right(startFolder, 1) <> "\" Then
                .InitialFileName = startFolder & "\"
            Else
                .InitialFileName = startFolder
            End If
        End If
        If .Show <> -1 Then GoTo NextCode
        vItem = .SelectedItems(1)
    End With
NextCode:
    GetFolderString = vItem
    Set fle = Nothing
End Function