Merge CSVs to one file

[samahiji]

Hello

I have the following code that merges CSVs files and produce one file but in xlsx format.

I would like the output file to be CSV as well and not XLS.

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long
    
    Private Declare PtrSafe Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
#Else
    Private Declare Function OpenProcess Lib "kernel32" _
        (ByVal dwDesiredAccess As Long, _
        ByVal bInheritHandle As Long, _
        ByVal dwProcessId As Long) As Long
    
    Private Declare Function GetExitCodeProcess Lib "kernel32" _
        (ByVal hProcess As Long, _
        lpExitCode As Long) As Long
#End If


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
        
        Application.EnableEvents = True
        
        
    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

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

    '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 = "\\Autoload_tests\"
    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 or higher
        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" & FileExtStr

    'Browse to the folder with CSV files
        foldername = DefPath

        'Create the bat file
        Open BatFileName For Output As #1
        Print #1, "Copy " & Chr(34) & foldername & "*.csv" _
                & 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 "Oops!" & vbNewLine & vbNewLine & "Lost network connection!" & vbNewLine & "Check if S drive is connected and click {Refresh} botton again"
            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:=False

        '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 here: " & vbNewLine & XLSFileName

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

        Application.ScreenUpdating = True
        
        
 
End Sub

 

[WaterGypsy]

Try FileFormat = 6

 

[samahiji]

Try FileFormat = 6

WoooooW .. That's great!!

I've been searching for the number of csv file


I would like also to put the output file in different directory, how to do it?

 

[samahiji]

Thanks, I've done it

 

[WaterGypsy]

Just include the file path with the name

 

[samahiji]

Just a little deviation from the subject, can we add the user or the owner in the merged csv to be captured later in file attributes?

 

[WaterGypsy]

Sorry I don't know for sure - but suspect not as these would be Excel attributes which wouldn't exist in a csv file, which is basically a text file with rules applied to the data (i.e. comma to separate the data items) which Excel can interpret