Convert multiple csv files into one excel file
 

Can anyone help me. I want to convert a number of csv files (all in the
same folder) into an excel file. I would like each csv to be a worksheet in
the excel file with the sheet name the same as the csv file name but without
the .csv file extension.

I guess it would be easy if I was prompted for which folder I want to use as
source as I have to do this over and over again!!

--
Chris Lewis

Convert multiple csv files into one excel file
 

We believe the macro recorder can do this job.

Add a new worksheet;
get new filename;
name the worksheet with filename;
import the csv file;

continue with next file

Regards
"Chris Lewis" wrote in message
...
Can anyone help me. I want to convert a number of csv files (all in the
same folder) into an excel file. I would like each csv to be a worksheet
in
the excel file with the sheet name the same as the csv file name but
without
the .csv file extension.

I guess it would be easy if I was prompted for which folder I want to use
as
source as I have to do this over and over again!!

--
Chris Lewis

Convert multiple csv files into one excel file
 

Try this one Chris

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


Sub Example()
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 csv 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



"Chris Lewis" wrote in message ...
Can anyone help me. I want to convert a number of csv files (all in the same folder) into an excel file. I would like each csv
to be a worksheet in the excel file with the sheet name the same as the csv file name but without the .csv file extension.

I guess it would be easy if I was prompted for which folder I want to use as source as I have to do this over and over again!!

--
Chris Lewis

Convert multiple csv files into one excel file
 

Try windows ms-dos

in folder where the file a example c:\ myfolder
write, copy *.csv to c:\ all.csv
and next.

In excel import c:\all.csv

Best regard

Andrew
Ron de Bruin ha escrito:

Try this one Chris

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


Sub Example()
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 csv 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



"Chris Lewis" wrote in message ...
Can anyone help me. I want to convert a number of csv files (all in the same folder) into an excel file. I would like each csv
to be a worksheet in the excel file with the sheet name the same as the csv file name but without the .csv file extension.

I guess it would be easy if I was prompted for which folder I want to use as source as I have to do this over and over again!!

--
Chris Lewis

Convert multiple csv files into one excel file
 

Try windows ms-dos

in folder where the file a example c:\ myfolder
write, copy *.csv to c:\ all.csv
and next.

In excel import c:\all.csv

Best regard

Andrew
Ron de Bruin ha escrito:

Try this one Chris

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


Sub Example()
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 csv 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



"Chris Lewis" wrote in message ...
Can anyone help me. I want to convert a number of csv files (all in the same folder) into an excel file. I would like each csv
to be a worksheet in the excel file with the sheet name the same as the csv file name but without the .csv file extension.

I guess it would be easy if I was prompted for which folder I want to use as source as I have to do this over and over again!!

--
Chris Lewis

Convert multiple csv files into one excel file
 

Hi Mustang

The OP want a different sheet for each csv file.
This will not work then

Here is a way to do your example with code
http://www.rondebruin.nl/csv.htm


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



"Mustang" wrote in message ups.com...
Try windows ms-dos

in folder where the file a example c:\ myfolder
write, copy *.csv to c:\ all.csv
and next.

In excel import c:\all.csv

Best regard

Andrew
Ron de Bruin ha escrito:

Try this one Chris

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


Sub Example()
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 csv 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



"Chris Lewis" wrote in message ...
Can anyone help me. I want to convert a number of csv files (all in the same folder) into an excel file. I would like each
csv
to be a worksheet in the excel file with the sheet name the same as the csv file name but without the .csv file extension.

I guess it would be easy if I was prompted for which folder I want to use as source as I have to do this over and over again!!

--
Chris Lewis