Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert multiple excel files with multiple sheets to PDF - how? | Excel Discussion (Misc queries) | |||
Convert one .csv file to multiple excel files | Excel Discussion (Misc queries) | |||
How do I convert pdf files into an Excel file | Excel Discussion (Misc queries) | |||
Convert Multiple CSV Files to XLS Files (Again) | Excel Discussion (Misc queries) | |||
convert word files to excel file | Excel Programming |