Open all files, ignore some
Hi. The below code opens all files within a given folder, and consolidates
them into 1 file, 1 sheet. In each of the files to be consolidated, I have
a veryhidden sheet called Test. Is there a way to have the code open files
within the folder and consolidate ONLY the files that contain the sheet
Test? These files are templates that are being emailed in. I just want to
make sure that the file I get back is my file, so the formats are exactly
the same. Also, it would be great to write a log to a text file that
detailed the names of the files that were not consolidated becasue they did
not contain the sheet Test. Thanks in advance for any and all of your help
Dim sFolder As String
Dim wb As Workbook
Dim i As Long
Dim fname As String
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Sheets("Consol").Select
With Application.FileSearch
.NewSearch
.LookIn = "\\server\folder1\folder2\"
.SearchSubFolders = False
.filename = "*.xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() 0 Then
For i = 1 To .FoundFiles.Count
Set wb = Workbooks.Open(filename:=.FoundFiles(i))
fname = Left(ActiveWorkbook.name, Len(ActiveWorkbook.name) - 4)
wb.ActiveSheet.Range("G5:G" & _
wb.ActiveSheet.Range("D65536").End(xlUp).Row).Valu e = fname
wb.ActiveSheet.Range("A5:G" & _
wb.ActiveSheet.Range("D65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Consol").Range("A" & _
ThisWorkbook.Worksheets("Consol").Range("D65536"). End(xlUp).Offset(1,
0).Row).PasteSpecial _
Paste:=xlPasteValues
wb.Close savechanges:=False
fname = Nothing
Next i
Else
MsgBox "Folder " & sFolder & " contains no required files"
End If
End With
Application.ScreenUpdating = True
Application.DisplayAlerts = True
|