Combine CSV files into 1 Excel Workbook as sheets

Hello,

I have about 60 CSV files that get created by another application. I would
like to take all those files and combine them into a single Excel workbook --
each CSV file becoming a worksheet therein....

Help?! Thanks

Hi JEFF

Use this macro then if the files are in C:\Data
More info here
http://www.rondebruin.nl/copy3.htm


Sub Example12()
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

'Fill in the path\folder where the files are
'on your machine
MyPath = "c:\Data"

'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

'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))
mybook.Worksheets(1).Copy after:= _
basebook.Sheets(basebook.Sheets.Count)

On Error Resume Next
ActiveSheet.Name = mybook.Name
On Error GoTo 0

' You can use this if you want to copy only the values
' With ActiveSheet.UsedRange
' .Value = .Value
' End With

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



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


"JEFF" <[email protected]> wrote in message news:[email protected]...
> Hello,
>
> I have about 60 CSV files that get created by another application. I would
> like to take all those files and combine them into a single Excel workbook --
> each CSV file becoming a worksheet therein....
>
> Help?! Thanks

perfect.. thanks

"Ron de Bruin" wrote:

> Hi JEFF
>
> Use this macro then if the files are in C:\Data
> More info here
> http://www.rondebruin.nl/copy3.htm
>
>
> Sub Example12()
> 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
>
> 'Fill in the path\folder where the files are
> 'on your machine
> MyPath = "c:\Data"
>
> '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
>
> '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))
> mybook.Worksheets(1).Copy after:= _
> basebook.Sheets(basebook.Sheets.Count)
>
> On Error Resume Next
> ActiveSheet.Name = mybook.Name
> On Error GoTo 0
>
> ' You can use this if you want to copy only the values
> ' With ActiveSheet.UsedRange
> ' .Value = .Value
> ' End With
>
> mybook.Close savechanges:=False
> Next Fnum
> End If
> CleanUp:
> Application.ScreenUpdating = True
> End Sub
>
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "JEFF" <[email protected]> wrote in message news:[email protected]...
> > Hello,
> >
> > I have about 60 CSV files that get created by another application. I would
> > like to take all those files and combine them into a single Excel workbook --
> > each CSV file becoming a worksheet therein....
> >
> > Help?! Thanks
>
>
>