Hello Peeps,

I have the following code from a google search but i would like to know if it can be modified to peform the same fuction on multipul cells (the number of cells will vary).

I use Application.GetOpenFilename to get the full path in to Row 1 then the below Function twice with a slight change to seperate the path and file name out in to Cells

What i need is a way to do this on multipul paths e.g

A1 = C:\Rich\Test\rich.xmsl
A2 = C:\Testing\this\really\long\folder\name\test.xmsl
A3 = C:\Blah\blah.xmsl

You get the idea at the end of the macro i would like

A1 = C:\Rich\Test\rich.xmsl
B1 = C:\Rich\Test\
C1 = rich.xmsl

A2 = C:\Testing\this\really\long\folder\name\test.xmsl
B2 = C:\Testing\this\really\long\folder\name\
C2 = test.xmsl

A3 = C:\Blah\blah.xmsl
B3 = C:\Blah\
C3 = blah.xmsl


Any Help Greatly Appreciated.

Function getafilename() As String

Dim someFileName As Variant
Dim folderName As String
Dim i As Integer
Const STRING_NOT_FOUND As Integer = 0

'select a file using a dialog and get the full name with path included
someFileName = Cells(1, 5) '("Text Files (*.txt), *.txt")

If someFileName <> False Then

    'strip off the folder path
    folderName = vbNullString
    i = 1

    While STRING_NOT_FOUND < i
        i = InStr(1, someFileName, "\", vbTextCompare)  'returns position of the first backslash "\"
        If i <> STRING_NOT_FOUND Then
            folderName = folderName & Left(someFileName, i)
            someFileName = Right(someFileName, Len(someFileName) - i)
        Else 'no backslash was found... we are done
            getafilename = someFileName

        End If
    Wend

Else
    getafilename = vbNullString
End If

End Function