Kapalı Dosyada ilk sayfayı alma

[tamer42]

Merhaba,
Kapalı dosyadan sayfa ismini almak için aşağıdaki kodda:
"Dış Tablo, beklenen biçimde değil" hata mesajını alıyorum.

Sub Testt()

   MsgBox getworksheetname(Application.GetOpenFilename("Excel Files (*.xls), *.xls"))

End Sub

Function getworksheetname(sWorkbook As String)

    Dim adoConnection As New ADODB.Connection

    adoConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sWorkbook & ";Extended Properties=""Excel 8.0;HDR=Yes;"""

    adoConnection.Open
   
    Dim adoWbkAsDatabase As New ADOX.Catalog
    adoWbkAsDatabase.ActiveConnection = adoConnection
      
    MsgBox adoWbkAsDatabase.Tables(0).Name
    
    adoConnection.Close

    Set adoConnection = Nothing
    Set adoWbkAsDatabase = Nothing
    
End Function

teşekkürker,
iyi Çalışmalar.

 

[Haluk]

DAO ile bu şekilde kullanın;

Sub Test_HD()
   MsgBox getworksheetname_HD(Application.GetOpenFilename("Excel Files (*.xls), *.xls"))
End Sub
'
Function getworksheetname_HD(sWorkbook As String)
    Dim daoDBEngine As Object, DB As Object, MySheet As String
    
    Set daoDBEngine = CreateObject("DAO.DBEngine.120")
        
    Set DB = daoDBEngine.OpenDatabase(sWorkbook, False, False, "Excel 8.0; HDR=Yes; IMEX=1;")
    
    MySheet = VBA.Replace(DB.TableDefs(0).Name, "'", "")
    MySheet = Application.Substitute(MySheet, "'", "")
    MySheet = Left(MySheet, InStr(1, MySheet, "$", 1) - 1)
    
    getworksheetname_HD = MySheet
End Function

.

 

[tamer42]

DAO ile bu şekilde kullanın;

Sub Test_HD()
   MsgBox getworksheetname_HD(Application.GetOpenFilename("Excel Files (*.xls), *.xls"))
End Sub
'
Function getworksheetname_HD(sWorkbook As String)
    Dim daoDBEngine As Object, DB As Object, MySheet As String
   
    Set daoDBEngine = CreateObject("DAO.DBEngine.120")
       
    Set DB = daoDBEngine.OpenDatabase(sWorkbook, False, False, "Excel 8.0; HDR=Yes; IMEX=1;")
   
    MySheet = VBA.Replace(DB.TableDefs(0).Name, "'", "")
    MySheet = Application.Substitute(MySheet, "'", "")
    MySheet = Left(MySheet, InStr(1, MySheet, "$", 1) - 1)
   
    getworksheetname_HD = MySheet
End Function

.

çok teşekkürler Haluk hocam, iyiki varsınız!