Importing lots of CSV files into an XLS file as different workshee

I need to import a load of CSV files into one workbook so that I can have
everything in XLS format and tidy everything up. The problem is I need to do
this to lots of files, and for a lot of workbooks, and to do it manually
would take an age. Can anyone suggest a macro that can import CSV files into
my workbook, perhaps taking each file name and path from a list file?? Or
another method maybe?

Thanks in advance, and Happy New Year!

Hi rmellison

Try this
http://www.rondebruin.nl/csv.htm


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


"rmellison" <[email protected]> wrote in message news:[email protected]...
>I need to import a load of CSV files into one workbook so that I can have
> everything in XLS format and tidy everything up. The problem is I need to do
> this to lots of files, and for a lot of workbooks, and to do it manually
> would take an age. Can anyone suggest a macro that can import CSV files into
> my workbook, perhaps taking each file name and path from a list file?? Or
> another method maybe?
>
> Thanks in advance, and Happy New Year!

Thanks Ron, I used the Macro.

However, I really need to be able to import each CSV file as an indivdual
worksheet, with the name of the file used as the worksheet name. If I use the
Macro as it is a get 50,000 rows and 200 columns of data, which, although it
fits snuggly onto one sheet, is a bit much to manage.

Can you, or anyone else perhaps, suggest an ammendment to the code? I'm
afraid my knowledge of VBA is minimal at best!

Thanks.


"Ron de Bruin" wrote:

> Hi rmellison
>
> Try this
> http://www.rondebruin.nl/csv.htm
>
>
> --
> Regards Ron de Bruin
> http://www.rondebruin.nl
>
>
> "rmellison" <[email protected]> wrote in message news:[email protected]...
> >I need to import a load of CSV files into one workbook so that I can have
> > everything in XLS format and tidy everything up. The problem is I need to do
> > this to lots of files, and for a lot of workbooks, and to do it manually
> > would take an age. Can anyone suggest a macro that can import CSV files into
> > my workbook, perhaps taking each file name and path from a list file?? Or
> > another method maybe?
> >
> > Thanks in advance, and Happy New Year!
>
>
>

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


"rmellison" <[email protected]> wrote in message news:[email protected]...
> Thanks Ron, I used the Macro.
>
> However, I really need to be able to import each CSV file as an indivdual
> worksheet, with the name of the file used as the worksheet name. If I use the
> Macro as it is a get 50,000 rows and 200 columns of data, which, although it
> fits snuggly onto one sheet, is a bit much to manage.
>
> Can you, or anyone else perhaps, suggest an ammendment to the code? I'm
> afraid my knowledge of VBA is minimal at best!
>
> Thanks.
>
>
> "Ron de Bruin" wrote:
>
>> Hi rmellison
>>
>> Try this
>> http://www.rondebruin.nl/csv.htm
>>
>>
>> --
>> Regards Ron de Bruin
>> http://www.rondebruin.nl
>>
>>
>> "rmellison" <[email protected]> wrote in message news:[email protected]...
>> >I need to import a load of CSV files into one workbook so that I can have
>> > everything in XLS format and tidy everything up. The problem is I need to do
>> > this to lots of files, and for a lot of workbooks, and to do it manually
>> > would take an age. Can anyone suggest a macro that can import CSV files into
>> > my workbook, perhaps taking each file name and path from a list file?? Or
>> > another method maybe?
>> >
>> > Thanks in advance, and Happy New Year!
>>
>>
>>

Works just as I need it to, many thanks.

"Ron de Bruin" wrote:

>
> 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
>
>
> "rmellison" <[email protected]> wrote in message news:[email protected]...
> > Thanks Ron, I used the Macro.
> >
> > However, I really need to be able to import each CSV file as an indivdual
> > worksheet, with the name of the file used as the worksheet name. If I use the
> > Macro as it is a get 50,000 rows and 200 columns of data, which, although it
> > fits snuggly onto one sheet, is a bit much to manage.
> >
> > Can you, or anyone else perhaps, suggest an ammendment to the code? I'm
> > afraid my knowledge of VBA is minimal at best!
> >
> > Thanks.
> >
> >
> > "Ron de Bruin" wrote:
> >
> >> Hi rmellison
> >>
> >> Try this
> >> http://www.rondebruin.nl/csv.htm
> >>
> >>
> >> --
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl
> >>
> >>
> >> "rmellison" <[email protected]> wrote in message news:[email protected]...
> >> >I need to import a load of CSV files into one workbook so that I can have
> >> > everything in XLS format and tidy everything up. The problem is I need to do
> >> > this to lots of files, and for a lot of workbooks, and to do it manually
> >> > would take an age. Can anyone suggest a macro that can import CSV files into
> >> > my workbook, perhaps taking each file name and path from a list file?? Or
> >> > another method maybe?
> >> >
> >> > Thanks in advance, and Happy New Year!
> >>
> >>
> >>
>
>
>