Directory Path of selected file

[spydey]

Using some VBA, I am able to select a file. I need to get the directory path of that selected file without the file name.

The answer seems to be right in front of me but I am missing something.

Here is the code I am using:

Private Sub PickFile()


Dim fd As FileDialog
Dim sPath As String


Set fd = Application.FileDialog(msoFileDialogFilePicker)
    With fd
            .Title = "Select file"
            If fd.Show <> -1 Then MsgBox "No file selected! Exiting script.": End
        sPath = fd.SelectedItems(1)
        
    End With
    
    MsgBox sPath w/out file name
                
End Sub

The selected file will always be a .xlsx file.

Example: File selected: C:\Folder\Where\File\Is\Located\File.xlsx

I need: C:\Folder\Where\File\Is\Located\

Seems it should be quite simple, but the solution eludes me.

Any thoughts/suggestions?

-Spydey

P.S. I realize that "MsgBox sPath w/out file name" isn't going to give me the directory path. That is just an example of what I hope to have the msgbox output.

 

[Norie]

Try this,

    MsgBox Replace(sPath, Dir(sPath), "")

or this.

    MsgBox Left(sPath, InStrRev(sPath, "\"))

 

[Rick Rothstein]

Try this...

MsgBox Left(sPath, InStrRev(sPath, ""))

Note: Using End to stop your code is usually considered a bad way to do that... better is to use Exit Sub for a macro/subroutine and Exit Function for a UDF/function.

 

[spydey]

Try this,

    MsgBox Replace(sPath, Dir(sPath), "")

or this.

    MsgBox Left(sPath, InStrRev(sPath, "\"))

Thank you Norie for those two options. I have tried them both and they do what I need. Just out of curiosity, what is the difference between the two? Obviously one uses Replace with Dir and the other uses Left with InStrRev. What are the benefits of using one over the other?

Thanks again for your assistance, I very much appreciate it!

-Spydey

 

[spydey]

Try this...

MsgBox Left(sPath, InStrRev(sPath, ""))

Note: Using End to stop your code is usually considered a bad way to do that... better is to use Exit Sub for a macro/subroutine and Exit Function for a UDF/function.

Rick, thanks for the clarification of using End vs Exit Sub. Out of curiosity, why is it considered better to use Exit Sub vs End? Would it be that Exit Sub stops the currently called "child" macro and returns to the "parent" macro, allowing it to continue, whereas End just puts a complete halt/stop on any and every Macro running? Perhaps I have those backwards .....

Thanks again Rick. Your help is greatly appreciated!

-Spydey

 

[Rick Rothstein]

Rick, thanks for the clarification of using End vs Exit Sub. Out of curiosity, why is it considered better to use Exit Sub vs End? Would it be that Exit Sub stops the currently called "child" macro and returns to the "parent" macro, allowing it to continue, whereas End just puts a complete halt/stop on any and every Macro running? Perhaps I have those backwards .....

The problem with End comes when you start to write more sophisticated programs that create objects... it is my understanding that using End does not allow VB to clean up after itself so these object do not get removed from memory thus stealing RAM from your system until your system is rebooted. I believe there are other "clean up" things VB does not get to do as well, but the object thing is the one that comes to mind immediately. One way around the child-chaining problem you mentioned is to make your subroutines functions instead and have the function return a testable condition back to the calling procedures as to whether it ended properly or not.

 

[spydey]

The problem with End comes when you start to write more sophisticated programs that create objects... it is my understanding that using End does not allow VB to clean up after itself so these object do not get removed from memory thus stealing RAM from your system until your system is rebooted. I believe there are other "clean up" things VB does not get to do as well, but the object thing is the one that comes to mind immediately. One way around the child-chaining problem you mentioned is to make your subroutines functions instead and have the function return a testable condition back to the calling procedures as to whether it ended properly or not.

Hmmm, interesting point. Thanks Rick! I will have to look in to this more. Thanks for pointing it out.

Take care

-Spydey