Summing column H in 100 unopened workbooks

[jardenp]

I have two similar challenges I need help with:

1. I have about a hundred workbooks saved in a folder. I need to get the sum of all H columns in those workbooks. How can I do this without opening them all and using something like this:

=SUM('[Workbook1.xls]Sheet1'!H:H, '[Workbook2.xls]Sheet1'!H:H, ...)
​

I'm guessing there is a VBA script to do this, preferably one that can automatically perform the action on all .xls files in a folder, so I don't need to type in each workbook name.

2. I have some workbooks, each with about 75 worksheets. I need to get the sum of each B3 cell in a workbook. I will do this on a new sheet within each workbook, so the solution doesn't need to reference workbook names. However, it can't specifically reference sheet names either because each workbook is going to have different, unpredictable sheet names.

If the solution to challenge 1 isn't sheet-name specific, i.e., it automatically includes all sheets in a workbook, and not, say, just sheets with a specific name like "Sheet1", then the same solution should work for both challenges.

Thank you for any help you provide. The thought of having to do this manually makes me want to go wander the train tracks next to my office blindfolded!

 

[snb_]

Answer to question 1:

Sub snb()
  c01 = "E:\OF\"
  c02 = Dir(c01 & "*.xls")
  Do Until c02 = ""
    With GetObject(c01 & c02)
      x1 = x1 + Application.Sum(.Sheets(1).Columns(8))
      .Close False
    End With
  Loop
  cells(1,1)=x1
End Sub

second question:

Sub snb_001()
  With sheets.add
    .Cells(1, 1) = "=sum(" & Sheets(1).Name & ":" & Sheets(Sheets.Count).Name & "!B3)"
  End With
End Sub

 

[prabby25101981]

The below mentioned code will pull all workbooks available in the folder you specify to a sheet named "FileNames" in the current workbook -

Sub GetFileNames()
Dim Pth As String
Dim fsFile As Object
Dim i As Long
Sheets("FileNames").Range("A2:A" & Application.Sheets("FileNames").Range("A3").End(xlDown).Row).ClearContents
 
i = 1
Pth = InputBox("Enter Path - ")
 
For Each fsFile In CreateObject("Scripting.FileSystemObject").GetFolder(Pth$).Files
If Right(fsFile.Name, 3) = "xls" Then
i = i + 1
Sheets("FileNames").Cells(i, 1).Value = fsFile.Name
End If
Next
End Sub

Once you have the file names, you can either use formulas to pickup the values for each of the workbook, or use a macro to do so. Let me know what suits you.

 

[jardenp]

Snb: The solution to the second works perfectly. Thanks! But I'm having problems with the first. It's hanging up on line 4 ("With Get...") and I'm not sure why. Here's what I'm trying (your code with my folder path):

Sub snb()
  c01 = "C:\Documents and Settings\Josh\Desktop\Test"
  c02 = Dir(c01 & "*.xls")
  Do Until c02 = ""
    With GetObject(c01 & c02)
      x1 = x1 + Application.Sum(.Sheets(1).Columns(8))
      .Close False
    End With
  Loop
  cells(1,1)=x1
End Sub

I copied your code to a new module in the VBA editor, opened a new sheet, and ran the macro. Is this what I should be doing?

 

[jardenp]

Prabby: Where do I input my folder path? At "Enter Path -" in

Pth = InputBox("Enter Path - ")

BTW, thanks for this different approach. This will be very useful in other applications.

 

[prabby25101981]

One slash (\) missing I suppose...

 

[prabby25101981]

Prabby: Where do I input my folder path? At "Enter Path -" in

Pth = InputBox("Enter Path - ")

BTW, thanks for this different approach. This will be very useful in other applications.

Thats correct ... You need to enter the path when the dialog box appears...

 

[jardenp]

I changed snb's sub snb() folder path from "...\Test" to "...\Test\" (i'm assuming that's what Prabby was talking about) and I triggered an endless loop when I ran the macro. It brought up the sheets but kept flashing over and over like it was endlessly refreshing and I had to escape out of it. The debugger highlighted the "With Get Object..." line.

 

[jardenp]

Prabby: I'm getting a "Script out of range error" when running the script and the debugger highlights the "Sheets("FileNames").Range("A2:A" ..." line. I don't get to the dialog box part, so I can't enter a path yet.

 

[prabby25101981]

You need to have a sheet named FileNames in your file for this to work. The code will automatically put all file names in this sheet's column A. Hope you are doing this only...