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

Visual Basic - Special Folders (Temp Folder , System Folder)

Here is a simple routine to get special folders like temporary folder etc:

Sub Get_Special_Folders()

' Uses File System Object
' Need to have reference to Microsoft Scripting Runtime

On Error GoTo Show_Err

Dim oFS As FileSystemObject
Dim sSystemFolder As String
Dim sTempFolder As String
Dim sWindowsFolder As String

Set oFS = New FileSystemObject

' System Folder - Windows\System32
sSystemFolder = oFS.GetSpecialFolder(SystemFolder)

' Temporary Folder Path
sTempFolder = oFS.GetSpecialFolder(TemporaryFolder)

' Windows Folder Path
sWindowsFolder = oFS.GetSpecialFolder(WindowsFolder)

Dim a
a = oFS.GetFolder("m:\9.3 BulkLoad\BLT1_Base15.6\Reports\08-Nov-2007\Output\")

If Not oFS Is Nothing Then Set oFS = Nothing

Show_Err:

If Err <> 0 Then
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End If
End Sub

You need to have reference to Microsoft Scripting Runtime to execute the above code

Primitive File Handling Functions

Copying Files/ Deleting Files / Moving Files

Sub Moving_Files()

Dim oFS As FileSystemObject

' Copying Files
FileCopy "c:\temp\IamAStar.xls", "c:\temp\backup\IamAStar.xls"

' Deleting Files
Kill "c:\temp\IamAStar.xls"

' Using File System Object - You need to Include Microsoft Scripting Runtime in you references
Set oFS = New FileSystemObject

' Moving Files
oFS.MoveFile "c:\temp\D8C7I12.xls", "c:\temp\backup\D8C7I12.xls"

End Sub