Combining Text Files using VBA

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

Query Table with Excel as Data Source

Sub Excel_QueryTable()

Dim oCn As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim ConnString As String
Dim SQL As String

Dim qt As QueryTable

ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\SubFile.xls;Extended Properties=Excel 8.0;Persist Security Info=False"
Set oCn = New ADODB.Connection
oCn.ConnectionString = ConnString
oCn.Open

SQL = "Select * from [Sheet1$]"

Set oRS = New ADODB.Recordset
oRS.Source = SQL
oRS.ActiveConnection = oCn
oRS.Open

Set qt = Worksheets(1).QueryTables.Add(Connection:=oRS, _
Destination:=Range("B1"))

qt.Refresh

If oRS.State <> adStateClosed Then
oRS.Close
End If


If Not oRS Is Nothing Then Set oRS = Nothing
If Not oCn Is Nothing Then Set oCn = Nothing

End Sub

Query Table for Text / CSV Files

If you update some Excel frequently, you can keep it as shared and then ask your fellow colleagues to check if often (refresh)

One of the good option is to have them as CSV file and use query table to update it regularly

Sub TXT_QueryTable()

Dim ConnString As String

Dim qt As QueryTable

ConnString = "TEXT;C:\Temp.txt"


Set qt = Worksheets(1).QueryTables.Add(Connection:=ConnString, _
Destination:=Range("B1"))

qt.Refresh

End Sub

The Refresh method causes Microsoft Excel to connect to the query table’s data source, execute the SQL query, and return data to the query table destination range. Until this method is called, the query table doesn’t communicate with the data source.

SQL Server 2005 - Remote Connection

If you have downloaded the express edition, you may not connect to the server from a remote machine.

In the SQL Server Configuration Manager (SSCM) enable the Remote Connections Option

Under the SQL Native Client Configuration enable TCP/IP protocol.

ADO connection string for Excel

If are a diehard ADO user. Here is the connection string for Excel

sXL = "c:\CTWebEg.xls"
Set Cn = New ADODB.ConnectionCn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sXL & ";Extended Properties=Excel 8.0;Persist Security Info=False"Cn.ConnectionTimeout = 40

Cn.Open

The rest is the usual ADO recordset retrieving technique