'-------------
'The information in this article applies to:
'
'Microsoft Excel 2000 and later.
'External reference(s):
'Microsoft Scripting Runtime.
'
'--------------------------------------------------------------------------------
'Task
' Calculate the average of values in the same ranges from several closed 'workbooks and write the values to the active sheet.
'In the example it's assumed that the sheetnames are similar to the filenames and therefore the sheetnames can be extracted from the filenames.
Code
Option Explicit
Sub Calculate_Get_Values_Closed_Workbooks()
Dim fsoObj As Scripting.FileSystemObject
Dim fsoFolder As Scripting.Folder
Dim fsoFile As Scripting.File
Dim stPath As String
Dim i As Long, j As Long
Dim vaFiles As Variant, vaSheets As Variant
With Application
.EnableEvents = False
.ScreenUpdating = False
End With
'The path to the target folder.
stPath = "C:\Test\"
Set fsoObj = New Scripting.FileSystemObject
Set fsoFolder = fsoObj.GetFolder(stPath)
'Iterate through the collection of files and
'write the file- and the sheetnames to the active sheet.
For Each fsoFile In fsoFolder.Files
With ActiveSheet
.Range("A" & i + 1).Value = fsoFile.Name
.Range("B" & i + 1).Value = Left(fsoFile.Name, (Len(fsoFile.Name) - 4))
End With
i = i + 1
Next fsoFile
'Populate the two arrays.
With ActiveSheet
vaFiles = .Range(.Range("A1"), .Range("A65536").End(xlUp)).Value
vaSheets = .Range(.Range("B1"), .Range("B65536").End(xlUp)).Value
End With
'Iterate through the two arrays of files and sheets and
'calculate the average and write the values to the active sheet.
For j = 1 To UBound(vaFiles)
ActiveSheet.Range("C" & j).Value = _
Application.ExecuteExcel4Macro( _
"AVERAGE('" & stPath & "[" & vaFiles(i, 1) & "]" & vaSheets(i, 1) & "'!R1C5:R60C5)")
Next
With Application
.EnableEvents = False
.ScreenUpdating = True
End With
'Release objects from memory.
Set fsoFile = Nothing
Set fsoFolder = Nothing
End Sub
'Comments
' By using the above approach it's possible to use several of the built-in 'functions MS Excel provides including LOOKUP-functions. The technique itself, 'i e using Application.ExecuteExcel4Macro, is both fast and reliable. A small 'note is that it may be difficult to get the required string-expression correct 'the first time.
'The approach can be a good alternative to the ADO & SQL-based approach.
'------------------------
