ncurran217
04-24-2013, 12:22 PM
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:
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
Here is the code:
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