Processing a series of files

One common use for macros, of course, is to repeat an operation a number of times. The example in Listing 11-8 demonstrates how to execute a macro on several different files stored on disk. This example — which may help you set up your own routine for this type of task —prompts the user for a file specification and then processes all matching files. In this case, processing consists of importing the file and entering a series of summary formulas that describe the data in the file.

Listing 11-8: A Macro That Processes Multiple Stored Files

Sub BatchProcess()

Dim FS As FileSearch

Dim FilePath As String, FileSpec As String Dim i As Integer


Listing 11-8 (Continued)

' Specify path and file spec

FilePath = ThisWorkbook.Path & "\" FileSpec = "text??.txt"

' Create a FileSearch object

Set FS = Application.FileSearch With FS

.LookIn = FilePath .FileName = FileSpec .Execute

Exit if no files are found If .FoundFiles.Count = 0 Then MsgBox "No files were found" Exit Sub End If End With

' Loop through the files and process them For i = 1 To FS.FoundFiles.Count

Call ProcessFiles(FS.FoundFiles(i)) Next i

End Sub

This example uses three additional files,which are also provided on the CD-ROM: Text01.txt, Text02.txt,and Text03.txt.You'll need to modify the routine to import other text files.This procedure uses the FileSearch object, so it will work in Excel 2000 or later.

The matching files are retrieved by the FileSearch object, 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, _

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

0 0

Post a comment