CODEWITHSUNDEEP
vbaexcel
Silent Observer
Silent Observer

Reputation: 69

Find sum across different workbooks

I have six workbooks, all identical. Every workbook has some value in A2, B2, C2 cells of the second sheet of the workbook. I want a consolidated Excel sheet which would produce the sum of A2s,B2s,C2s of sheet2 of all workbooks. I put all the six workbooks in a newfolder and created a new workbook and used the following code,

Sub SumWB()
  Dim Arr(2) As Long, MyWB As Workbook, fStr As String
  Const Folder = "C:\Users\satsobek\Downloads\New folder\"
  fStr = Dir(Folder)
  While (file <> "")
    Set MyWB = Workbooks.Open(Folder & fStr, , True)
    Arr(0) = Arr(0) + MyWB.Sheets(2).Range("A2").Value
    Arr(1) = Arr(1) + MyWB.Sheets(2).Range("B2").Value
    Arr(2) = Arr(2) + MyWB.Sheets(2).Range("C2").Value
    MyWB.Close
    file = Dir
  Wend
  Debug.Print Arr(0) & " - " & Arr(1) & " - " & Arr(2)
End Sub

and wanted to initially check whether the output produces the sum. But I am getting only 

0-0-0

as output.

Can anybody help me in debugging the code to find the sum of A2s,B2s and C2s of sheet2 across various workbooks?

Upvotes: 1

Views: 108

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27269

The problem is in how you define the file to search for.

You set fStr = Dir(Folder) Then you say While (file <> "") Since file is not set initially, the While Loop will exit right away (because file does not ever get set until the first loop - which never happens).

Change all references of fStr to file and it will work.

Sub SumWB()
  Dim Arr(2) As Long, MyWB As Workbook, fStr As String
  Const Folder = "C:\Users\satsobek\Downloads\New folder\"
  file = Dir(Folder)
  While (file <> "")

    If file <> "consol.xlsx" Then 
       Set MyWB = Workbooks.Open(Folder & file, , True)
       Arr(0) = Arr(0) + MyWB.Sheets(2).Range("A2").Value
       Arr(1) = Arr(1) + MyWB.Sheets(2).Range("B2").Value
       Arr(2) = Arr(2) + MyWB.Sheets(2).Range("C2").Value
       MyWB.Close
    End If
    file = Dir
  Wend
  Debug.Print Arr(0) & " - " & Arr(1) & " - " & Arr(2)
End Sub

Upvotes: 2

Related Questions