Hi,
I have the below code that, when pointed at a particular folder, will capture the following data in my check tab : file name, number of rows, number of columns. The final part i need help with is to find a header, say its "value", and sum the column, posting the total adjacent to each file name starting in cell d8. Code below. Any ideas how to do this easily?
I have the below code that, when pointed at a particular folder, will capture the following data in my check tab : file name, number of rows, number of columns. The final part i need help with is to find a header, say its "value", and sum the column, posting the total adjacent to each file name starting in cell d8. Code below. Any ideas how to do this easily?
VBA Code:
Sub CollectData()
Dim fso As Object, xlFile As Object
Dim sFolder$
Dim r&, j&, k&
'*
Sheets("Check").Activate
Range("F8:I50").ClearContents
Range("A8:D50").Copy Range("F8")
Range("A8:D50").ClearContents
'*
With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
.InitialFileName = ThisWorkbook.path
If .Show Then sFolder = .SelectedItems(1) Else Exit Sub
End With
Set fso = CreateObject("Scripting.FileSystemObject")
For Each xlFile In fso.GetFolder(sFolder).Files
With Workbooks.Open(xlFile.path, Password:="password")
With .Sheets(1)
j = .Cells(.Rows.Count, 1).End(xlUp).Row
k = .Cells(1, Sheet1.Columns.Count).End(xlToLeft).Column
End With
.Close False
End With
r = r + 1
Cells(r + 7, 1).Value = xlFile.Name
Cells(r + 7, 2).Value = j
Cells(r + 7, 3).Value = k
ActiveWorkbook.Save
Next
End Sub