Soru Ado ile kapalı excel son satır no ve adres bulma

[FERAZ]

Korhan Beyin de dediği gibi, kapalı dosyayı eğer ADO ile ulaşarak veri tabanı olarak kullanmak istiyorsak, olmazsa olmazların başında sütun başlıkları, ve sütunlardaki verilerin aynı cinste olmaları gerekliliğidir. Ayrıca, hücrelerin boş olmaması da bir çok yönden kolaylık sağlar....

Benim daha önce önerdiğim kod, Zafer beyin verdiği örnek dosyada sütun başlığı olması halinde çalışıyordu ancak, eğer sayfada A sütununun yanındaki B sütununda da veriler varsa ve bunlar A sütunundan daha da aşağıya doğru giderse, ADO her 2 sütunu da tek 1 tablo olarak algılayıp, A sütunundaki en son veriyi "null" olarak algılıyordu. (B sütunundaki en son veriye karşılık gelen satırdaki A sütunu hücresi)

Bunu önlemek üzere, kodu biraz daha geliştirdim ve hız kazandırmak için döngü kullanmadım. Örnek dosyalar hem mesaj ekine hem de harici linke yüklenmiştir. Örnek olarak verilen dosyalardan "kitap.xlsx" dosyasındaki veriler "String" tipindedir....

Örnek dosya harici linki:

Link silindi...


.

Haluk hocam birde A sütunundaki verileri silip denedim B sütunundaki son satır no bulunuyor.

 

[Haluk]

Haluk hocam birde A sütunundaki verileri silip denedim B sütunundaki son satır no bulunuyor.

Bu normal, çünkü eğer sayfada sadece B sütununda veri varsa, o zaman F1 olarak tablonun (sayfanın) ilk sütunu olan B sütununu "birinci alan" olarak alacaktır.

.

 

[FERAZ]

Bu normal, çünkü eğer sayfada sadece B sütununda veri varsa, o zaman F1 olarak tablonun (sayfanın) ilk sütunu olan B sütununu "birinci alan" olarak alacaktır.

.

Kodu alttaki gibi değiştirince başarılı oldu.

Sub Test()
'   Haluk - 02/03/2020
'   sa4truss@gmail.com

    Dim adoCN As Object, RS As Object
    Dim myFile As String, strSQL As String
    Dim strLastData As String
    
    Const adOpenKeyset = 1
    Const sayfaa As String = "[Sayfa1$A:A]"
    
    myFile = ThisWorkbook.Path & Application.PathSeparator & "kapali.xlsx"
    
    Set adoCN = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
 
    adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
    adoCN.Properties("Data Source") = myFile
    adoCN.Properties("Extended Properties") = "Excel 12.0 Macro; HDR=No; IMEX=1"
    adoCN.Open
 
    strSQL = "Select [F1] from " & sayfaa & " where [F1] is not null"
    
    RS.CursorType = adOpenKeyset
    On Error GoTo son
    RS.Open strSQL, adoCN
    
        RS.MoveLast
        
        strLastData = RS(0)
        RS.Close
        
        strSQL = "Select [F1] from " & sayfaa & ""
        RS.Open strSQL, adoCN
        RS.MoveFirst
        RS.Find "[F1]= " & Chr(39) & strLastData & Chr(39)
        
        MsgBox "A sütununda son dolu hücrenin satýr numarasý: " & RS.AbsolutePosition
        
        adoCN.Close
        Set RS = Nothing
        Set adoCN = Nothing
        Exit Sub
        
son:

        MsgBox "A sütununda veri yok yada baska problem var...", vbCritical, "Hata"
        
        adoCN.Close
        Set RS = Nothing
        Set adoCN = Nothing
  
End Sub

 

[Haluk]

Ben, "On Error Resume Next" komutunu kullanmaktan genelde çekinirim. Bazı durumlarda yanlış yönlendirmeler olabiliyor...

O yüzden, bu iş için aşağıdaki gibi bir yöntem izlenebilir....

