Combine csv files

Hello...Everyday a folder is created with a certain amount of csv files. Each one is the same format, line one is the field names and line two has all the info. Is there a way of combining all the files into one file, leaving off the field names, without importing each one manually. The amount of files and the file names are never the same and the amount of files can range anywhere from 10 to 100.

Thanks in advance for any help you can offer.

Jeff

Hi RevJeff

This is fast but not for you because you only want row 2
http://www.rondebruin.nl/csv.htm

You can open the files and copy row 2 with code like this
http://www.rondebruin.nl/copy3.htm

Change the folder in the code

Sub Example2()
Dim MyPath As String
Dim FilesInPath As String
Dim MyFiles() As String
Dim SourceRcount As Long
Dim Fnum As Long
Dim mybook As Workbook
Dim basebook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim rnum As Long

'Fill in the path\folder where the files are
'MyPath = "C:\Data" or on a network :
MyPath = "\\ComputerName\YourFolder"

'Add a slash at the end if the user forget it
If Right(MyPath, 1) <> "\" Then
MyPath = MyPath & "\"
End If

'If there are no Excel files in the folder exit the sub
FilesInPath = Dir(MyPath & "*.csv")
If FilesInPath = "" Then
MsgBox "No files found"
Exit Sub
End If

On Error GoTo CleanUp
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
'clear all cells on the first sheet
basebook.Worksheets(1).Cells.Clear
rnum = 1

'Fill the array(myFiles)with the list of Excel files in the folder
Fnum = 0
Do While FilesInPath <> ""
Fnum = Fnum + 1
ReDim Preserve MyFiles(1 To Fnum)
MyFiles(Fnum) = FilesInPath
FilesInPath = Dir()
Loop

'Loop through all files in the array(myFiles)
If Fnum > 0 Then
For Fnum = LBound(MyFiles) To UBound(MyFiles)
Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
Set sourceRange = mybook.Worksheets(1).Range("A2:IV2")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Range("A" & rnum)

sourceRange.Copy destrange

rnum = rnum + SourceRcount
mybook.Close savechanges:=False
Next Fnum
End If
CleanUp:
Application.ScreenUpdating = True
End Sub



--
Regards Ron de Bruin
http://www.rondebruin.nl


"RevJeff" <[email protected]> wrote in message
news:[email protected]...
>
> Hello...Everyday a folder is created with a certain amount of csv files.
> Each one is the same format, line one is the field names and line two
> has all the info. Is there a way of combining all the files into one
> file, leaving off the field names, without importing each one manually.
> The amount of files and the file names are never the same and the
> amount of files can range anywhere from 10 to 100.
>
> Thanks in advance for any help you can offer.
>
> Jeff
>
>
> --
> RevJeff
> ------------------------------------------------------------------------
> RevJeff's Profile: http://www.excelforum.com/member.php...o&userid=32201
> View this thread: http://www.excelforum.com/showthread...hreadid=519493
>