How list files from directory in listbox with "LIKE" 2

[jadeiitti]

Hi,
I’m looking for some piece of code to list files in directory but with some filter.
I must verify if I have simile files in directory.
I tried to use the code from

http://allenbrowne.com/ser-59.html

but it’s not working like I would like.
I tried get in “listbox” (lstFileList) files contain number from [Numero] field (Numero field is numeric e.g. 0022038).
The name of the file could be a number e.g.: 0022038.doc
or mist COR-1LD-1871-1226-0022038.doc

If they exist, I would like only them, to show in listbox.
If possible only file names without full directory path.

Do you know how build a string with “LIKE” option to do the same?

This is part of code I tried to use to check if files are in directory:

Private Sub Form_Current()
Dim nomefnr, nomefnome, nomefpdf, cartella, Categoria, nomefilenumero, nomefilenome, nomefilepdf As Variant
Dim criteri As String
Dim, MyPath As String

'Prova di verificare il file

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
MyPath = cartella & "\AD\DOC\" & Categoria & "\"

'''Verifico se ci sono i file nella directory
'Call ListFiles(MyPath, , "WHERE Me!Numero Like "*" & Format(Me!Numero, "0000000") & ".doc", Me.lstFileList)
'Call ListFiles(MyPath, , "*[! 0-9A-Z]*" & Format(Me!Numero, "0000000") & ".doc", Me.lstFileList)

End Sub

Call ListFiles is function from

http://allenbrowne.com/ser-59.html

put in Modules.
---
I tried also so to make visible button to open a file (please don’t laugh) but this could be confuse if there are more file with same number. So I will go more in direction of listbox:

'''Verifico se il file con nome uguale al numero esiste
'''Se esiste, rendo visibile l'icona di explorer

criteri = "*" & (Format(Me!Numero, "0000000") & ".*")
'criteri = Trim$(Format(Me!Numero, "0000000") & ".*")
'criteri = "Like '*' " & Format(Me!Numero, "0000000") & ".doc"
strEndFilePath = cartella & "\AD\DOC\" & Categoria

strLook = Dir(strEndFilePath & "\" & criteri)
If strLook = nomefilenumero Then
'If Dir(nomefpdf) = nomefilepdf Then
'If strLook <> "" Then
'ProcessFile = "File Present."
Me!cmd_file_con_nome.Visible = True
Else: Me!cmd_file_con_nome.Visible = False
End If

Hope what I said make sense.
I will appreciate your help.

 

[JBinQLD]

G'day,

Allen brown gives an example you could use:

To limit the results to zip files:
    Call ListFiles("C:\Data", "*.zip")

So, you can adapt this to:

    Call ListFiles(MyPath,"*" & Format(Me!Numero, "0000000") & "*.doc")

This would be the same as typing "*0022038*.doc" into a windows search.

Don't really follow second part of problem mate,

JB

 

[jadeiitti]

Hi,
Thanks JBinQLD.

1.
When I used Call ListFiles(MyPath,"*" & Format(Me!Numero, "0000000") & "*.doc")
I don't get list in listbox at all.

When I used Call ListFiles(MyPath,"*" & Format(Me!Numero, "0000000") & "*.doc", Me.lstFileList)
I get error "Run-time error '13': Type mismatch.

2.
In the second part of message I tried do the same what with Call function. Only I tried to show button command to open exist file. Problem was that if there are more files with e.g. number 0022038 I can't open it with only one button. So I must have a list of files which contain number e.g. 0022038. But how I can show this in listbox?

'''Verify that file exist
'''If exist show button
'''If it's only one file this could works
'''But for more files with same arg. this code is unusfull
Dim cartella, Categoria As Variant
Dim criteri, strFilePath, strLook As String

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
criteri = "*" & (Format(Me!Numero, "0000000") & ".*")
strFilePath = cartella & "\AD\DOC\" & Categoria & "\"

strLook = Dir(strEndFilePath & criteri)
If strLook = nomefilenumero Then
If strLook <> "" Then
Me!cmd_file_con_nome.Visible = True
Else: Me!cmd_file_con_nome.Visible = False
End If

For open a file I use:
<code>
Private Sub cmd_file_con_nome_Click()

On Error GoTo Err_cmd_file_con_nome_Click
Dim appl, nomef, cartella, Category, nomev As Variant
Dim nomenr As Variant


cartella = Application.CurrentProject.Path
Category = Me!Categoria

'''If it's only one file it's working
nomef = cartella & "\AD\DOC\" & Category & "\" & Format(Me!Numero, "0000000") & ".doc"
'''Or in case is not only number but
'''e.g. COR-1LD-1871-1226-0022038.doc
'nomef = cartella & "\AD\DOC\" & Category & "\" & "*" & Format(Me!Numero, "0000000") & ".doc"

Shell "rundll32.exe url.dll,FileProtocolHandler " & nomef, vbMaximizedFocus

Exit_cmd_file_con_nome_Click:
Exit Sub

