Appending data of text file to another text file using VBA MS excel

[Asher David]

Hi,

Can anyone help me to provide me aVBA script to append data of text file (.txt file) to another text file? My problem is I got around 50 different text file and I want to append the data into single text file.

My text file contains all the same HEADER. And i want to append the remaining 49 text file into the 1st text fi;e with header already remove since the 1st text file already contain header. Its to tiring for me to do it manually so I open up a question for this.

Many thanks.

[westconn1]

try this, code not tested

' assumes all the text files in folder to be processed
myfolder = "c:\test\"
myfile = Dir(myfolder & "*.txt")
If Len(myfile) = 0 Then Exit Sub   ' no txt files found
Open myfolder & "newfile.txt" For Append As 2
Do While Len(myfile) > 0
    Open myfolder & myfile For Input As 1
        filestr = Input(LOF(1), #1)
        Close 1
        filestr = Mid(filestr, InStr(filestr, vbNewLine) + 2) 'remove first line
        Print #2, filestr
        myfile = Dir
Loop
Close 2

Edit: make sure it does not try to process the output file

[Asher David]

Hi Westconn1,
The code you provide is not working. Maybe need some modification.


Hi Koolsid,
I think this one ok but my problem is I have lot's of text file and i want to do it just one select so that all the 49 text files will be appended to the 1st textfile with the headers already removed.

thanks for all your help

[westconn1]

The code you provide is not working. Maybe need some modification.

which bit don't work?

[Asher David]

on part of:

Do While Len(myfile) > 0
Open myfolder & myfile For Input As 1

It always say file already open.

[westconn1]

are you opening a file #1 before that in the code?
is this the first time through the loop? or is it failing to close 1, though there is only one line between opening and closing, else use freefile

f1 = FreeFile
Open myfolder & myfile For Input As f1
    filestr = Input(LOF(f1), #f1)
        Close f1

[Asher David]

Hi,

many thanks. Let me try this one first.

[Siddharth Rout]

Hi

I think this is what you are looking for...

http://www.microsoft.com/technet/scr...4/hey1201.mspx

Hope this helps...

[Asher David]

Koolsid,

Thanks! its very helpful... But i just have to modify it first coz i want the 2nd and the remaining text file already removed the header before it append. meaning the first lne of text will be removed.

Thanks again!

[Asher David]

Hi Koolsid,

I tried the code but im just wondering where the "output.txt" goes? I cannot view the output.

Can you help me on this?

[Siddharth Rout]

1) Where are you saving the output.txt
2) also are you giving a path in the code?

[Asher David]

Here is the code:

Const ForReading = 1

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objOutputFile = objFSO.CreateTextFile("output.txt")

strComputer = "."
Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")

Set FileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='D:\test'} Where " _
& "ResultClass = CIM_DataFile")

For Each objFile In FileList
Set objTextFile = objFSO.OpenTextFile(objFile.Name, ForReading)
strText = objTextFile.ReadAll
objTextFile.Close
objOutputFile.WriteLine strText
Next

objOutputFile.Close

I put the all my text file in D:\test. Where will I put the output in this code?

[Siddharth Rout]

Hi

This is where you mention the path for the output.txt...

for ex d:\output.txt

Set objOutputFile = objFSO.CreateTextFile("output.txt")

Hope this helps...