To get rid of some contents from the path location string

[plasma33]

hi guys,

i am trying to convey something in the message box..which is as follows:

        ' Promt
        strPrompt = ".xls file location titled 'Test.xls' can be found in location " & filespec & "Test.xls" & "." & vbCrLf & _
        "Please click 'OK' or 'Cancel' to exit this message box."
        
        ' Dialog's Title
        strTitle = "Test.xls Location"
        
        'Display MessageBox
        iRet = MsgBox(strPrompt, vbOK, strTitle)
        
        ' Check pressed button
        If iRet = vbOK Then
            Range("A9").Select
        Else
            ' do nothing
        End If

filespec is "C:\something_else.xls"

so the message pops up as ".xls file location titled 'Test.xls' can be found in location C:\something_else.xlsTest.xls. Please click 'OK' or 'Cancel' to exit this message box." now

i need the message to pop up as ".xls file location titled 'Test.xls' can be found in location C:\Test.xls. Please click 'OK' or 'Cancel' to exit this message box."

i need to get rid of "something_else.xls" from the file location path.

any suggestion guys..

thanks in advance guys..

best regards
plasma33

[Leith Ross]

Hello plasma33,

Herr is one to split the filespec up...

Sub SplitFilespec()

    Dim File        As String
    Dim Filespec    As String
    Dim Folder      As String
    Dim n           As Long
    
        Filespec = "C:\test.xls"
        
        n = InStrRev(Filespec, "\")
        
        Folder = Left(Filespec, n)
        File = Right(Filespec, Len(Filespec) - n)
        
End Sub

[plasma33]

Hi Leith,

thank you for your kind reply.

my filespec will always be a dynamic string as its chosen by user.

what if my filespec is C:\Users\ExcelForum\Desktop\Something.exe

i want to get rid of 'something.exe'. this can be anything from something.exe to something.xls to something.txt

thanks again, Leith.

best regards
plasma33

[Leith Ross]

Hello plasma33,

The macro divides the file specification into two parts: The folder path and the file name. They are saved in the variables Folder and File respectively.

[plasma33]

Hi Leith,

Thank for your kind reply.

Your code works fine for me now.

I found a code on MSDN that does the trick too. Please find the code below:

Sub Path_Location()

Dim Filespec As String
Dim Drive_Path As String
Dim File_Path As String

Filespec = "C:\Users\ExcelForum\Desktop\Something.exe"

        Drive_Path = GetAName(Filespec)
        
        File_Path = ShowAbsolutePath(Drive_Path & ".")

        ' Promt
        strPrompt = ".xls file location titled 'Test.xls' can be found in location " & File_Path & "\" & "Test.xls" & "." & vbCrLf & _
        "Please click 'OK' or 'Cancel' to exit this message box."
        
        ' Dialog's Title
        strTitle = "Test.xls Location"
        
        'Display MessageBox
        iRet = MsgBox(strPrompt, vbOK, strTitle)
        
        ' Check pressed button
        If iRet = vbOK Then
            Range("A9").Select
        Else
            ' do nothing
        End If

End Sub

Function GetAName(Filespec As String)
   Dim fso
   Set fso = CreateObject("Scripting.FileSystemObject")
   GetAName = fso.GetDriveName(Filespec)
End Function

Function ShowAbsolutePath(Filespec As String)
   Dim fso
   Set fso = CreateObject("Scripting.FileSystemObject")
   ShowAbsolutePath = fso.GetAbsolutePathName(Filespec)
End Function

thanks again, Leith.

best regards
plasma33