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!

 

[jardenp]

That works! I think I'll add code to create a new sheet named "FileNames" at the beginning.

Your help is much appreciated!

 

[jardenp]

Here's Prabby's code with my addition for any future readers of this post:

Sub GetFileNames()

Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "FileNames"

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

 

[prabby25101981]

I think you can modify the code further to make it do what you wanted in your original post... If you need any help, let me know...

Thanks!

 

[snb_]

No,No,No I'm sorry I forgot something:

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

 

[jardenp]

I don't know how to modify your code to do what I originally wanted--to automatically load the workbooks, add the H columns, and present that result somewhere. I'm not saying it can't be done, just that I don't know how. I can create a macro that adds "=SUM('[" before and "]Sheet1'!H:H)" after the file names, activates the results as a formula, and then adds those results. My scripting skills are limited to tweaking products of the macro recorder (which is why I love this forum so much!!!).

If snb is still following this thread, I'm still very interested in debugging his/her code as noted above (or, more likely, finding out how I'm not using it properly).

But, Prabby, you're a champ and I appreciate your help.

 

[snb_]

You already got your answer.

 

[jardenp]

I was writing that as you posted. Thanks for the reply and I'm trying it out now.

 

[jardenp]

snb: I'm trying your code with the following change:

  c01 = "C:\Documents and Settings\Josh\Desktop\Test\"

I'm running it as a macro in a new workbook, but no results are produced. No errors either, but I'm not sure what to expect.

 

[snb_]

In cell A1 the result will be shown

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
    c02=Dir
  Loop
  cells(1,1)=x1
End Sub

From this distance I can't see whether these workbooks contain only 1 sheet or more, nor am I able to trace whether Column H contains numerical data or not.
If necessary post two workbook examples.

 

[jardenp]

I'm running the script on some test workbooks since I don't have the "real" files on the computer I'm using. I have two files in the folder: Book1.xls and Book5.xls. Each has three sheets (Sheet1, Sheet2, and Sheet3) but there is only data on Sheet 1. Each has the number 5 in cells H3:H8, so I expect the total from both workbooks to be 50.

I added a test at the end in hopes that it will demonstrate the macro has run, but I'm not getting the expected "Done" in A2:

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(3).Columns(8))
      .Close False
    End With
    c02 = Dir
  Loop
  Cells(1, 1) = x1
  
  Range("A2").Select
        ActiveCell.FormulaR1C1 = "Done"
End Sub

I can link to a drop site with the test files I'm using, but they're really as simple as I described, so I think the problem is elsewhere.

To make sure I'm not missing something, this is what I've done:
1. Copy the above code to a module in the VBA editor
2. Save in the VBA editor
3. Open a new workbook
4. Open the macros dialog from the Developer ribbon
5. Run the macro "snb"

Nothing appears in A1 or A2. But, like I said earlier, no errors are triggered either. It's like I didn't do anything.

Thank you for your continued help.