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.
1234567891011121314151617181920212223242526272829303132333435363738394041Private Sub VBA_Merge_Text_Files()'''''Declare VariablesDim InpFileNum As IntegerDim OutFileNum As IntegerDim In_File_Path As StringDim Out_File_Path As StringDim In_File_Data As String'''''Assigning the Input & Output File pathiRow = 2In_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 ModeOutFileNum = FreeFileOpen Out_File_Path For Output As OutFileNum'''''Loop Thru each file in Input ListWhile Sheets(1).Cells(iRow, 1) <> ""'''''Open Input File in Read ModeInpFileNum = FreeFileOpen In_File_Path For Input As InpFileNum'''''Read Input File line by line till End Of File and Write to OutputWhile Not VBA.EOF(InpFileNum)Line Input #InpFileNum, In_File_DataPrint #OutFileNum, In_File_Data 'Merge Data Files to One FileWend'''''Close Currently Opened File & Process Next File in Input ListClose InpFileNumiRow = iRow + 1In_File_Path = VBA.Trim(Sheets(1).Cells(iRow, 1))Wend'Close Output FileClose OutFileNumMsgBox "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
VBA Merge Text Files – Combine Data Files Into One with Excel