Discussion on:

10 ways to reference Excel workbooks and sheets using VBA

Here's my take on an 2007-friendly OpenAllWB alternative. This uses Microsoft Scripting Runtime to look for files:

'******************************************************************************
'* Purpose :
'* This procedure opens all Excel files found within a given directory.
'* Input :
'* strFolder, the folder to look for Excel files within
'******************************************************************************
Sub OpenAllWBInFolder(strFolder As String)
On Error GoTo ErrorHandler:
Dim objFSO As Object
Dim objDir As Object
Dim objFile As Object
Dim lngWbCnt As Long

Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objDir = objFSO.GetFolder(strFolder)
lngWbCnt = Workbooks.Count

'Loop all files in given directory, looking for Excel files
For Each objFile In objDir.Files
If objFile.Type Like "Microsoft Office Excel*Worksheet" Then
Workbooks.Open objFile.Path
End If
Next objFile

'Warn the user if no files was found to open
If lngWbCnt = Workbooks.Count Then
MsgBox "There are no workbooks to open", vbOKOnly + vbInformation, _
"OpenAllWBInFolder"
End If

ExitProc:
On Error Resume Next
Set objFSO = Nothing
Set objDir = Nothing
Set objFile = Nothing
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 5, 76
' 5 = Invalid procedure call or argument
'76 = Path not found
MsgBox "Invalid path specified", vbOKOnly + vbInformation, _
"OpenAllWBInFolder"
Case 429 'Can't create ActiveX
'Microsoft scripting runtime probably not installed
MsgBox "'Microsoft scripting runtime' must be installed in order" _
& vbCr & "for this procedure to work.", _
vbOKOnly + vbInformation, "OpenAllWBInFolder"
Case Else
MsgBox "[" & Err.Source & "]" & vbCrLf & "An unexpected run-time" _
& " error occured '" & CStr(Err.Number) & "':" & vbCrLf _
& vbCrLf & Err.Description, vbExclamation, "OpenAllWBInFolder", _
Err.HelpFile, Err.HelpContext
End Select
Resume ExitProc:
End Sub