Err_cmd_file_con_nome_Click:
MsgBox Err.Description
Resume Exit_cmd_file_con_nome_Click

End Sub
</code>

3.
I can't use:
To limit the results to zip files:
Call ListFiles("C:\Data", "*.zip")
because I don’t want all files (*.doc, zip ecc. ). I want only files with number in [Numero] field.

 

[JBinQLD]

Hi,

Take out error handling for now and post which line is producing the type mismatch error pls mate

3. This was just highlighting Allens own example, showing that the function is designed to have an optional criteria field. We merely adapted that to have your search criteria rather than *.zip. No drama.

JB

 

[jadeiitti]

Hi,
After the night error is changed mind and is changed.
Now it's error '94' Invalid use of Null.

It's producing line:
Call ListFiles(strPath, strFileSpec, Me.lstFileList)

Debug is showing that:
strPath = "fullpath" (OK)
strFileSpec = "*0022038*.doc"
Me.lstFileList = Null


--code--
Private Sub Form_Current()
Dim cartella, Categoria As Variant
Dim strPath As String, strFileSpec As String

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
strPath = cartella & "\AD\DOC\" & Categoria & "\"

'''Verifico se ci sono i file nella directory
'''Verify if files in directory are

strFileSpec = "*" & Format(Me!Numero, "0000000") & "*.doc"
Call ListFiles(strPath, strFileSpec, Me.lstFileList)

End Sub
--end code--
(PS I changed MyPath to strPath and added strFileSpec)

From 'Sub Form_Current()' I copied only part of code which will help me to see these file names.
There are a little more but maybe is not necessary to show it now.

On the form I have listbox (lstFileList) where:
Name= lstFileList
ControlSource = is empty ‘Unbound’
Visible= Yes
RowSource= empty
RowSourceType = Value List

Thanks You are ready to help to find search criteria.

 

[PHV]

Call ListFiles(strPath, strFileSpec, [!], [/!]Me.lstFileList)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jadeiitti]

Thanks, Thanks, Thanks
It's works.

Now,

1. I add .RowSource = "" to clean ListBox for evere record.
So when I pass to next record I have new list of files.

Private Sub Form_Current()
Dim cartella, Categoria As Variant
Dim strPath As String, strFileSpec As String

cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
strPath = cartella & "\AD\DOC\" & Categoria & "\"

'''Verifico se ci sono i file nella directory
'''Verify if files in directory are

lstFileList.RowSource = ""
strFileSpec = "*" & Format(Me!Numero, "0000000") & "*.doc"
Call ListFiles(strPath, strFileSpec, , Me.lstFileList)

End Sub

2. How I can "trim" result to show only name of files and not full path?

Thanks for help.

 

[JBinQLD]

jade,

strTrimmed = Right(strFullPath, InStrRev(strFullPath, "\") + 1)

you can use [ code ] and [ / code ] without spaces to put your code in blocks for readability

JB

 

[JBinQLD]

PS. You can thank PHV for spotting the missing parameter by clicking on "Thank PHV for this valuable post", it's always appreciated

 

[jadeiitti]

Hi,
Sorry stupid question but where I put this 'strTrimmed' code to have result in ListBox?

 

[JBinQLD]

Looking at Allens FillDir function, it's even easier - Remove:

colDirList.Add [red]strFolder & [/red] strTemp

JB

 

[jadeiitti]

Thanks JbinQLD & PHV

Now it's works I would like.
I added to ListBox also DblClick function so I can open these files.

So final code to see file with value using code of Allen Brown:

Purpose: to see if in directory are files with same value from field [Numero].
It’s a possibility to open each file to check which is corrected. After verification, user saves the file in a place prepared to archive the file. User writes file name in field [filename] and changes category in field [Categoria] same with name of place of archive.

Private Sub Form_Current()
Dim cartella, Categoria As Variant
Dim strPath As String, strFileSpec As String

    cartella = Application.CurrentProject.Path
    Categoria = Me!Categoria
    strPath = cartella & "\AD\DOC\" & Categoria & "\"

'''Verify if files in directory are
 
 lstFileList.RowSource = ""
 strFileSpec = "*" & Format(Me!Numero, "0000000") & "*.doc"
 Call ListFiles(strPath, strFileSpec, , Me.lstFileList)

End Sub

The code of Allen Brown changed to show only name of file:

'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colDirList.Add strTemp
        'colDirList.Add strFolder & strTemp  '''original
        strTemp = Dir
    Loop

Added DblClick to ListBox.

Private Sub lstFileList_DblClick(Cancel As Integer)
Dim FileName As String
Dim FilePath As String
Dim FullName As String
Dim cartella As Variant
Dim Categoria As Variant

Me.lstFileList.Requery
cartella = Application.CurrentProject.Path
Categoria = Me!Categoria
FileName = lstFileList
FilePath = cartella & "\AD\DOC\" & Categoria & "\"
FullName = FilePath & FileName

Shell "rundll32.exe url.dll,FileProtocolHandler " & FullName, vbMaximizedFocus

End Sub

Thanks very much for help.