VBA Express : Multiple Apps - Get the File Name or the Path

Multiple Apps

Get the File Name or the Path

Ease of Use

Easy

Version tested with

2000, 2002, 2003

Submitted by:

mark007

Description:

Functions to retrieve the filename with or without the extension and the path to the file from a given full path.

Discussion:

You might need the path function if you want to open a file in the same folder. You might need the filename function to store it in a database or name some other field in your app.

Code:

instructions for use

			Option Explicit 
 
 'The following function returns the filename without the extension from the file's full path:
Function FileNameNoExt(strPath As String) As String 
    Dim strTemp As String 
    strTemp = Mid$(strPath, InStrRev(strPath, "\") + 1) 
    FileNameNoExt = Left$(strTemp, InStrRev(strTemp, ".") - 1) 
End Function 
 
 'The following function returns the filename with the extension from the file's full path:
Function FileNameWithExt(strPath As String) As String 
    FileNameWithExt = Mid$(strPath, InStrRev(strPath, "\") + 1) 
End Function 
 
 'the following function will get the path only (i.e. the folder) from the file's ful path:
Function FilePath(strPath As String) As String 
    FilePath = Left$(strPath, InStrRev(strPath, "\")) 
End Function

How to use:

Open your application fo choice e.g. Word or Excel
Copy the code
Press Alt + F11 to open the Visual Basic Editor (VBE)
Select INSERT > MODULE from the menubar
Paste code into the right pane
Then call the function from other code when you need it or in an excel cell when you need it

Test the code:

Go to TOOLS > MACRO > MACROS
When the dialog appears, select (TestIt)
Press Run
This will return the outputs from calling each function on the current workbook path.

Sample File:

sample.zip 7.66KB

Approved by mdmackillop

This entry has been viewed 292 times.