用户输入filedialog到VBA中的源文件的路径

Question

已解决，正如 @z32a7ul i指出的那样，我使用了错误的变量来陈述filedialog之后的路径。它应该是出现的而不是出现。

代码执行什么：我有一个代码，该代码在文件夹中读取文件，在活动工作簿中打印名称，然后按上升顺序放置名称。

obs1：我有以下代码将此信息用于计算，但是此部分与当前问题无关。

目标：我正在尝试创建一个filedialog，以便用户可以输入源文件的文件夹。

问题：我为此创建一个代码，但是由于某种原因，即使格式相同，它也没有读取源文件。

我到底到了：如果我删除了此用户输入，并且只是"硬码"来源的地址（假设我的Gainterer Workbook与他们同一文件夹），则一切正常。但是后来我仅限于可以放置这个"收集者"工作簿的地方。

问题：我没有特定的错误行。结果是问题，因为它找不到源文件。有人对在这里做什么有任何想法吗？

代码：

Option Explicit
Public path As String
Sub Counter()
Dim count As Integer, i As Long, var As Integer
Dim ws As Worksheet
Dim w As Workbook
Dim Filename As String
Dim FileTypeUserForm As UserForm
Dim X As String
Dim varResult As Variant
Dim OutPath As String, OutPathS As String, wPos As Long
Set w = ThisWorkbook
Application.Calculation = xlCalculationManual
 'source input by user
        varResult = Application.GetSaveAsFilename(FileFilter:="Comma  Separated Values Files" & "(*.csv), *.csv", Title:="OutPath", InitialFileName:="D:StartingPath")
        If varResult <> False Then
            OutPath = varResult
            w.Worksheets("FILES").Cells(1, 4) = varResult
        Else
            Exit Sub
        End If
wPos = InStr(OutPath, "StartingPath")
OutPathS = Mid(OutPath, 1, wPos - 1)
**'MY ERROR IS HERE, It has to be OutpathS:
path = OutPath & "*.*" 'this should be: path = OutPathS & "*.*"**

Filename = Dir(path)
ThisWorkbook.Sheets("FILES").Range("A:A").ClearContents
X = GetValue
If X = "EndProcess" Then Exit Sub

Set ws = ThisWorkbook.Sheets("FILES")
i = 0
Do While Filename <> ""
    var = InStr(Filename, X)
    If var <> 0 Then
        i = i + 1
        ws.Cells(i + 1, 1) = Filename
        Filename = Dir()
    Else: Filename = Dir()
    End If
Loop
Range("A2:A" & i).Sort key1:=Range("A2"), order1:=xlAscending, Header:=xlNo     'this will sort the names directly in the "FILES" sheet
Application.Calculation = xlCalculationAutomatic
ws.Cells(1, 2) = i
MsgBox i & " : files found in folder"
End Sub

Function GetValue()
With FileTypeUserForm
    .Show
    GetValue = .Tag
End With
Unload FileTypeUserForm
End Function

obs2：有一个公共变量，因为它将在后续宏中使用，用于计算。

obs3：整个FileDialog部分只是为了找到源文件所在的路径。它没有保存任何东西。

Answer 1

如果您只需要选择一个文件夹，请考虑使用Application.FileDialog(msoFileDialogFolderPicker)

返回选定文件夹的功能看起来像

 Function GetFolder(initPath As String) As String
    Dim dialog As FileDialog
    Set dialog = Application.FileDialog(msoFileDialogFolderPicker)
    dialog.title = "Select a Folder"
    dialog.AllowMultiSelect = False
    dialog.InitialFileName = initPath 
    If dialog.show Then
        GetFolder = dialog.SelectedItems(1)
    Else
        GetFolder = ""
    End If
    Set dialog = Nothing
End Function