CODEWITHSUNDEEP
vbaexcel
samcooper11
samcooper11

Reputation: 275

Concatenating files in VBA

I want to concatenate all .dat files in a specific directory (always different names) into one text file. Can anyone point me in the right direction?

Upvotes: 0

Views: 3793

Answers (3)

Mr Red
Mr Red

Reputation: 1

I thought the above example might be faster than my version below But on a 2 Mb audio file the above took 31 seconds cf 19 below and Len = 1 or 100 or 1000 made no difference. Maybe the DOS wheeze is faster. My files can be hour long @ 128Kb baud rate.

 n=0
 k=0
 Do Until LenB(sdir) = 0
    inFileNum = FreeFile
    Open spath + sdir For Binary Access Read Lock Read As inFileNum Len = 1000
    Do While Not EOF(inFileNum)
      Get inFileNum, k, bytTemp
      k=k+1
      Put outFileNum, n, bytTemp
      n=n+1
    Loop

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91316

You can run dos under VBA:

Set oWSH = CreateObject("WScript.Shell")
    oWSH.Run ("%comspec% /c copy c:\docs\conc\*.dat c:\docs\conc\onefile.txt")

You will need the /b switch if the files have a terminator and if the files do not end in a return, the lines will continue:

abc
defabc
def

Upvotes: 2

Dr. belisarius
Dr. belisarius

Reputation: 61016

Sub a()
   Dim spath As String
   Dim intFileNum%, bytTemp As Byte
   spath = "c:\kk\"
   outFileNum = FreeFile
   Open spath + "Outfile.out" For Binary Access Write As outFileNum
   sdir = Dir$(spath & "*.dat", vbNormal)
   Do Until LenB(sdir) = 0
        inFileNum = FreeFile
        Open spath + sdir For Binary Access Read Lock Read As inFileNum
        Do While Not EOF(inFileNum)
          Get inFileNum, , bytTemp
          Put outFileNum, , bytTemp
        Loop
        Close inFileNum
        sdir = Dir$
   Loop
   Close outFileNum
End Sub

The only trick is that the Dir$() has a weird iteration convention

HTH!

Upvotes: 1

Related Questions