VBA: Folder picker run-time error Object Variable or With block variable not set-CodePudding

I'm trying to add in a folder selection dialog into my code and I keep getting the run-time error "Object variable or With block variable not set". Honestly, I suspect that the problem is just me being dumb and might be as simple as a typo or me not understanding something simple about this function, but I just can't seem to figure it out.

Dim ofso As Scripting.FileSystemObject
Dim oFolder As Object
Dim oFile As Object
Dim i As Long, colFolders As New Collection, ws As Worksheet
 
Set ws = Sheets.Add(Type:=xlWorksheet, After:=ActiveSheet)
Set ofso = CreateObject("Scripting.FileSystemObject")
'Set oFolder = ofso.GetFolder("F:\") This is the line to be replaced with the folder picker and what was being used before.
'Start folder picker
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
With FldrPicker
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub 'Check if user clicked cancel button
    oFolder = .SelectedItems(1) & "\"
End With

Then the code resumes with everything else that works fine when not using the folder picker thing. Stepping through the code, it gives the error when it gets to the line

    oFolder = .SelectedItems(1) & "\"

Maybe I just need another set of eyes to point out what I'm missing? Or maybe I'm just not understanding something fundamental here (I'm still learning). Either way, I need help.

I also tried playing with my object names

Set oFolder = Application.FileDialog(msoFileDialogFolderPicker)

With oFolder
    .Title = "Select A Target Folder"
    .AllowMultiSelect = False
    If .Show <> -1 Then Exit Sub 'Check if user clicked cancel button
    oFolder = .SelectedItems(1) & "\"
End With

And it still isn't working

Edit: Here is my full code without the folder picker, to show what I'm trying to do.

Sub GetFilesColFunc()
Application.ScreenUpdating = False

Dim ofso As Scripting.FileSystemObject
Dim FldrPicker As FileDialog
Dim oFolder As Object
Dim oFile As Object
Dim i As Long, colFolders As New Collection, ws As Worksheet

Set ws = Sheets.Add(Type:=xlWorksheet, After:=ActiveSheet)
Set ofso = CreateObject("Scripting.FileSystemObject")
Set oFolder = ofso.GetFolder("F:\")


On Error Resume Next
       
ws.Cells(1, 1) = "File Name"
ws.Cells(1, 2) = "File Type"
ws.Cells(1, 3) = "Date Created"
ws.Cells(1, 4) = "Date Last Modified"
ws.Cells(1, 5) = "Date Last Accessed"
ws.Cells(1, 6) = "File Path"

Rows(1).Font.Bold = True
Rows(1).Font.Size = 11
Rows(1).Borders(xlEdgeBottom).LineStyle = XlLineStyle.xlContinuous
Range("C:E").Columns.AutoFit
       
colFolders.Add oFolder          'start with this folder

Do While colFolders.Count > 0      'process all folders
    Set oFolder = colFolders(1)    'get a folder to process
    colFolders.Remove 1            'remove item at index 1
                
    For Each oFile In oFolder.Files
        
            ws.Cells(i   2, 1) = oFile.Name
            ws.Cells(i   2, 2) = oFile.Type
            ws.Cells(i   2, 3) = oFile.DateCreated
            ws.Cells(i   2, 4) = oFile.DateLastModified
            ws.Cells(i   2, 5) = oFile.DateLastAccessed
            ws.Cells(i   2, 6) = oFolder.Path
            i = i   1
        
    Next oFile

    'add any subfolders to the collection for processing
    For Each sf In oFolder.SubFolders
        If Not SkipFolder(sf.Name) Then colFolders.Add sf 'Skips folders listed within the referenced function
    Next sf
       
Loop

Application.ScreenUpdating = True

End Sub

CodePudding user response：

You are confusing things, probably because you stared at your code for too long :)

I botched together an example for you that hopefully illustrates some of the confusing stuff. Please note: I haven't done VBA in ages

I divided up your problem into a few subsections.

A FileDialog (aka the folderpicker built-in in Office) returns strings. So I put that into its own function.

You cannot simply create an FSO Folder object by assigning a string to it. That is not how objects work, you have to bring them to live with the Set keyword. When you are new to VBS/VBA that is hard to understand at first. The GetFolder method from the Windows Scripting Host's FileSystemObject returns a Folder object. I put the output of that in the GetFSOFolder function. The GetFSOFolder returns an object, so you have to Set the variable (oFolder) that captures it

Without the fluff that you decorated your OP with, here is an example of how you could approach this to just get the oFolder in your OP.

It is probably longer than you want it to be, the reason being that I hope to clarify some things by being elaborate about it.


Sub Main()

    Dim sFolder As String
    sFolder = FolderPicker() 'get the string representation from FileDialog
    If sFolder = "" Then
        Debug.Print "No folder was selected"
        Exit Sub
    End If
    
    'create a Folder object from the string
    Dim oFolder As Object
    Set oFolder = GetFSOFolder(sFolder)

    'what do we have?
    Debug.Print "Selected folder was: " & oFolder.path
    
End Sub

Function GetFSOFolder(path As String) As Object 'returns a Folder object if path is valid
    
    Dim ofso As Scripting.FileSystemObject
    Set ofso = CreateObject("Scripting.FileSystemObject")
    Set GetFSOFolder = ofso.GetFolder(path) 'note the Set, we are returning an object

End Function

Function FolderPicker() As String 'takes care of the folder picking dialog stuff
    Dim FldrPicker As FileDialog
    'Start folder picker
    Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)
    With FldrPicker
        .Title = "Select A Target Folder"
        .AllowMultiSelect = False
        If .Show <> -1 Then Exit Function 'Check if user clicked cancel button
        FolderPicker = .SelectedItems(1) '.SelectedItems(1) returns a string!
    End With
End Function