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
Bookmarks