Processing a series of files

One common use for macros, of course, is to repeat an operation a number of times. The example in this section 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.

In previous editions of this book, I used the FileSearch object. Excel 2007 no longer supports the FileSearch object, so I resorted to the more cumbersome Dir function.

Sub BatchProcess()

Dim FileSpec As String Dim i As Integer Dim FileName As String Dim FileList() As String Dim FoundFiles As Integer

' Specify path and file spec

FileSpec = ThisWorkbook.Path & "\" & "text??.txt" FileName = Dir(FileSpec)

If FileName <> "" Then FoundFiles = 1

ReDim Preserve FileList(1 To FoundFiles) FileList(FoundFiles) = FileName

Else

MsgBox "No files were found that match " & FileSpec Exit Sub End If

' Get other filenames Do

FileName = Dir

If FileName = "" Then Exit Do FoundFiles = FoundFiles + 1 ReDim Preserve FileList(1 To FoundFiles) FileList(FoundFiles) = FileName & "*"

Loop

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

Call ProcessFiles(FileList(i)) Next i End Sub

CD-ROM

This example, named ® batch processing.xlsm , is on the companion CD-ROM. It uses three additional files (also on the CD): textoi.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:= _

' Enter summary formulas Range("D1").Value = "A" Range("D2").Value = "B" Range("D3").Value = "C" Range("E1:E3").Formula = Range("F1:F3").Formula = End Sub

CROSS-REFERENCE

"=COUNTIF(B:B,D1)" "=SUMIF(B:B,D1,C:C)"

For more information about working with files using VBA, refer to Chapter 27

4 PREV

NEXT

0 0

Post a comment