Consulting

Results 1 to 6 of 6

Thread: VBA Macro to Count Rows across Multiple Workbooks

  1. #1

    VBA Macro to Count Rows across Multiple Workbooks

    I have found from google a script to open csv files in a directory count the rows. Then it is supposed to print in the workbook with the macro the file name and the amount of rows. Then close the csv file it opened and move on to the next csv file in the directory until there is no more. It stops at opening the first csv file and doesn't do anything past that. I have not worked much with VBA, so I am stuck, and anything that I try still doesn't seem to get past of just opening the first file.

    Here is the code:

    [VBA]
    Sub OpenCSVFiles()

    Dim wb As Workbook, wbCSV As Workbook
    Dim sPath As String, sFilename As String
    Dim NbRows As Integer, rg As Range

    Set wb = ThisWorkbook

    Application.ScreenUpdating = False

    sPath = "C:\Users\ncurran\Desktop\asdf" 'Path of CSV Files
    sFilename = Dir(sPath & "*.csv")

    Do While Len(sFilename) > 0
    Set wbCSV = Workbooks.Open(sPath & sFilename) 'open file
    NbRows = wbCSV.Sheets(1).Range("A100").End(xlUp).Row 'nb of rows

    Set rg = wb.Sheets(1).Range("A100").End(xlUp).Offset(1, 0)
    rg = sFilename
    rg.Offset(0, 1) = NbRows

    wbCSV.Close False 'close file
    sFilename = Dir

    Loop
    Application.ScreenUpdating = True

    End Sub
    [/VBA]

  2. #2
    It seemed to work with a really small file, but my actual files that have over 100,000 rows in them it seems to not continue after opening the first file.

  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Untested but give it a try
    [VBA]Sub OpenCSVFiles()

    Dim wb As Workbook, wbCSV As Workbook
    Dim sPath As String, sFilename As String
    Dim NbRows As Integer, rg As Range

    Set wb = ThisWorkbook

    Application.ScreenUpdating = False

    sPath = "C:\Users\ncurran\Desktop\asdf\" '\ added to correct file path
    sFilename = Dir(sPath & "*.csv")

    Do While Len(sFilename) > 0
    Set wbCSV = Workbooks.Open(sPath & sFilename) 'open file
    NbRows = wbCSV.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row 'maximise rows to check; 100 may be exceeded

    Set rg = wb.Sheets(1).Cells(Rows.Count, 1).End(xlUp).Offset(1, 0) 'maximise result rows; 100 may be exceeded
    rg = sFilename
    rg.Offset(0, 1) = NbRows

    wbCSV.Close False 'close file
    sFilename = Dir

    Loop
    Application.ScreenUpdating = True

    End Sub
    [/VBA]
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  4. #4
    With that, I get a Run-time error '6': Overflow.

  5. #5
    I got it!

    I changed:

    [VBA]Dim NbRows As Integer[/VBA]

    to

    [VBA]Dim NbRows As Long[/VBA]

    And it worked perfectly fine!

  6. #6
    Hello All, could this script be utilized for similar purposes, but with .txt or .xlsx files?
    If so, are there any modifications to be made and please, what would the modifications be?

    Thanks!

    Quote Originally Posted by ncurran217 View Post
    I got it!

    I changed:

    [VBA]Dim NbRows As Integer[/VBA]

    to

    [VBA]Dim NbRows As Long[/VBA]

    And it worked perfectly fine!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •