How to list all excel file names in specific foldder and total number of row used in each file using Excel VSTO C#

Question

I am new to Excel add-in.

I have specific folder where I want Excel add-in to search for any excel file including sub folder and list down all file names and total number of row used in existing excel file where the add-in is running.

Any help will be appreciated.

Many thanks in advance.

Answer 1

This should do what you want.

Sub OpenAllExcelFiles()

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:\your_path\"       'Path of CSV Files
sFilename = Dir(sPath & "*.xlsx")


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

Final result in my test case: