View Full Version : Sleeper: Find and Open Files
ylDevo
03-24-2005, 03:06 PM
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
03-24-2005, 03:07 PM
Must open?
Try this?
http://www.vbaexpress.com/kb/getarticle.php?kb_id=9
ylDevo
03-24-2005, 03:41 PM
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
03-24-2005, 04:13 PM
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
03-24-2005, 04:33 PM
I'll get to work on these and see what happens. Thanks again!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.