CODEWITHSUNDEEP
vbaexcelcombobox
Karthik P B
Karthik P B

Reputation: 237

How to display the file name from the path name in combobox

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)

enter image description here

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

Upvotes: 0

Views: 1494

Answers (3)

Davesexcel
Davesexcel

Reputation: 6984

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

enter image description here

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

enter image description here

Upvotes: 1

Siva
Siva

Reputation: 1149

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

Upvotes: 0

Pierre Chevallier
Pierre Chevallier

Reputation: 754

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, "")

Upvotes: 0

Related Questions