Hi
I'm trying to find a set of files that contain data I want to manipulate. End goal is to collate all the data from each file matching my criteria. However, I'm struggling at the moment as the function I'm using to search is crashing out.
I believe that it's failing due to either a file being corrupt or the path & filename being searched being too long for VBA / Excel to handle.
However - I am 99.99% confident that it will never fail for the files in the folder that I'm looking for. I've done some reasonably exhaustive testing and it's only ever failed on files in folders I don't care about. So I'm trying to refine the search to only look for the matching filename (*Finance Tracker*.xlsm) in a specific subfolder.
Unfortunately, because I've used code recommended to other people for recursive searching, I'm struggling to identify where I need to amend it to get the output I want.
In an ideal world I'd like to be able to use a wildcard value in the search path underlined in the code so the end of it reads \Projects\*\Controls\ and therefore skips searching all the other subfolders of the \*\ level (which is the project names).
Hope that makes sense... can anyone help me?
The code I'm using is:
I'm trying to find a set of files that contain data I want to manipulate. End goal is to collate all the data from each file matching my criteria. However, I'm struggling at the moment as the function I'm using to search is crashing out.
I believe that it's failing due to either a file being corrupt or the path & filename being searched being too long for VBA / Excel to handle.
However - I am 99.99% confident that it will never fail for the files in the folder that I'm looking for. I've done some reasonably exhaustive testing and it's only ever failed on files in folders I don't care about. So I'm trying to refine the search to only look for the matching filename (*Finance Tracker*.xlsm) in a specific subfolder.
Unfortunately, because I've used code recommended to other people for recursive searching, I'm struggling to identify where I need to amend it to get the output I want.
In an ideal world I'd like to be able to use a wildcard value in the search path underlined in the code so the end of it reads \Projects\*\Controls\ and therefore skips searching all the other subfolders of the \*\ level (which is the project names).
Hope that makes sense... can anyone help me?
The code I'm using is:
Code:
Sub Get_Weekly_Costs()
Dim wbSource As Workbook
Dim wbDest As Workbook
Dim i As Integer
Dim colFiles As New Collection
Dim vFile As Variant
Dim FoundCell As Range
Dim LastRow As Integer
Dim Project_Name As String
Application.ScreenUpdating = False
ThisWorkbook.Sheets("Weekly Costs").Select
Set wbDest = ActiveWorkbook
RecursiveDir colFiles, [U]"C:\Users\" & Environ$("Username") & "\<Company>\Projects\"[/U], "*Finance Tracker*.xlsm", True
i = 2
LastRow = 1
For Each vFile In colFiles
Debug.Print vFile
Workbooks.Open Filename:=vFile, ReadOnly:=True
//Do some stuff to the file that's not important right now
ActiveWorkbook.Close savechanges:=False
i = i + 1
Next vFile
Application.ScreenUpdating = True
End Sub
Public Function RecursiveDir(colFiles As Collection, _
strFolder As String, _
strFileSpec As String, _
bIncludeSubfolders As Boolean)
Dim strTemp As String
Dim colFolders As New Collection
Dim vFolderName As Variant
'Add files in strFolder matching strFileSpec to colFiles
strFolder = TrailingSlash(strFolder)
'Debug.Print strFolder
strTemp = Dir(strFolder & strFileSpec)
Dim v As Variant
Do While strTemp <> vbNullString
colFiles.Add strFolder & strTemp
strTemp = Dir
Loop
If bIncludeSubfolders Then
'Fill colFolders with list of subdirectories of strFolder
strTemp = Dir(strFolder, vbDirectory)
'Debug.Print "Temp " & strTemp
Do While strTemp <> vbNullString
If (strTemp <> ".") And (strTemp <> "..") Then
Debug.Print "strTemp = " & strTemp
If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0 Then
colFolders.Add strTemp
For Each v In colFolders
Debug.Print v
Next v
End If
End If
strTemp = Dir
'Debug.Print "Temp2" & strTemp
Loop
'Call RecursiveDir for each subfolder in colFolders
For Each vFolderName In colFolders
'Debug.Print vFolderName
Call RecursiveDir(colFiles, strFolder & vFolderName, strFileSpec, True)
Next vFolderName
End If
End Function
Public Function TrailingSlash(strFolder As String) As String
If Len(strFolder) > 0 Then
If Right(strFolder, 1) = "\" Then
TrailingSlash = strFolder
Else
TrailingSlash = strFolder & "\"
End If
End If
End Function
