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?