Sub Test2()
'   Haluk - 03/03/2020
'   sa4truss@gmail.com

    Dim adoCN As Object, RS As Object
    Dim myFile As String, strSQL As String
    Dim strLastData As String
    
    Const adOpenKeyset = 1
    
    myFile = ThisWorkbook.Path & Application.PathSeparator & "kapali.xlsx"
    
    Set adoCN = CreateObject("ADODB.Connection")
    Set RS = CreateObject("ADODB.Recordset")
 
    adoCN.Provider = "Microsoft.ACE.OLEDB.12.0"
    adoCN.Properties("Data Source") = myFile
    adoCN.Properties("Extended Properties") = "Excel 12.0 Macro; HDR=No; IMEX=1"
    adoCN.Open
 
    strSQL = "Select [F1] from [Sayfa1$] where [F1] is not null"
    
    RS.CursorType = adOpenKeyset
    RS.Open strSQL, adoCN
    
    If RS.RecordCount > 0 Then
        RS.MoveLast
        strLastData = RS(0)
        RS.Close
    Else
        MsgBox "Tablonun 1.sütununda veri bulunamadı ..."
        Exit Sub
    End If
    
    strSQL = "Select [F1] from [Sayfa1$]"
    RS.Open strSQL, adoCN
    RS.MoveFirst
    RS.Find "[F1]= " & Chr(39) & strLastData & Chr(39)
    
    MsgBox "Tablonun 1.sütununda son dolu hücrenin satır numarası: " & RS.AbsolutePosition
    
    RS.Close
    adoCN.Close
    Set RS = Nothing
    Set adoCN = Nothing
End Sub

.

 

[FERAZ]

On error resumeden bende çekiniyorum.Sizin yöntemi yani recırdcount böyle yapıncada bazen hata oluyordu alttaki gibi tanımlayıp ilgili yerede ekleyince değişkeni sorun kalktı.

Sizin son eklediğiniz kod gibi yapınca A boşsa zaten B ninkini veriyordu.
Yarın birde böyle deneyeyim.

Const sayfaa As String = "[Sayfa1$A:A]"

 

[Erdem Akdemir]

Merakınızı gidermek için mi soruyorsunuz?
Gerçekte böyle database tutmak iyi birşey değil.

 

[FERAZ]

Merakınızı gidermek için mi soruyorsunuz?
Gerçekte böyle database tutmak iyi birşey değil.

Her türlü olasılığı düşünmek gerek abey.
Bir projeye felan şimdilik uygulamayacağım fakat merak edip duruyordum bunun çözümünü.

 

[Haluk]

......
...
Sizin son eklediğiniz kod gibi yapınca A boşsa zaten B ninkini veriyordu.
.....
..

24 No'lu mesajda verdiğim en son kodda herhangi bir problem yok. Ancak; sizin dikkat etmeniz gereken önemli bir husus var....

Siz eğer A sütununu seçip, verileri sildiğiniz zaman bu sütunu "boş" olarak görüyor ve sayfada sadece B sütunu gördüğünüz için sadece 1 sütun veri olduğunu düşünürsünüz ama; ADO içeriği silinen hücreleri "Null" olarak görür ve tabloda (yani sayfada) halen 2 sütun olduğunu düşünerek hareket eder.

Sadece, eğer A sütununu komple seçip mouse'un"Delete" tuşuna basarak sütunu Excel'den kaldırdıktan sonra yani, eski B sütunu şimdi yeni A sütunu olduktan sonra, buraya tekrar bir sütun ilave ettiğinizde elinizde gerçek anlamda içinde hiçbir şey olmayan bir sütun elde edebilirsiniz. Bu durumda; sayfada A sütunu boş ve B sütununda veriler varken ADO bu sefer sayfayı (tabloyu), içinde sadece 1 adet alan olan bir tablo olarak algılar ve o zaman kodlarda B sütununu "F1" olarak ifade edebiliriz.

Diğer bir deyişle, A sütununda veri varken sütunu yok etmeden sadece hücreleri temizlediğiniz zaman ADO 2 adet alan olarak algılayacağından, doğru sonuçlar elde etmek için A sütununu F1, B sütununu ise F2 olarak refere etmek gerekir.

.

 

[FERAZ]

Haluk hocam öncedende yazmıştınız A1 de mutlaka başlık olacak diye.
Denemelerimde ilk satırlar boşsa yani tüm sütunların ilk satırları boşsa tam çalışmıyor fakat herhangi bir sütunun ilk satırı doluysa doğru çalışıyor.

Bunun için bir çzüm varmıdır?Ben biraz uğraşıyorum.Sanırım ilk herhangi bir sütunun ilk satırı boşsa kodunu Ado ile yapabilirsem sorunu çözecek gibiyim.