Excel vba Windows OS  File search

Posted on 2011-05-06
Last Modified: 2012-05-11
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)
        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)
     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

Question by:Lambel
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    have you checked the file attributes.

    You might want to try:

    strTemp = Dir(,vbHidden+vbHidden+vbReadOnly)
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    oops, that should have been:

    strTemp = Dir(,vbNormal+vbHidden+vbReadOnly)

    Author Comment

    @ 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.
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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.

    Accepted Solution

    Argh!!:  I figured it out.  I put the debug.print in the wrong place and wasn't reading the first element of the array.  

    Author Closing Comment

    fyed-  Thank you for your comments.


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    The password reset disk is often mentioned as the best solution to deal with the lost Windows password problem. In Windows 2008, 7, Vista and XP, a password reset disk can be easily created. But besides Windows 7/Vista/XP, Windows Server 2008 and ot…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now