I tried solution you proposed, without having a reference to VBA Ext.
Results are the same when this reference was set on.
Final test I made:

1. Created new workbook
2. Added several sheets more and renamed them as "Summary Report1",
"SummaryReport1", "Summary Report2", "SummaryReport2", etc..
3. Run following macro from another workbook.

-------------------------------
Sub Test()

Dim MyBook As Workbook
Dim Wks As Worksheet
Dim MyFiles As Variant
Dim VBC As Variant
Dim MyText As String

10 MyFiles = Application.GetOpenFilename("Excel files, *.xls", , "Select file...", "Select", True)

20 If IsArray(MyFiles) Then
30 For Each f In MyFiles
40 Set MyBook = Application.Workbooks.Open(f, , True)

50 For Each Wks In MyBook.Worksheets
60 MyText = MyText & "Worksheet Name: " & Wks.Name & vbTab & " CodeName: " & Wks.CodeName & vbCrLf
70 Next

80 MsgBox MyText, , "Based on Worksheets collection"
90 MyText = ""

100 For Each VBC In MyBook.VBProject.VBComponents
110 MyText = MyText & "Worksheet Name: " & VBC.Properties("Name").Value & vbTab & " CodeName: " & VBC.Name & vbCrLf
120 Next

130 MsgBox MyText, , "Based on VBComponents collection"
140 MyBook.Close False

150 Set MyBook = Nothing
160 Next
170 End If

End Sub
-------------------------------

Results:

1. Going through worskheets collection:

Worksheet Name: Summary Report1 CodeName: Sheet1
Worksheet Name: SummaryReport1 CodeName: Sheet2
Worksheet Name: Summary Report2 CodeName: Sheet3
Worksheet Name: SummaryReport2 CodeName: (Empty!)
Worksheet Name: Summary Report3 CodeName: (Empty!)
Worksheet Name: SummaryReport3 CodeName: (Empty!)

It looks newly last three sheets I added are somehow wrongly interpreted.

2. Reaching second For..Each loop (line 100) the same error 1004 raises.
"Method 'VBProject' of object '_Workbook' failed"