Removing XLS Extension from Combo Box. Please help!!!

[Ninjataktikz]

Hi guys here is the full code.

Option Explicit


Public Function strRootFolder() As String
strRootFolder = ThisWorkbook.Sheets("Config").Cells(3, 2)
End Function

Private Sub cbofolder_Change()
  Dim FSO As Object
    Dim oFile As Object
    Dim strTemp As String
    

    If cbofolder.ListIndex > -1 Then
        Set FSO = CreateObject("Scripting.FileSystemObject")
        
        For Each oFile In FSO.GetFolder(strRootFolder & "\" & cbofolder.Text).Files
            If InStr(1, FSO.GetExtensionName(oFile.Path), "xls", vbTextCompare) > 0 Then
                strTemp = strTemp & "|~|" & oFile.Name
            End If
        Next oFile
        
        With cbofile
            .Clear
            .List = Split(Mid(strTemp, 4), "|~|")
        End With
        
        Set FSO = Nothing
        Set oFile = Nothing
    End If
    
End Sub

Private Sub cbofolder_GotFocus()
Dim FSO As Object
    Dim oFolder As Object
    Dim strTemp As String

    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    For Each oFolder In FSO.GetFolder(strRootFolder).SubFolders
        strTemp = strTemp & "|~|" & oFolder.Name
    Next oFolder
    
    cbofolder.List = Split(Mid(strTemp, 4), "|~|")
    
    Set FSO = Nothing
    Set oFolder = Nothing
    
End Sub

Private Sub CMDCLICK_Click()

 Dim wb3 As Workbook
    
    Dim ws As Worksheet
    Dim filer As String
    
    Application.ScreenUpdating = False

    
    If cbofolder.ListIndex = -1 Then
        MsgBox "Must select a folder", , "Open Error"
        Exit Sub
    End If
    
    If cbofile.ListIndex = -1 Then
        MsgBox "Must select a file", , "Open Error"
        Exit Sub
    End If
    
    filer = strRootFolder & "\" & cbofolder.Text & "\" & cbofile.Text
    Set wb3 = Excel.Workbooks.Open(filer)
     
     
     
    For Each ws In ThisWorkbook.Sheets
    If ws.Name = "Defects" Or ws.Name = "Releases" Then
        Application.DisplayAlerts = False
        ws.Delete
        Application.DisplayAlerts = True
    End If
    Next
    
    
  wb3.Sheets("Defects").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
  wb3.Sheets("Releases").Copy after:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)
    
    
    
    wb3.Close False
    Set wb3 = Nothing
    
    
    If Sheets("ReleaseSelector").Visible = False Then
    Sheets("ReleaseSelector").Visible = True
    Sheets("ReleaseSelector").Move after:=ActiveWorkbook.Sheets(1)
    Sheets("ReleaseSelector").Activate
    End If
    ThisWorkbook.Sheets("Releases").Visible = False
    Sheets("ReleaseSelector").Activate
    
    
    Application.ScreenUpdating = True



End Sub

This code is pulling all folders from a root folder and the files that are inside the folders in 2 drop down menus'. The files are xls files. When the names come up in dropdown menu it has the extension *.xls in the back. I DO NOT want that to show up. How can I remove that extension from the file name in the drop down menu. Please help me.

Thanks,

[Trebor76]

Hi there,

try replacing this line of code...

strTemp = strTemp & "|~|" & oFile.Name

...with this:

strTemp = strTemp & "|~|" & Left(oFile.Name, Len(oFile.Name) - 4)

Regards,

Robert

[patel45]

strTemp = strTemp & "|~|" & left(oFile.Name, InStr(oFile.Name, ".") - 1)