VBA:未设置文件夹选取器运行时错误"对象变量"或"With块变量"

Question

我试图在代码中添加一个文件夹选择对话框，但我一直收到运行时错误"对象变量或With块变量未设置"；。老实说，我怀疑问题只是我太笨了，可能简单到打字错误，或者我不理解这个函数的一些简单之处，但我似乎就是想不通。

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

然后，当不使用文件夹选择器时，代码会继续处理其他一切正常工作的内容。通过代码，当它到达行时，它会给出错误

oFolder = .SelectedItems(1) & ""

也许我只是需要另一双眼睛来指出我错过了什么？或者我只是不理解这里的一些基本内容(我还在学习(。不管怎样，我都需要帮助。

我还试着玩我的对象名

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

它仍然不起作用

编辑：这是我没有文件夹选择器的完整代码，以显示我正在尝试做什么。

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

结束子

Answer 1

您混淆了事情，可能是因为您盯着代码看了太久：(

我为你拼凑了一个例子，希望能说明一些令人困惑的东西。请注意：我已经很久没有做VBA了

我把你的问题分成几个小节。

FileDialog(也就是Office中内置的文件夹选择器(返回字符串。所以我把它放在它自己的功能中。

你不能简单地通过给一个FSOFolder对象分配一个字符串来创建它。对象不是这样工作的，你必须让它们与Set关键字一起使用。当你刚接触VBS/VBA时，一开始很难理解。来自Windows脚本主机的FileSystemObject的GetFolder方法返回一个Folder对象。我把它的输出放在GetFSOFolder函数中。GetFSOFolder返回一个对象，所以您必须Set捕获它的变量(oFolder(

如果没有你装饰你的OP的绒毛，这里有一个例子，说明你如何处理这个问题，只在你的OP中获得oFolder。

它可能比你想要的要长，原因是我希望通过详细说明它来澄清一些事情

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