CODEWITHSUNDEEP
excelvba
NPoorbaugh
NPoorbaugh

Reputation: 15

How to Store a Folder Path in Excel VBA

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!

Upvotes: 1

Views: 28247

Answers (4)

NPoorbaugh
NPoorbaugh

Reputation: 15

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

Upvotes: 0

Kazimierz Jawor
Kazimierz Jawor

Reputation: 19067

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 :)

Upvotes: 1

HighHopes
HighHopes

Reputation: 2102

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:

    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.

Upvotes: 0

AxGryndr
AxGryndr

Reputation: 2344

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.

Upvotes: 2

Related Questions