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"
"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
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
oops, that should have been:
strTemp = Dir(,vbNormal+vbHidden+vbR eadOnly)
strTemp = Dir(,vbNormal+vbHidden+vbR
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
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
fyed- Thank you for your comments.
Lynn
Lynn
You might want to try:
strTemp = Dir(,vbHidden+vbHidden+vbR