asked on

How not allow file name "THUMBS" appear in a listbox that displays file names

I have a listbox that is populated with file names that exist in a specific directory.

The onopen event code of the form that puts them there is:

Private Sub Form_Open(Cancel As Integer)

    With Me.ListImageFiles 'the name of your listbox
        .RowSource = PopListBox(Me.txtClientImageFolderDataPath)   ' You can leave the ext. out to show all files
        .RowSourceType = "value list"
    End With

    Me.cmdCloseForm.SetFocus

End Sub

Open in new window

Also in the form code is this:

Function PopListBox(strPath As String, Optional FileExtension As String) As String

    Dim MyFso As Object
    Dim MainDir As Object
    Dim MyFile As Object
    Dim LstItems As String, FileProp As String
    Dim NameLength As Integer, InitialNameLength As Integer, TypeLength As Integer, InitialTypeLength As Integer
    Dim x As Integer, y As Integer
    Dim NameSep As Integer, TypeSep As Integer
    Dim Placer As Variant
    On Error Resume Next
    Set MyFso = CreateObject("scripting.FileSystemObject")
    Set MainDir = MyFso.GetFolder(strPath)
    InitialNameLength = 1
    InitialTypeLength = 1
    For Each MyFile In MainDir.Files ' all this in side this loop is to help try and format the columns a bit more uniformly.
        NameLength = Len(MyFile.Name)
        x = IIf(NameLength > InitialNameLength, NameLength, InitialNameLength)
        InitialNameLength = x
        
        TypeLength = Len(MyFile.Type)
        y = IIf(TypeLength > InitialTypeLength, TypeLength, InitialTypeLength)
        InitialTypeLength = y
    Next
    For Each MyFile In MainDir.Files
    Placer = InStr(1, MyFile.Name, ".") - 1
    NameSep = x - Len(MyFile.Name) + 4
    TypeSep = y - Len(MyFile.Type) + 4
    
    If Mid(MyFile.Name, InStr(1, MyFile.Name, ".") + 1) = FileExtension Then
        FileProp = Mid(MyFile.Name, 1, Placer) '& String(NameSep, "_") '& MyFile.Type & String(TypeSep, "_") & MyFile.DateCreated
        LstItems = LstItems & FileProp & ";"
    ElseIf FileExtension = "" Then
        FileProp = Mid(MyFile.Name, 1, Placer) '& String(NameSep, "_") '& MyFile.Type & String(TypeSep, "_") & MyFile.DateCreated
        LstItems = LstItems & FileProp & ";"
    End If
    
    Next
    Debug.Print LstItems
    'Clean up
    Set MyFso = Nothing
    Set MainDir = Nothing
    PopListBox = UCase(LstItems)
    
End Function

Open in new window

The user does a copy/paste to put the image files in the Explorer window but also is a file named "THUMBS". Where is that coming from and how do I prevent it from showing up in the listbox?

--Steve

Mlanda T

Insert this immediately AFTER both line 16 and 25

If MyFile.Name = "THUMBS.DB" Then Continue For

Open in new window

so it will for example look like:

For Each MyFile In MainDir.Files ' all this in side this loop is to help try and format the columns a bit more uniformly.
     If MyFile.Name = "THUMBS.DB" Then Continue For

Open in new window

SteveL13

ASKER

When I copy/paste that after each line you indicated I get an error...

Compile error: Expected: Expression

Mlanda T

You haven't by any chance broken that IF over multiple lines have you? this must be on one line, otherwise you will need to add an End IF

If MyFile.Name = "THUMBS.DB" Then Continue For

Mlanda T

Function PopListBox(strPath As String, Optional FileExtension As String) As String

Dim MyFso As Object
Dim MainDir As Object
Dim MyFile As Object
Dim LstItems As String, FileProp As String
Dim NameLength As Integer, InitialNameLength As Integer, TypeLength As Integer, InitialTypeLength As Integer
Dim x As Integer, y As Integer
Dim NameSep As Integer, TypeSep As Integer
Dim Placer As Variant
On Error Resume Next
Set MyFso = CreateObject("scripting.FileSystemObject")
Set MainDir = MyFso.GetFolder(strPath)
InitialNameLength = 1
InitialTypeLength = 1
For Each MyFile In MainDir.Files ' all this in side this loop is to help try and format the columns a bit more uniformly.
If MyFile.Name = "THUMBS.DB" Then Continue For
NameLength = Len(MyFile.Name)
x = IIf(NameLength > InitialNameLength, NameLength, InitialNameLength)
InitialNameLength = x

TypeLength = Len(MyFile.Type)
y = IIf(TypeLength > InitialTypeLength, TypeLength, InitialTypeLength)
InitialTypeLength = y
Next
For Each MyFile In MainDir.Files
If MyFile.Name = "THUMBS.DB" Then Continue For
Placer = InStr(1, MyFile.Name, ".") - 1
NameSep = x - Len(MyFile.Name) + 4
TypeSep = y - Len(MyFile.Type) + 4

If Mid(MyFile.Name, InStr(1, MyFile.Name, ".") + 1) = FileExtension Then
FileProp = Mid(MyFile.Name, 1, Placer) '& String(NameSep, "_") '& MyFile.Type & String(TypeSep, "_") & MyFile.DateCreated
LstItems = LstItems & FileProp & ";"
ElseIf FileExtension = "" Then
FileProp = Mid(MyFile.Name, 1, Placer) '& String(NameSep, "_") '& MyFile.Type & String(TypeSep, "_") & MyFile.DateCreated
LstItems = LstItems & FileProp & ";"
End If

Next
Debug.Print LstItems
'Clean up
Set MyFso = Nothing
Set MainDir = Nothing
PopListBox = UCase(LstItems)

End Function

SteveL13

ASKER

Add an endif where?

Mlanda T

Did you try the complete method that I posted? did you still get the same error?

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

How not allow file name &quot;THUMBS&quot; appear in a listbox that displays file names

How not allow file name "THUMBS" appear in a listbox that displays file names