How to display the file name from the path name in

Question

I have written a code which will extract all the sub folders which is present inside a particular folder/Directory. Here is the code.

ComboBox10.List = Split(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\inkapb\AppData\Local\Temp\EPC AutoTool\Projects\*."" /b /s").stdout.readall, vbCrLf)

Here in the above code all the sub folder path is getting populated instead of the subfolder name. Can any one help me to achieve my requirement

Answer 1

In your command button code you could use something like this.

When I use this, just the folder names show up, not the path.

I used C:\ as the main folder in this example.

Private Sub CommandButton1_Click()

    Dim fs, f, f1, fc, s
    Dim folderspec

    folderspec = "C:\"

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.SubFolders

    ComboBox1.Clear

    For Each f1 In fc

        ComboBox1.AddItem f1.Name

    Next f1

    ComboBox1.Activate

    Application.SendKeys "^{F4}"

End Sub

Once clicked this will be the result

When you do select a sub-folder, then second combobox will show the files.

Private Sub ComboBox1_Change()

    Dim fs, f, f1, fc, s
    Dim folderspec

    folderspec = "C:\" & ComboBox1

    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(folderspec)
    Set fc = f.Files

    ComboBox2.Clear

    For Each f1 In fc

        ComboBox2.AddItem f1.Name

    Next f1

    ComboBox2.Activate

    Application.SendKeys "^{F4}"

End Sub

Those results will look like this

Answer 2

I have a different piece of suggestion to serve your requirement.

Sub AddHighPlusOne()
Dim cb As ComboBox
Set cb = ActiveSheet.ComboBox1
Dim objFS As Object
Dim folders As Object
Set objFS = CreateObject("Scripting.FileSystemObject")
Set folders = objFS.GetFolder(Application.ActiveWorkbook.Path)
cb.Clear
For Each Folder In folders.SubFolders
  cb.AddItem (Folder.Name)
Next
End Sub

Answer 3

You could try a Replace() function with the full path name. Hence:

pathName = "C:\Users\inkapb\AppData\Local\Temp\EPC AutoTool\Projects\"
ComboBox10.List = Split(Replace(CreateObject("wscript.shell").exec("cmd /c Dir ""C:\Users\inkapb\AppData\Local\Temp\EPC AutoTool\Projects\*."" /b /s").stdout.readall, vbCrLf), pathName, "")