Looping through workbooks and counting non zero values

[adjoa]

I have this VBA code to count all values that are not zero in all excel files saved in a folder and print out the result in the workbook containing the macro. The problem I am having is that it opens the same file (the first one) over and over instead of moving to the next file to Count. Thanks for any help.

Sub RealCount()
Dim file As String
Dim row As Integer
Dim wb As Workbook
row = 2
file = Dir("\\Marknadskommunikation\FEB\*.xl??")
Do While file <> ""

Set wb = Workbooks.Open("\\Marknadskommunikation\FEB\*.xl??")
Call ZeroCount
file = Dir()

Loop

End Sub


Sub ZeroCount()

row = 2

ThisWorkbook.Sheets("Sheet1").Cells(row, 2) = Application.WorksheetFunction.CountIf(Range("B3:DE26"), "<>0")
row = row + 1

End Sub

 

[JackDanIce]

Welcome to the board.

I think it's the wrong argument being passed to Set wb = Workbooks.Open, try:

Sub RealCount()

    Dim file    As String
    Dim rng     As Range
    Const ROOT  As String = "\\Marknadskommunikation\FEB\*.xl??"
        
    Set rng = ThisWorkbook.Sheets("Sheet1").Cells(2, 2)
    
    file = Dir(ROOT)
    
    Application.ScreenUpdating = False
    
    Do While LenB(file) > 0
        rng.Value = ZeroCount(Workbooks.Open(file, ReadOnly:=True))
        file = Dir()
        ActiveWorkbook.Close False
        Set rng = rng.Offset(1)
    Loop
    
    With ThisWorkbook
           .Activate
           If ActiveSheet.Name <> "Sheet1" Then .Sheets("Sheet1").Select
    End With

    Application.ScreenUpdating = True
    
    Set rng = Nothing
    
End Sub

Private Function ZeroCount(ByRef wkb As Workbook) As Long


    Dim x       As Long
    Dim y       As Long
    Dim arr()   As Variant
        
    arr = ActiveSheet.Range("B3:DE26").Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        For y = LBound(arr, 2) To UBound(arr, 2)
            ZeroCount = --(arr(x, y) <> 0) + ZeroCount
        Next y
    Next x
    
    Erase arr
    
End Function

 

[adjoa]

Hello JackDanIce. Thank you for the alternative but unfortunately, it is still opening the same file (first one) and counting over and over. And that is after replacing the Word "file" with "ROOT" in rng.Value = ZeroCount(Workbooks.Open(ROOT, ReadOnly:=True)). Otherwise it gave an error that the file could not be found in the location...Oh and the Count came as negative too. Any other remedies?
Thanks in advance!