Trouble with ReDim Preserve and array

[rob925]

Relatively newbie to VBA and I've been having problems with this code. It worked at one time, but for the life of me I can't figure out why it can't find the first item in the array.

What I'm trying to do is search a directory for Excel files that match a specific word (this works) and store the file names into an array. I pull the specific word and month from a sheet which I can change from month to month. The files look something like "GM County FFY 2014.xls. After populating the array, I open each workbook, and activate, re-calculate and printout sheets that are named a specific month, such as "Jan1" and "Jan2." Someone helped me write this so I'm not sure if this is even the best way to go about this, but the second part works.

Any suggestions would be helpful. Thanks

strProj = "Fresno" 'as an example
FileSpec = ThisWorkbook.Path & "\" & "*" & strProj & "*" & ".xls"
FileName = Dir(FileSpec)


'/ the first one found
'/ All files are in the same directory
If FileName <> "" Then
FoundFiles = 1
ReDim Preserve FileList(1 To FoundFiles)
FileList(FoundFiles) = FileName
Else
MsgBox "No files found that match " & sFound
Exit Sub
End If


'/ Get other file names that match filter and create array
Do
FileName = Dir
If FileName = "" Then Exit Do
FoundFiles = FoundFiles + 1
ReDim Preserve FileList(1 To FoundFiles)
FileList(FoundFiles) = FileName
Loop


'/ Open workbooks from array
For j = LBound(FileList) To UBound(FileList)

' This is were I'm getting an error message: Sorry, we couldn't find "GM county FFY 2015...Is it possible it was moved, renamed or deleted?
'It just so happens that "GM county FFY 2015.. is the first file in the array.


Workbooks.Open FileName:=FileList(j)
' Set ws_count = to number of sheets - this code works
' The following code works
ws_count = ActiveWorkbook.Worksheets.Count

For k = 1 To ws_count
Set ws = ActiveSheet
If ActiveSheet.Name Like "*Sep*" Then
ActiveSheet.Calculate
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:=False
ActiveSheet.Close
End If
k = k + 1
Next k
Next j

 

[wigi]

Sub loopDir()
[COLOR=#574123]strProj = "Fresno" 'as an example[/COLOR]
[COLOR=#574123]FileSpec = ThisWorkbook.Path & "\*" & strProj & "*.xls"[/COLOR]
[COLOR=#574123]fn = Dir(FileSpec)[/COLOR]
Do While fn <> ""
[COLOR=#333333]  FoundFiles = FoundFiles + 1[/COLOR]
[COLOR=#333333]  ReDim Preserve FileList(1 To FoundFiles)[/COLOR]
[COLOR=#333333]  FileList(FoundFiles) = [/COLOR][COLOR=#574123]ThisWorkbook.Path & "\" & fn[/COLOR]
  fn = Dir
    Loop
End Sub

 

[rob925]

Thank you! Adding that one part with the path did the trick. I wouldn't have thought that that was needed since I was working in one directory. Much appreciated.
Rob

 

[wigi]

The thing is, you need to know what the Dir function returns. It's a filename, not a filename and a directory.