如何在 Excel VBA 中存储文件夹路径

How to Store a Folder Path in Excel VBA

提问by NPoorbaugh

Sub GetFolderPath()
Dim InputFolder As String
Dim OutputFolder As String

InputFolder = Application.GetOpenFilename("Folder, *")
Range("C1").Select
ActiveCell.Value = InputFolder & "\"

End Sub

I am using the code above to attempt to store, and then paste, a folder location for another macro I am running.

我正在使用上面的代码尝试存储，然后粘贴我正在运行的另一个宏的文件夹位置。

Any idea how to make it stop at the folder level or remove the filename from the end?

知道如何让它在文件夹级别停止或从末尾删除文件名吗？

Thanks!

谢谢！

回答by AxGryndr

You could use

你可以用

FileName = Dir(InputFolder)
InputFolder = Left(InputFolder, Len(InputFolder)-Len(FileName))

Dir() gets just the file name and Left() helps trim down the string to just the folder path.

Dir() 仅获取文件名，而 Left() 有助于将字符串缩减为仅文件夹路径。

回答by Kazimierz Jawor

There is even shorter option to get your path. Just with one single line:

有更短的选择来获取您的路径。只需一行：

'...your code
Dim InputFolder As String
InputFolder = Application.GetOpenFilename("Folder, *")

'new, single line solution
InputFolder = Mid(InputFolder, 1, InStrRev(InputFolder, Application.PathSeparator))

And I think there could be some more options available :)

而且我认为可能还有更多选择：)

回答by NPoorbaugh

Wow, this board is incredible! I would up using casey's code and it worked perfectly :). I also added in a function to create subfolders as needed.

哇，这个板子太棒了！我会使用凯西的代码，它工作得很好:)。我还添加了一个函数来根据需要创建子文件夹。

Here is the final product I settled on.

这是我选择的最终产品。

    Option Explicit

Sub GetFolderPath()
Dim InputFolder As String
Dim OutputFolder As String

MsgBox ("Please Select the Folder of Origin")
  InputFolder = Application.GetOpenFilename("Folder, *")
    Range("D5").Value = getFilePath(InputFolder)
MsgBox ("Please Select the Desired Destination Root Folder")
  InputFolder = Application.GetOpenFilename("Folder, *")
    Range("E5").Value = getFilePath(InputFolder)

    Dim OutputSubFolder As String
    Dim Cell As Range
      Range("E5").Select
    OutputSubFolder = ActiveCell.Value


    'Loop through this range which includes the needed subfolders
        Range("C5:C100000").Select
          For Each Cell In Selection
        On Error Resume Next
          MkDir OutputSubFolder & Cell
        On Error GoTo 0
        Next Cell

End Sub

Function getFilePath(path As String)

Dim filePath() As String
Dim finalString As String
Dim x As Integer
filePath = Split(path, "\")

For x = 0 To UBound(filePath) - 1
    finalString = finalString & filePath(x) & "\"
Next

getFilePath = finalString
End Function

回答by Casey

If I understand right, you want to get the path to a file but you do not want to file name in the InputFolder string. If I understood correctly then this will do the trick:

如果我理解正确，您想获取文件的路径，但不想在 InputFolder 字符串中输入文件名。如果我理解正确，那么这将起作用：

    Option Explicit

Sub GetFolderPath()
Dim InputFolder As String
Dim OutputFolder As String

InputFolder = Application.GetOpenFilename("Folder, *")
Range("C1").Value = getFilePath(InputFolder)

End Sub

Function getFilePath(path As String)

Dim filePath() As String
Dim finalString As String
Dim x As Integer
filePath = Split(path, "\")

For x = 0 To UBound(filePath) - 1
    finalString = finalString & filePath(x) & "\"
Next

getFilePath = finalString
End Function

Also, you do not have to write the file name to the spreadsheet in order for another macro to get it. You can just call the other macro from your first macro and pass the file name as a parameter or set the file name variable as a module level variable so it can be accessed by the other macro, assuming that second macro is in the same module.

此外，您不必将文件名写入电子表格，以便其他宏获取它。您可以从第一个宏调用另一个宏并将文件名作为参数传递或将文件名变量设置为模块级变量，以便其他宏可以访问它，假设第二个宏在同一个模块中。