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!

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

Hi rmellison

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


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


"rmellison" wrote in message ...
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!

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

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" wrote in message ...
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!

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

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" wrote in message ...
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" wrote in message ...
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!

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

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" wrote in message ...
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" wrote in message ...
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!