Sleeper: Find and Open Files

[ylDevo]

There are 3 pieces to my puzzle.

1. Allow the user to set a base directory from which to search through sub-directories looking for .xls files.

2. If an .xls file is found, open that file.

3. Run the code that harvests the data from this file.

1 and 3 are done, but I cannot figure out how to open the file once it is found in step one. Here's the base directory search:


Set fs = Application.FileSearch
With fs
.LookIn = BaseDir
If .FileName = "*.xls" Then
' open the file
CashPosActuals
Else
MsgBox "There were no files found."
End If
End With


Thanks in advance for the help.

Chris

[Anne Troy]

Must open?
Try this?
http://www.vbaexpress.com/kb/getarticle.php?kb_id=9

[ylDevo]

Maybe it's because tomorrow is a holiday, but I'm not getting this to work. Also, this appears to open excel files in one folder and I have 26 sub-directories that must be searched.

Sorry for being slow this afternoon.

[Ken Puls]

Hi there,

What about: Process All Documents in a Specified Directory Folder Including Subfolders (http://www.vbaexpress.com/kb/getarticle.php?kb_id=76). It's a Word entry, but you may be able to adapt it easily.

OR:

Loop through all/selected files in a folder and its subfolders (http://www.vbaexpress.com/kb/getarticle.php?kb_id=245)

Let us know if you need help, and how far you get....

** Updated **

Based on the second link I provided, give this a shot:


Option Explicit
Option Compare Text

Sub OneType()
Const MyPath = "c:" ' Set the path, but leave off last "\"
Const FileType = "*.xls"
ProcessFiles MyPath, FileType
End Sub

Sub ProcessFiles(strFolder As String, strFilePattern As String)
Dim strFileName As String
Dim strFolders() As String
Dim iFolderCount As Integer
Dim i As Integer
Dim wb As Workbook
'Collect child folders
strFileName = Dir$(strFolder & "\", vbDirectory)
Do Until strFileName = ""
If (GetAttr(strFolder & "\" & strFileName) And vbDirectory) = vbDirectory Then
If Left$(strFileName, 1) <> "." Then
ReDim Preserve strFolders(iFolderCount)
strFolders(iFolderCount) = strFolder & "\" & strFileName
iFolderCount = iFolderCount + 1
End If
End If
strFileName = Dir$()
Loop
'process files in current folder
strFileName = Dir$(strFolder & "\" & strFilePattern)
Do Until strFileName = ""
'Do things with files here
Set wb = Workbooks.Open(strFileName)
'Do something with that Workbook, insert whatever you want to do here
Debug.Print wb.Name
'You can save it, if you like, here it's not saved
wb.Close False
strFileName = Dir$()
Loop
'Look through child folders
For i = 0 To iFolderCount - 1
ProcessFiles strFolders(i), strFilePattern
Next i
End Sub

[ylDevo]

I'll get to work on these and see what happens. Thanks again!