Listing A Robust Batch Workbook Processing Framework

Sub ProcessFileBatch() Dim nIndex As Integer Dim vFiles As Variant Dim wb As Workbook Dim bAlreadyOpen As Boolean

On Error GoTo ErrHandler

' Get a batch of Excel files vFiles = GetExce1Fi1es("Se1ect Workbooks for Processing")

' Make sure the dialog wasn't cancelled - in which case ' vFiles would equal False and therefore wouldn't be an array. If Not IsArray(vFiles) Then

Debug.Print "No files selected."

Exit Sub End If

Application.ScreenUpdating = False

' OK - loop through the filenames For nIndex = 1 To UBound(vFiles)

If IsWorkbookOpen(CStr(vFi1es(nIndex))) Then

Set wb = Workbooks(GetShortName(CStr(vFi1es(nIndex)))) Debug.Print "Workbook already open: " & wb.Name bAlreadyOpen = True

Else

Set wb = Workbooks.Open(CStr(vFi1es(nIndex)), False) Debug.Print "Opened workbook: " & wb.Name bAlreadyOpen = False

End If

Application.StatusBar = "Processing workbook: " & wb.Name

' Code to process the file goes here

Debug.Print "If we wanted to do something to the " & _ "workbook, we would do it here."

' Close workbook unless it was already open

If Not bAlreadyOpen Then

Debug.Print "Closing workbook: " & wb.Name wb.Close True

End If Next nIndex

' Clean up Set wb = Nothing

ErrHandler:

Application.StatusBar = False Application.ScreenUpdating = True End Sub

0 0

Post a comment