How to extract file name from path

Question

How do I extract the filename myfile pdf from C  Documents myfile pdf in VBA

User · Answer

To get the file name in an excel macro is   filname   Mid spth  InStrRev spth       Len spth     1  Len spth   MsgBox Mid filname  1  InStr filname       - 1

User · Answer

The best way of working with files and directories in VBA for Office 2000 2003 is using the scripting library  Add a reference to Microsoft Scripting Runtime  Tools   References in the IDE    Create a filesystem object and do all operations using that   Dim fso as new FileSystemObject Dim fileName As String fileName   fso GetFileName  c  any path file txt     The FileSystemObject is great  It offers a lot of features such as getting special folders  My documents  etc   creating  moving  copying  deleting files and directories in an object oriented manner  Check it out

User · Answer

This gleaned from Twiggy   http   archive atomicmpc com au and other places    since the file name and path were used several times in code  variables were made public  Public FName As Variant  Filename As String  Path As String  Sub xxx             If Not GetFileName   1 Then Exit Sub          End Sub  Private Function GetFileName      GetFileName   0  used for error handling at call point in case user cancels    FName   Application GetOpenFilename  Ramp log file    txt     txt      If Not VarType FName    vbBoolean Then GetFileName   1  to assure selection was made    Filename   Split FName       UBound Split FName          results in file name    Path   Left FName  InStrRev FName         results in path End Function

User · Answer

If you want a more robust solution that will give you both the full folder s path AND the filename  here it is   Dim strFileName As String  strFolderPath As String Dim lngIndex As Long Dim strPath   As String  strPath     Split OpenArgs          Put the Parts of our path into an array lngIndex   UBound strPath  strFileName   strPath lngIndex      Get the File Name from our array strPath lngIndex                    Remove the File Name from our array strFolderPath   Join strPath        Rebuild our path from our array   Or as a sub function   Private Sub SeparatePathAndFile ByRef io strFolderPath As String  ByRef o strFileName As String          Dim strPath   As String     Dim lngIndex As Long      strPath     Split io strFolderPath         Put the Parts of our path into an array     lngIndex   UBound strPath      o strFileName   strPath lngIndex     Get the File Name from our array     strPath lngIndex                     Remove the File Name from our array     io strFolderPath   Join strPath            Rebuild our path from our array   End Sub   You pass the first parameter with the full path of the file and it will be set to the folder s path while the second parameter will be set to the file s name

User · Answer

Function file name only file path As String  As String  Dim temp As Variant  temp   Split file path  Application PathSeparator   file name only   temp UBound temp    End Function    here you give your file name as input of the function the split function of VBA splits the path in different portion by using     as path separator  amp  stores them in an array named  temp  the UBound   finds the max item number of array and finally assigns the result to  file name only  function   Hope this will be helpful

User · Answer

I ve read through all the answers and I d like to add one more that I think wins out because of its simplicity   Unlike the accepted answer this does not require recursion   It also does not require referencing a FileSystemObject   Function FileNameFromPath strFullPath As String  As String      FileNameFromPath   Right strFullPath  Len strFullPath  - InStrRev strFullPath         End Function   http   vba-tutorial com parsing-a-file-string-into-path-filename-and-extension  has this code plus other functions for parsing out the file path  extension and even the filename without the extension

User · Answer

Dim nme As String   My Computer FileSystem GetFileInfo pathFicheiro  Name Dim dirc As String   My Computer FileSystem GetFileInfo nomeFicheiro  Directory

User · Answer

I can t believe how overcomplicated some of these answers are     no offence    Here s a single-line function that will get the job done       Function getFName pf As String getFName Mid pf InStrRev pf      1  End Function       Function getPath pf As String getPath Left pf InStrRev pf       End Function     Examples

User · Answer

I needed the path  not the filename   So to extract the file path in code   JustPath   Left sFileP  Len sFileP  - Len Split sFileP       UBound Split sFileP

User · Answer

Here s a simple VBA solution I wrote that works with Windows  Unix  Mac  and URL paths   sFileName   Mid Mid sPath  InStrRev sPath         1   InStrRev sPath         1   sFolderName   Left sPath  Len sPath  - Len sFileName     You can test the output using this code    Visual Basic for Applications  http    https   www server com docs Letter txt  unix     home user docs Letter txt  dos    C  user docs Letter txt  win      Server01 user docs Letter txt  blank       sPath   unix  sFileName   Mid Mid sPath  InStrRev sPath         1   InStrRev sPath         1  sFolderName   Left sPath  Len sPath  - Len sFileName    Debug print  Folder     amp  sFolderName  amp    File     amp  sFileName   Also see  Wikipedia - Path  computing

User · Answer

Dim sFilePath   sFileName  sFileName   Split sFilePath       UBound Split sFilePath

User · Answer

Here s an alternative solution without code  This VBA works in the Excel Formula Bar   To extract the file name    RIGHT A1 LEN A1 -FIND     SUBSTITUTE A1         LEN A1 -LEN SUBSTITUTE A1               To extract the file path    MID A1 1 LEN A1 -LEN MID A1 FIND CHAR 1  SUBSTITUTE A1     CHAR 1  LEN A1 -LEN SUBSTITUTE A1            1 LEN A1

User · Answer

Dir  C  Documents myfile pdf     will return the file name  but only if it exists

User · Answer

I am using this function    VBA Function   Function FunctionGetFileName FullPath As String  As String  Update 20140210 Dim splitList As Variant splitList   VBA Split FullPath       FunctionGetFileName   splitList UBound splitList  1   End Function   Now enter    FunctionGetFileName A1  in youe required cell    or You can use these      MID A1 FIND     SUBSTITUTE A1         LEN A1 -LEN SUBSTITUTE A1            1 LEN A1

User · Answer

This is taken from snippets dzone com   Function GetFilenameFromPath ByVal strPath As String  As String   Returns the rightmost characters of a string upto but not including the rightmost       e g   c  winnt win ini  returns  win ini       If Right  strPath  1   lt  gt      And Len strPath   gt  0 Then         GetFilenameFromPath   GetFilenameFromPath Left  strPath  Len strPath  - 1     Right  strPath  1      End If End Function

User · Answer

The simplest approach if you are sure the file physically exists on the disk   Dim fileName  filePath As String filePath    C  Documents myfile pdf  fileName   Dir filePath    If you are not sure about existence of file or just want to extract filename from a given path then  simplest approach is   fileName   Mid filePath  InStrRev filePath         1

[string] How to extract file name from path?

Examples related to string

Examples related to vba