(Solved) – How to calculate the sum for all files in a directory?

(solved)-–-how-to-calculate-the-sum-for-all-files-in-a-directory?

All files in directory survey contain same structure,i can add b column and c column with sub sum():

Sub Sum()
    Dim BottomOfTable As Long
    BottomOfTable = Cells(Rows.Count, "A").End(xlUp).Row
    Cells(BottomOfTable   1, "A").Value = "score"
    Range("B" & BottomOfTable   1).Select
    Selection.FormulaR1C1 = "=round(SUM(R[-" & BottomOfTable - 1 & "]C:R[-1]C)" & ",2)"
    Selection.AutoFill Destination:=Range("b" & BottomOfTable   1 & ":" & "c" & BottomOfTable   1), Type:=xlFillDefault
    Range("b" & BottomOfTable   1 & ":" & "c" & BottomOfTable   1).Select    
End Sub

And i can list all files in directory with Sub ListDir:

Sub ListDir()
Dim FileName As String
FileName = Dir("d:survey", vbDirectory)
Do While FileName <> ""
    Debug.Print FileName
    FileName = Dir()
Loop
End Sub

How can i calculate the sum when to list the file?Maybe rewrite sub sum as a function,sum all b and c column when to listdir?

Sub ListDir()
Dim FileName As String
FileName = Dir("d:survey", vbDirectory)
Do While FileName <> ""
    sum(FileName)  #how to fill it?
    FileName = Dir()
Loop
End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *