Microsoft Office Tutorials and References
In Depth Information
VBA Techniques
End If
‘ Get other filenames
FileName = Dir
If FileName = “” Then Exit Do
FoundFiles = FoundFiles + 1
ReDim Preserve FileList(1 To FoundFiles)
FileList(FoundFiles) = FileName & “*”
‘ Loop through the files and process them
For i = 1 To FoundFiles
Call ProcessFiles(FileList(i))
Next i
End Sub
This example, named batch processing.xlsm , is on the companion CD-ROM. It uses
three additional files (also on the CD): text01.txt , text02.txt , and text03.txt .
You’ll need to modify the routine to import other text files.
The matching filenames are stored in an array named FoundFiles , and the procedure uses a
For-Next loop to process the files. Within the loop, the processing is done by calling the
ProcessFiles procedure, which follows. This simple procedure uses the OpenText method to
import the file and then inserts five formulas. You may, of course, substitute your own routine in
place of this one:
Sub ProcessFiles(FileName As String)
‘ Import the file
Workbooks.OpenText FileName:=FileName, _
Origin:=xlWindows, _
StartRow:=1, _
DataType:=xlFixedWidth, _
FieldInfo:= _
Array(Array(0, 1), Array(3, 1), Array(12, 1))
‘ Enter summary formulas
Range(“D1”).Value = “A”
Range(“D2”).Value = “B”
Range(“D3”).Value = “C”
Range(“E1:E3”).Formula = “=COUNTIF(B:B,D1)”
Range(“F1:F3”).Formula = “=SUMIF(B:B,D1,C:C)”
End Sub
For more information about working with files using VBA, refer to Chapter 27.
Search JabSto ::

Custom Search