Excel vba Windows OS  File search

Posted on 2011-05-06
Medium Priority
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
  • 3
  • 3
LVL 49

Expert Comment

by:Dale Fye
ID: 35707474
have you checked the file attributes.

You might want to try:

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

Expert Comment

by:Dale Fye
ID: 35707478
oops, that should have been:

strTemp = Dir(,vbNormal+vbHidden+vbReadOnly)

Author Comment

ID: 35707724
@ 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.
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

LVL 49

Expert Comment

by:Dale Fye
ID: 35707812
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

Lambel earned 0 total points
ID: 35708546
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

ID: 35735923
fyed-  Thank you for your comments.


Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

850 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