The function runs itself until coming up a backslash ("\") or not a character left.
If the test is passed, the function returns itself with the argument without its last character and the last character of the path. The first row is a logical test that checks if the last character in the argument is a backslash ("\") or not and if the argument is not an empty string. The function's name is GetFilenameFromPath_Recursive and take a single argument named Path. Because the most of Excel users do not have access to the LAMBDA function, we will show you VBA version which any Excel user can use. You could have used recursive functions only in VBA until the LAMBDA function has been released. The recursive approach acts like an iteration and helps us to parse values starting from the end of a string. GetFileNameFromPath_FSO = fso.GetFileName(Path)Ī recursive function is function which calls itself. Here is the function version you can use in your worksheets as well.įunction GetFileNameFromPath_FSO(ByVal Path As String) As String The above line returns "Dashboard.xlsm" string. GetFileNameFromPath_FSO = fso.GetFileName("C:\Excel Files\Dashboards.xlsm") Once the object is initialized, use the GetFileName method by providing the path. Set fso = CreateObject("Scripting.FileSystemObject")
#R get file path code
The following code sets FileSystemObject object to fso variable. The important section of the code is the initializing of the object. It uses GetFileName method of FileSystemObject object. Of course, you can use this function in your macros as well. You can use VBA to create your own custom functions which you can use in worksheet as well. Finally, the FIND locates the "*" character, and the MID function parses the file name after the character.Obviously, the last separator's instance is equal to number of separators (2). The outer SUBSTITUTE function replaces the last separator ("\") with "*".The formula subtracts the length of substituted path (1) from original path to find the number of separators.At the inner section, all separators ("\") get replaced with empty strings.
0 kommentar(er)
