Alexandra12
New Member
- Joined
- May 29, 2008
- Messages
- 33
Hello Guys,
I have not been on this post for a long time and I am a bit out of practice.
I was looking on the forum for a piece of code that will take multiple CSV files from multiple subfolders and load the data into 1 sheet.
Can anyone recommend how I can change the below code to do this as the below code only works for XLS
Sub CombineSubfolderFiles()
'copies rows from first worksheet of all Excel files in first subfolders
'level of a given folder.
Dim Fs As Object 'FileSystem
Dim D As Object 'Folder
Dim Fx As Object 'Subfolder
Dim File As Object 'File
Dim PathName As String
Dim iRow As Long 'next available row index of destination worksheet
Dim LRow As Long 'last row of source worksheet
With Worksheets(1) 'data destination worksheet
Set Fs = CreateObject("Scripting.FileSystemObject")
Set D = Fs.GetFolder("C:\Documents and Settings\ieu92786\Desktop\Green Sigma\North Commander II\gsextract\2009\")
iRow = 1
For Each Fx In D.subfolders 'loop through subfolders
For Each File In Fx.Files 'loop through files
If File.Name Like "*.csv" Then
PathName = Fx.Name & "\" & File.Name
Workbooks.Open D.path & "\" & PathName
Application.StatusBar = "Processing " & PathName
'copy rows to last row having data in column A
LRow = Worksheets(1).Range("A65536").End(xlUp).Row
Range(Rows(1), Rows(LRow)).Copy Destination:=.Rows(iRow)
ActiveWorkbook.Close
End If
iRow = iRow + LRow
Next File
Next Fx
End With 'Worksheets(1) of this workbook
End Sub
Also, I was wondering that is it correct to say that a CSV file will take an infinite amount of rows as their is no limitations like xls has ?
Thanks in advance
I have not been on this post for a long time and I am a bit out of practice.
I was looking on the forum for a piece of code that will take multiple CSV files from multiple subfolders and load the data into 1 sheet.
Can anyone recommend how I can change the below code to do this as the below code only works for XLS
Sub CombineSubfolderFiles()
'copies rows from first worksheet of all Excel files in first subfolders
'level of a given folder.
Dim Fs As Object 'FileSystem
Dim D As Object 'Folder
Dim Fx As Object 'Subfolder
Dim File As Object 'File
Dim PathName As String
Dim iRow As Long 'next available row index of destination worksheet
Dim LRow As Long 'last row of source worksheet
With Worksheets(1) 'data destination worksheet
Set Fs = CreateObject("Scripting.FileSystemObject")
Set D = Fs.GetFolder("C:\Documents and Settings\ieu92786\Desktop\Green Sigma\North Commander II\gsextract\2009\")
iRow = 1
For Each Fx In D.subfolders 'loop through subfolders
For Each File In Fx.Files 'loop through files
If File.Name Like "*.csv" Then
PathName = Fx.Name & "\" & File.Name
Workbooks.Open D.path & "\" & PathName
Application.StatusBar = "Processing " & PathName
'copy rows to last row having data in column A
LRow = Worksheets(1).Range("A65536").End(xlUp).Row
Range(Rows(1), Rows(LRow)).Copy Destination:=.Rows(iRow)
ActiveWorkbook.Close
End If
iRow = iRow + LRow
Next File
Next Fx
End With 'Worksheets(1) of this workbook
End Sub
Also, I was wondering that is it correct to say that a CSV file will take an infinite amount of rows as their is no limitations like xls has ?
Thanks in advance