Tuesday, December 04, 2007

Combining Text Files using VBA

Visual Basic Application to Merge Text Files

Multiple utilities are available to split & merge text files. However, here is a simple one my friend uses to merge around 30 ascii files into one

It uses File System Object and you need to add a reference of Microsoft Scripting Runtime

Sub Append_Text_Files()

Dim oFS As FileSystemObject
Dim oFS1 As FileSystemObject

Dim oTS As TextStream
Dim oTS1 As TextStream

Dim vTemp

Set oFS = New FileSystemObject
Set oFS1 = New FileSystemObject


For i1 = 1 To 30

Set oTS = oFS.OpenTextFile("c:\Sheet" & i1 & ".txt", ForReading)
vTemp = oTS.ReadAll

Set oTS1 = oFS.OpenTextFile("c:\CombinedTemp.txt", ForAppending, True)
oTS1.Write (vTemp)

Next i1

End Sub

The code is simple.. it searches for files from Sheet1.txt ...Sheet30.txt and copies the content into one variable. Then it appends the content to CombinedTemp.txt

3 comments:

  1. Anonymous11:59 AM

    May be more efficient if you move this line to the outside of the loop:

    Set oTS1 = oFS.OpenTextFile("c:\CombinedTemp.txt", ForAppending, True)

    ReplyDelete
  2. Thank you very much. I am able to start with the above post and solve a problem with 105 text files in about 30 minutes.

    I have to add an If statement to skip some non-existing file names. Also have to add .close command each time after the cumulative file is opened and appended.

    Thanks again.

    Sub Append_Text_Files()

    Dim oFS As FileSystemObject
    Dim oFS1 As FileSystemObject

    Dim oTS As TextStream
    Dim oTS1 As TextStream

    Dim vTemp

    Set oFS = New FileSystemObject
    Set oFS1 = New FileSystemObject


    For intCounter = 26 To 1231

    If Dir("C:\FileAppend\SVExport_" & intCounter & "2009.txt") <> "" Then
    Set oTS = oFS.OpenTextFile("C:\FileAppend\SVExport_" & intCounter & "2009.txt", ForReading)
    vTemp = oTS.ReadAll

    Set oTS1 = oFS.OpenTextFile("C:\FileAppend\CombinedTemp.txt", ForAppending, True)
    oTS1.Write (vTemp)
    oTS1.Close
    End If

    Next intCounter

    End Sub

    ReplyDelete
  3. Anonymous1:20 PM

    Can you do this in Excel VBA?

    ReplyDelete

StumbleUpon
Share on Facebook
Related Posts Plugin for WordPress, Blogger...

Visual Basic for Applications (VBA) Forum (recent threads)

CodeKeep VBA Feed

Visual Studio Tools for Office Forum (recent threads)

Download Windows Live Toolbar and personalize your Web experience! Add custom buttons to get the information you care about most.

Office Business Applications (OBA) Team Blog

MSDN Code Gallery Published Resources For Tag VSTO

microsoft.public.vsnet.vstools.office Google Group