[Nedulous]

I have 200 csv files with data that I want to merge into one Excel file for
sorting purposes.

[Jacob Skaria]

Copy all .csv files to a folder
Suppose you have copied that to c:\temp\

From command line within C:\Temp\ use COPY command as below to copy all to a
single file with a different extension...

COPY *.csv MyTest.txt/b

Open the newly create MyTest.txt in Excel and open as comma delimiter file....

If this post helps click Yes
---------------
Jacob Skaria


"Nedulous" wrote:

I have 200 csv files with data that I want to merge into one Excel file for
sorting purposes.

[Ron de Bruin]

See
http://www.rondebruin.nl/csv.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Nedulous" wrote in message ...
I have 200 csv files with data that I want to merge into one Excel file for
sorting purposes.

[Angela[_2_]]

On Apr 28, 11:26*pm, "Ron de Bruin" wrote:
Seehttp://www.rondebruin.nl/csv.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Nedulous" wrote in ...
I have 200 csvfileswith data that I want tomergeinto oneExcelfile for
sorting purposes.- Hide quotedtext-

- Show quotedtext-

Dear Ron,

I have modified the code a bit but I'm unable to get one thing.

Header is already in first text file. The code is importing header
from all text files which is not good.

Any help would be appreciated.

Thx.
Code is as follows:
' Start Code

Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional
WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub


Sub Merge_CSV_Files()
Dim BatFileName As String
Dim TXTFileName As String
Dim XLSFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String
Dim Wb As Workbook
Dim oApp As Object
Dim oFolder
Dim foldername

'Create two temporary file names
BatFileName = Environ("Temp") & _
"\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") &
".bat"
TXTFileName = Environ("Temp") & _
"\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

'Folder where you want to save the Excel file
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If

'Set the extension and file format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
'If you want to save as xls(97-2003 format) in 2007 use
'FileExtStr = ".xls": FileFormatNum = 56
End If

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterCSV " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

'Browse to the folder with CSV files
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV
files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) < "\" Then
foldername = foldername & "\"
End If

'Create the bat file
Open BatFileName For Output As #1
Print #1, "Copy " & Chr(34) & foldername & "*.txt" _
& Chr(34) & " " & TXTFileName
Close #1

'Run the Bat file to collect all data from the CSV files into
a TXT file
ShellAndWait BatFileName, 0
If Dir(TXTFileName) = "" Then
MsgBox "There are no csv files in this folder"
Kill BatFileName
Exit Sub
End If

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array
(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 1), Array
(6, 2))


'Save text file as a Excel file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
Application.DisplayAlerts = True

Wb.Close savechanges:=False
MsgBox "You find the Excel file he " & vbNewLine &
XLSFileName

'Delete the bat and text file you temporary used
Kill BatFileName
Kill TXTFileName

Application.ScreenUpdating = True
End If
End Sub

' End code

[Ron de Bruin]

Use this then
http://www.rondebruin.nl/copy3.htm

Or if you want it easy use my add-in
http://www.rondebruin.nl/merge.htm

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Angela" wrote in message ...
On Apr 28, 11:26 pm, "Ron de Bruin" wrote:
Seehttp://www.rondebruin.nl/csv.htm

--

Regards Ron de Bruinhttp://www.rondebruin.nl/tips.htm



"Nedulous" wrote in ...
I have 200 csvfileswith data that I want tomergeinto oneExcelfile for
sorting purposes.- Hide quotedtext-

- Show quotedtext-

Dear Ron,

I have modified the code a bit but I'm unable to get one thing.

Header is already in first text file. The code is importing header
from all text files which is not good.

Any help would be appreciated.

Thx.
Code is as follows:
' Start Code

Declare Function OpenProcess Lib "kernel32" _
(ByVal dwDesiredAccess As Long, _
ByVal bInheritHandle As Long, _
ByVal dwProcessId As Long) As Long

Declare Function GetExitCodeProcess Lib "kernel32" _
(ByVal hProcess As Long, _
lpExitCode As Long) As Long

Public Const PROCESS_QUERY_INFORMATION = &H400
Public Const STILL_ACTIVE = &H103


Public Sub ShellAndWait(ByVal PathName As String, Optional
WindowState)
Dim hProg As Long
Dim hProcess As Long, ExitCode As Long
'fill in the missing parameter and execute the program
If IsMissing(WindowState) Then WindowState = 1
hProg = Shell(PathName, WindowState)
'hProg is a "process ID under Win32. To get the process handle:
hProcess = OpenProcess(PROCESS_QUERY_INFORMATION, False, hProg)
Do
'populate Exitcode variable
GetExitCodeProcess hProcess, ExitCode
DoEvents
Loop While ExitCode = STILL_ACTIVE
End Sub


Sub Merge_CSV_Files()
Dim BatFileName As String
Dim TXTFileName As String
Dim XLSFileName As String
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim DefPath As String
Dim Wb As Workbook
Dim oApp As Object
Dim oFolder
Dim foldername

'Create two temporary file names
BatFileName = Environ("Temp") & _
"\CollectCSVData" & Format(Now, "dd-mm-yy-h-mm-ss") &
".bat"
TXTFileName = Environ("Temp") & _
"\AllCSV" & Format(Now, "dd-mm-yy-h-mm-ss") & ".txt"

'Folder where you want to save the Excel file
DefPath = Application.DefaultFilePath
If Right(DefPath, 1) < "\" Then
DefPath = DefPath & "\"
End If

'Set the extension and file format
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
'You use Excel 2007
FileExtStr = ".xlsx": FileFormatNum = 51
'If you want to save as xls(97-2003 format) in 2007 use
'FileExtStr = ".xls": FileFormatNum = 56
End If

'Name of the Excel file with a date/time stamp
XLSFileName = DefPath & "MasterCSV " & _
Format(Now, "dd-mmm-yyyy h-mm-ss") & FileExtStr

'Browse to the folder with CSV files
Set oApp = CreateObject("Shell.Application")
Set oFolder = oApp.BrowseForFolder(0, "Select folder with CSV
files", 512)
If Not oFolder Is Nothing Then
foldername = oFolder.Self.Path
If Right(foldername, 1) < "\" Then
foldername = foldername & "\"
End If

'Create the bat file
Open BatFileName For Output As #1
Print #1, "Copy " & Chr(34) & foldername & "*.txt" _
& Chr(34) & " " & TXTFileName
Close #1

'Run the Bat file to collect all data from the CSV files into
a TXT file
ShellAndWait BatFileName, 0
If Dir(TXTFileName) = "" Then
MsgBox "There are no csv files in this folder"
Kill BatFileName
Exit Sub
End If

'Open the TXT file in Excel
Application.ScreenUpdating = False
Workbooks.OpenText Filename:=TXTFileName, Origin:=xlWindows,
StartRow _
:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False,
Comma:=True, _
Space:=False, Other:=True, OtherChar:="|", FieldInfo:=Array
(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 1), Array
(6, 2))


'Save text file as a Excel file
Set Wb = ActiveWorkbook
Application.DisplayAlerts = False
Wb.SaveAs Filename:=XLSFileName, FileFormat:=FileFormatNum
Application.DisplayAlerts = True

Wb.Close savechanges:=False
MsgBox "You find the Excel file he " & vbNewLine &
XLSFileName

'Delete the bat and text file you temporary used
Kill BatFileName
Kill TXTFileName

Application.ScreenUpdating = True
End If
End Sub

' End code