Link to home
Start Free TrialLog in
Avatar of Lambel
Lambel

asked on

Excel vba Windows OS File search

I need to search a folder and create an array of all the filenames in the folder.  I thought it was working, but noticed it always skips the same file :
"RC-T Report OH Count Pool 20110331200006 .xls"
I attached a printscreen of the immediate window results.  It shows the results of looping
through the array with debug.print to see all the filenames it captured.

Is it a problem with a similarly named file:
"RC-T Report OH Count 20110331200006 .xls"

' File Folder Search
 ' Returns a string array of the input filenames
  Function fileList(strInputFolder As String, Optional strfilter As String = "*.*") As Variant

    Dim strHolder As String
    Dim i As Integer
    Dim strTemp As String
    Dim strTemp2 As String
    Dim arrayFiles() As String
    

    If Right$(strInputFolder, 1) <> "\" Then
        strInputFolder = strInputFolder & "\"
        strTemp = Dir(strInputFolder & strfilter)
    Else
        strTemp = Dir(strInputFolder & strfilter)
    End If
    ' make sure there are files in the folder
    If strTemp = "" Then
        MsgBox "The folder is empty.", vbCritical, "File Search"
        Exit Function
    End If

   'load filenames into array
      i = 0
      Do While strTemp <> ""
        ReDim Preserve arrayFiles(i) As String
        arrayFiles(i) = strTemp
        i = i + 1
        strTemp = Dir
        Debug.Print (strTemp)
      Loop
      
     fileList = arrayFiles

End Function

Open in new window

I looped through the array with debug.print, and it captured all the rest of the files: 
Is it a problem with the similarly named file: RC-T Report OH Count 20110331200006 .xls ?
RC-T Report Schedule U20110331200006 .xls
RC-T Report 959-619225 20110331200006 .xls
RC-T Report Active RL 20110331200006 .xls
RC-T Report 815-618839 20110331200006 .xls
RC-T Report 809-618824 20110331200006 .xls
RC-T Report OH Count 20110331200006 .xls
RC-T Report 809-618838 20110331200006 .xls
RC-T Report 827-618877  20110331200006 .xls
RC-T Report 936-619173 20110331200006 .xls
DCS RC-T Results 20110331 (version 1).xls

Open in new window

Avatar of Dale Fye
Dale Fye
Flag of United States of America image

have you checked the file attributes.

You might want to try:

strTemp = Dir(,vbHidden+vbHidden+vbReadOnly)
oops, that should have been:

strTemp = Dir(,vbNormal+vbHidden+vbReadOnly)
Avatar of Lambel
Lambel

ASKER

@ fyed:  I tried that in the loop - still doesn't pull the file:
RC-T Report OH Count Pool 20110331200006 .xls

I tried removing the file with the similar name: "
RC-T Report OH Count 20110331200006 .xls"
And then I re-ran the loop - it still did not pull the OH Count "Pool" file.  I've attached a printscreen of the explorer window.  It shows all the files in the folder.  I can't see any reason it wouldn't pull that file.
Doc1.doc
Have you right clicked on the file and checked its properties?  That is the only thing I can think of for why the Dir() function would not pull this one up.
ASKER CERTIFIED SOLUTION
Avatar of Lambel
Lambel

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Lambel

ASKER

fyed-  Thank you for your comments.

Lynn