Sum of Files

[K Raghavender rao]

Hi,

i have 300 excel files in each sheet i have some number i want to add them and input their numbers in excel book in below format.

Please find the attached macro sheet for your reference. This macro will do count of columns and rows, As mentioned in the above can i get sum of amount in column E.

Please help me .

Thanks,
K Raghavender rao

 

[Deepak]

Check this....

Sub CountRowsColumnsXLS()
Dim i As Integer
Dim j As Integer
Dim mypath As String
Dim filename As String
Dim shtname As String
Dim myfile As Workbook, strsheet As Worksheet
Dim w As Worksheet
Dim CountRow As Long

Application.ScreenUpdating = False

Set strsheet = ActiveSheet
With strsheet
    .Cells.ClearContents
    .[A1,B1,C1,D1,E1] = _
        Array("Filename", "Sheet's name", "Rows count", "Columns count", "Sum of Amount")
    .Columns.AutoFit
    CountRow = .Cells(Rows.Count, "A").End(xlUp).Row
End With

With Application.FileDialog(msoFileDialogFolderPicker)
If .Show = False Then Exit Sub
mypath = .SelectedItems(1) & "\"
End With

filename = Dir(mypath & "*.xls")
Do While filename <> ""
    Set myfile = Workbooks.Open(mypath & filename)
        Set w = ActiveSheet
        i = w.Parent.Worksheets.Count
            For j = 1 To i
                With strsheet
                    .Cells(CountRow + 1, 1).Value = filename
                    .Cells(CountRow + 1, 2).Value = w.Parent.Worksheets(j).Name
                    .Cells(CountRow + 1, 3).Value = w.Parent.Worksheets(j).Cells(1, 1).End(xlDown).Row
                    .Cells(CountRow + 1, 4).Value = w.Parent.Worksheets(j).Cells(1, 1).End(xlToRight).Column
                End With
            Next j
        w.Parent.Close False
    filename = Dir()
CountRow = CountRow + 1
Loop

Set w = Nothing
Set strsheet = Nothing

MsgBox ("Job Complete")

Application.ScreenUpdating = True
End Sub

 

[K Raghavender rao]

Hi Deepak,

Thanks for above query

I don't find the Sum of Sample File (column E) in Count (1) macro file in column E.

for example total sum of Sample file (Column E) is 180,000 this needs to updated in Count (1) macro file in Column E

Thanks in advance.

K Raghavender rao

 

[Deepak]

Check with attached file.