Summing same cell across hundreds of Excel workbooks

I have around 500 timecards. Each开发者_运维知识库 timecard is it's own file (.xls workbook). I need a total of cell K5 from all those workbooks. This is a one time thing (every quarter) - I don't need the result to be updated if any of those 500+ timecards change.

It would be great if there was a function that took two parameters - 1) the name of the directory containing the Excel files; 2) the specific cell you want totaled.

After 3.5 hours of searching, the Excel forums haven't helped. I must be using bad keywords, as I can't believe Excel doesn't have such basic functionality. (I did find some stuff if I had multiple worksheets in the same workbook - we would not be able to maintain all those time cards in the same file, and going through and opening all the workbooks at the same time would be very tedious - 500+ double-clicks.)

Thanks.

(Windows XP SP3; Microsoft Office Excel 2003)

This is some code that will prompt for a folder to be selected and then cycle all files in that folder for worksheet name [Worksheetname = "Sheet1"] and [CELL = "K5"] and sum the totals in cell K5.

Hopefully this will get you started. You will need to ensure all files have a valid 'Worksheetname' or insert a test.

Option Explicit

Private Sub ReadFilesinFolder()
'**Opens File Dialog and cycles all files for batch or just single file
Dim objFs As Object, objF As Object, objF1 As Object, objFc As Object
Dim strEndofPath As String, strFilePath As String, strFilename As String
Dim lngCount As Long, Worksheetname As String, CELL As String, objWB As Workbook, objWS As Worksheet
Dim dblTOTAL As Double

Worksheetname = "Sheet1"
CELL = "K5"


With Application.FileDialog(msoFileDialogOpen)
    .AllowMultiSelect = False
    .Show

    If .SelectedItems.Count = 0 Then
        End
    End If

    For lngCount = 1 To .SelectedItems.Count
        strEndofPath = InStrRev(.SelectedItems(lngCount), "\")
        strFilePath = Left(.SelectedItems(lngCount), strEndofPath)
        strFilename = Right(.SelectedItems(lngCount), Len(.SelectedItems(lngCount)) - strEndofPath)
    Next lngCount
End With

Set objFs = CreateObject("Scripting.FileSystemObject")
Set objF = objFs.GetFolder(strFilePath)

    'Batch Import
    Set objFc = objF.Files
    For Each objF1 In objFc
    DoEvents

        Set objWB = GetObject(objF1)
        Set objWS = objWB.Sheets(Worksheetname)
        dblTOTAL = dblTOTAL + objWS.Range(CELL).Value

        objWB.Close
        Set objWB = Nothing

    Next objF1
 msgbox dblTOTAL
End Sub