I've got a big folder with weekly reports in a format "YYYY-MM.DD FILE NAME.xlsx". Each report has a worksheet named "DD report". There are 2.5 years of weekly report. I need to analyse them and I want to copy the "DD report" worksheet from each into one master file under the date name (so "DD report" from the example will become YYYYMMDD) . But it looks like quite an endeavour for me. So far I have managed to come up with a code which apparently goes through all subfolders and finds the matching files (but it does not work if I put "*FILE NAME.xlsx" as a pattern but looks like working with a different file like "MTM*.*". Now I am stuck as I have no idea how to basically open each file, copy the sheet and rename it. I can probably manage to copy and rename operations but I do not know how to start with opening each matching file. Any help please?
VBA Code:
Sub FindPatternMatchedFiles()
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
Dim objRegExp As Object
Set objRegExp = CreateObject("VBScript.RegExp")
objRegExp.Pattern = "MTM*.*"
objRegExp.IgnoreCase = True
Dim colFiles As Collection
Set colFiles = New Collection
Dim folderName As String
Dim folder As Integer
'Set the folder name to a variable
folder = Application.FileDialog(msoFileDialogFolderPicker).Show
If folder <> 0 Then
folderName = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
End If
RecursiveFileSearch folderName, objRegExp, colFiles, objFSO
For Each f In colFiles
Debug.Print (f)
'Insert code here to do something with the matched files
Next
'Garbage Collection
Set objFSO = Nothing
Set objRegExp = Nothing
End Sub
Sub RecursiveFileSearch(ByVal targetFolder As String, ByRef objRegExp As Object, _
ByRef matchedFiles As Collection, ByRef objFSO As Object)
Dim objFolder As Object
Dim objFile As Object
Dim objSubFolders As Object
'Get the folder object associated with the target directory
Set objFolder = objFSO.GetFolder(targetFolder)
MsgBox targetFolder
'Loop through the files current folder
For Each objFile In objFolder.Files
If objRegExp.test(objFile) Then
matchedFiles.Add (objFile)
End If
Next
'Loop through the each of the sub folders recursively
Set objSubFolders = objFolder.SubFolders
For Each objSubfolder In objSubFolders
RecursiveFileSearch objSubfolder, objRegExp, matchedFiles, objFSO
Next
'Garbage Collection
Set objFolder = Nothing
Set objFile = Nothing
Set objSubFolders = Nothing
End Sub
