How to get Relative path from FileDialog Box

[BigKeeya]

When I use a FileDialog Box to return the name of a file, it returns the absolute file name (e.g., C:/documents and setting/usernmae/desktop/filename.xls).


Is there a way to retunr an relative file name, so that my VBA program can find the correct file on any computer, not just mine.

 

[PHV]

Have a look at the Mid and InStrRev functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Here is a function from John Walkenbach, on his "The Spreadsheet Page" website:

http://www.j-walk.com/ss/excel/tips/tip54.htm

Function FileNameOnly(ByVal PathName As String) As String
' Returns a filename from a path/filename string
Dim i As Integer
Dim Length As Integer
Dim Temp As String

   Length = Len(PathName)
   Temp = ""
   For i = Length To 1 Step -1
     If Mid$(PathName, i, 1) = Application.PathSeparator Then
       FileNameOnly = Temp
       Exit Function
     End If
     Temp = Mid$(PathName, i, 1) & Temp
   Next i
   FileNameOnly = PathName
   
End Function

Regards,
Mike

 

[PHV]

Mike, here an oneliner version of your post:
FileNameOnly = Mid(PathName, 1 + InStrRev(PathName, Application.PathSeparator))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rmikesmith]

Thanks, PH

I suspect John wrote that prior to XL2000. Yours is much more compact.

Regards,
Mike