а почему Worksheets(1).CodeName ?  
Worksheets(1).Name, разьве нет?

Изучаем АДО :)  
 
Sub ertert()  
ActiveSheet.[a1] = OnlyOneSheetName("D:\Загрузка\Книга1.xls")
End Sub  
 
Function OnlyOneSheetName(FileName As String) As String 'нужна ссылка Microsoft ActiveX Data Objects 6.0 Library  
Dim sPrv As String, sConStr As String  
If Val(Application.Version) < 12 Then  
   sPrv = "Microsoft.Jet.OLEDB.4.0": sConStr = "Data Source=" & FileName & ";Extended Properties=Excel 8.0;"  
Else  
   sPrv = "Microsoft.ACE.OLEDB.12.0": sConStr = "Data Source=" & FileName & ";Extended Properties=Excel 12.0;"  
End If  
With New ADODB.Connection  
   .Provider = sPrv: .ConnectionString = sConStr: .CursorLocation = adUseClient: .Open  
   With .OpenSchema(adSchemaTables)  
       OnlyOneSheetName = Replace(.Fields("TABLE_NAME").Value, "$", ""): .Close  
   End With: .Close  
End With  
End Function  
 
Путь свой поставьте.

"Вот в этой теме: http://www.planetaexcel.ru/forum.php?thread_id=13569"  
 
Да-да, именно из этой темы.  
И еще отсюда немного: http://www.rondebruin.nl/ado.htm  
Пытаюсь как-то освоить это дело :)