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.

Excel Merge Files

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.

Excel VBA Merge Text Files

Visual Basic

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

PrivateSubVBA_Merge_Text_Files()

'''''Declare Variables

DimInpFileNum AsInteger

DimOutFileNum AsInteger

DimIn_File_Path AsString

DimOut_File_Path AsString

DimIn_File_Data AsString

'''''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 ForOutput AsOutFileNum

'''''Loop Thru each file in Input List

WhileSheets(1).Cells(iRow,1)<>""

'''''Open Input File in Read Mode

InpFileNum=FreeFile

Open In_File_Path ForInput AsInpFileNum

'''''Read Input File line by line till End Of File and Write to Output

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.)

Kumarapush has experience in IT industry for 12+ Years including 10 Years in MNC. Has vast expertise in Microsoft Excel automation using VBA Macro. Excel to CSV converter is one of his famous tool that has been downloaded & used by 68,000 users around the globe.