open files, count rows, record findings

[keith05281967]

Greetings,

Okay, i'm a bit more prepared to ask my question this time.
I'd like to be able to go thru all the workbooks in a folder, check the row count and record that information either in a msgbox or spdsheet. I have some code that does some of this. I probably need to add an array to record all the counts


Sub RowCount()
Dim path As Variant
Dim excelfile As Variant
Dim NumRows As Integer

path = "C:\Documents and Settings\My Documents\Macro Testing\test directory code\"

excelfile = Dir("*.xls")
Do While excelfile <> ""
Workbooks.Open Filename:=path & excelfile
excelfile = Dir
NumRows = Application.WorksheetFunction.CountA(Range("A1:A65536"))
ActiveWorkbook.Close

Loop

End Sub


I'd want the results to look like this:

fileA........................50
fileB........................35
filec........................112


thanks,
Keith

 

[Case_Germany]

Hi,

try it this way ...

Option Explicit
Const strFolder As String = "C:\Temp\"
Sub Main()
    Dim objWorkbook As Workbook
    Dim strFilename As String
    Dim dblCount As Double
    Dim intCalc As Integer
    Dim lngRow As Long
    On Error GoTo Fin
        With Application
            .ScreenUpdating = False
            .EnableEvents = False
            intCalc = .Calculation
            .Calculation = xlCalculationManual
            .ShowWindowsInTaskbar = False
        End With
        strFilename = Dir$(strFolder & "*.xls")
        Do Until strFilename = vbNullString
            Set objWorkbook = Workbooks.Open(Filename:= _
                strFolder & strFilename, UpdateLinks:=0, ReadOnly:=True)
            lngRow = lngRow + 1
            dblCount = Application.WorksheetFunction.CountA(Range("A:A"))
            objWorkbook.Close False
            Sheet1.Cells(lngRow, 1).Value = strFilename
            Sheet1.Cells(lngRow, 2).Value = dblCount
            strFilename = Dir$()
            Set objWorkbook = Nothing
        Loop
Fin:
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = intCalc
        .ShowWindowsInTaskbar = True
    End With
    If Err.Number <> 0 Then MsgBox "Error: " & _
        Err.Number & " " & Err.Description
End Sub

 

[keith05281967]

Hi Case - that works brilliantly. That is precisely what I wanted to accomplish.

thank you so much.
Keith