Returning subfolder name with path name

[woolls01]

I am using the following code

Sub Ck()

Dim strStartPath As String

strStartPath = "d:\workpack\rr a"
ListFolder strStartPath

End Sub
Sub ListFolder(sFol derPath As String)

Dim FS As New FileSystemObjec t
Dim FSfolder As Folder
Dim subfolder As Folder
Dim i As Integer

Set FSfolder = FS.GetFolder(sF olderPath)

For Each subfolder In FSfolder.SubFol ders
DoEvents
i = i + 1
'added this line
Cells(i, 1) = subfolder
'commented out this one
'Debug.Print subfolder
Next subfolder

Set FSfolder = Nothing


End Sub

to return the subfolder names into cells in excel, problem is this also returns the path (ie D:\workpack\rra \example1), I only want the subfolder name, how can I do this?

[kadghar]

I am using the following code

(...)

Sub ListFolder(sFol derPath As String)

(...)

to return the subfolder names into cells in excel, problem is this also returns the path (ie D:\workpack\rra \example1), I only want the subfolder name, how can I do this?

the easiest way, since you have sFolderPath:
cells(i,1) = mid(subfolder, len(sFolderPath )+1)

HTH

[woolls01]

I have no idea what that means but it did the trick so thanks

[kadghar]

I have no idea what that means but it did the trick so thanks

mid will return a substring from the original string, so if you have

str1 = "hello world"

mid(str1, 2,4) = "ello" where the first parameter is the string, the second is the starting position and the third is the length. The third one is optional, and will give you the rest of the string as default.

HTH

[ubentook]

Try...
Cells(i, 1) = subfolder.Name


I am using the following code
-snip-
to return the subfolder names into cells in excel, problem is this also returns the path (ie D:\workpack\rra \example1), I only want the subfolder name, how can I do this?

[ubentook]

Try...
Cells(i, 1) = subfolder.Name

I am using the following code
-snip-
to return the subfolder names into cells in excel, problem is this also returns the path (ie D:\workpack\rra \example1), I only want the subfolder name, how can I do this?