VBA Merge Text Files – Combine Data Files Into One with Excel

How to Merge Files in Excel using VBA?

Get VBA merge text files Excel macro code in this article. In our daily work routine we deal with lot of data & log files of different formats collected from different sources.

To name a few .TXT, .EXE, .XLS, .XLSX, .PST, .DOC, .LOG etc., Well,  am just rethinking “Should I be explaining about all files types?”, When I am just going to explain how to Join or Merge or do data consolidation of same format files into one single file.

Commands in this article can be used to merge multiple files of same type or same file extension. Where as the open source code available here is just the basics for any learner. Lets take the first step.

VBA Merge Text Files – Input Setup

Assume you have list of files to be merged to a single output file as mentioned in an example excel sheet as below.

The code in this article reads list of files that you provide and merge them into one file. The code provided can be modified to include any delimiter at end of each file before it is merged to the main file if required.

Excel Macro Merge Files VBA Code

Copy paste the code to Excel VB Editor and Press F5 to run our simple Excel VBA Merge files tool. In case, if all these coding is a complex things for you, simply use the WinZIp, WinRAR or other file compressor software available in market.

Private Sub VBA_Merge_Text_Files()
    
    '''''Declare Variables
    Dim InpFileNum As Integer
    Dim OutFileNum As Integer
    Dim In_File_Path As String
    Dim Out_File_Path As String
    Dim In_File_Data As String
        
    '''''Assigning the Input & Output File path
    iRow = 2
    In_File_Path = VBA.Trim(Sheets(1).Cells(iRow, 1))
    Out_File_Path = VBA.Trim(Sheets(1).Cells(iRow, 2))
    
    '''''Open Output File in Proper Write Mode
    OutFileNum = FreeFile
    Open Out_File_Path For Output As OutFileNum
    
    
    '''''Loop Thru each file in Input List
    While Sheets(1).Cells(iRow, 1) <> ""
        '''''Open Input File in Read Mode
        InpFileNum = FreeFile
        Open In_File_Path For Input As InpFileNum
     
        '''''Read Input File line by line till End Of File and Write to Output
        While Not VBA.EOF(InpFileNum)
            Line Input #InpFileNum, In_File_Data
            Print #OutFileNum, In_File_Data 'Merge Data Files to One File
        Wend
        
        '''''Close Currently Opened File & Process Next File in Input List
        Close InpFileNum
        iRow = iRow + 1
        In_File_Path = VBA.Trim(Sheets(1).Cells(iRow, 1))
    Wend
    
    'Close Output File
    Close OutFileNum
    MsgBox "Files Merged"
End Sub

Note: This code performs open, read & write operations on source files in ASCII text mode only. If you need to merge binary files, please refer how to access files in Binary mode.

Also Read: How to Remove Junk Data From Text File?

Merge Text Files using DOS Command

Before doing this with Excel VBA code, let’s look at other alternative method. There is also simple shorter version of DOS command available to merge 2 files. Assume you have 2 files Source_File_1.exe and Source_File_2.ext. Use the Command as below. To know about more options available with this command, type ‘Help Copy’ at the dos prompt.

• Copy Source_File_1.ext + Source_File_2.ext Merged_Output_File.ext

How to dynamically Merge Files using Command Prompt?

If we know the list of files to be merged then we can use the above method. In case if we don’t have the exact list, but we need to convert a list of files found in a directory then use the below command. (Assuming we are merging list of Txt or VCF file.)

• CommandPrompt:\>for %f in (*.txt) do type “%f” >> MergedOutput.txt
• CommandPrompt:\>for %f in (contacts*.vcf) do type “%f” >> MergedContacts.vcf

Also Read: How to use Mail Merge option in Word for Mass E-mail Marketing?