Özellik ikonu

ADO

İndirme izni yok

Genel Bakış

ADO (ActiveX Data Objects), VBA kodundan Excel çalışma kitapları, Access, SQL Server gibi veri kaynaklarına bağlanıp SQL sorgusu çalıştırmanızı sağlayan nesne kitaplığıdır. Veriyi hücre hücre dolaşmadan tek bir sorguyla süzmenizi, gruplamanızı, toplamanızı ya da veritabanına kayıt eklemenizi sağlar. Büyük veri kümelerinde ve harici veritabanlarıyla çalışırken döngülü hücre okumaya göre çok daha hızlı ve düzenli bir çözümdür.

ADO nedir ve nasıl kurulur​

ADO, bir OLE DB sağlayıcısı üzerinden veri kaynağına bağlanır. Excel dosyaları için Access Database Engine (ACE) sağlayıcısı kullanılır; bilgisayarda yüklü olmalıdır. Excel dosyasında her çalışma sayfası bir tablo gibi görülür ve sorguda [Sayfa Adı$] biçiminde köşeli parantez ve dolar işaretiyle yazılır.

ADO'yu iki yolla kullanabilirsiniz:
  • Geç bağlama (late binding): CreateObject("ADODB.Connection") ile nesne oluşturulur. Başvuru eklemek gerekmez, kod farklı bilgisayarlarda sorunsuz çalışır. Sabitlerin (adOpenStatic gibi) adları bilinmez, sayısal değerleri yazılır.
  • Erken bağlama (early binding): Visual Basic Düzenleyicisi'nde Araçlar > Başvurular (Tools > References) penceresinden Microsoft ActiveX Data Objects 6.1 Library işaretlenir. Nesneler Dim cn As ADODB.Connection biçiminde tanımlanır; IntelliSense ve sabit adları kullanılabilir.
Çok kullanıcılı dağıtımlarda geç bağlama genellikle daha güvenlidir, çünkü başvuru sürümü uyuşmazlığından kaynaklanan hatalar oluşmaz.

Önemli nesneler​

ADO modelinin temel nesneleri şunlardır:
  • Connection: Veri kaynağına açılan bağlantıdır. Open, Execute, Close, BeginTrans, CommitTrans, RollbackTrans yöntemleri ve ConnectionString özelliği vardır.
  • Recordset: Sorgu sonucunda dönen kayıt kümesidir. Open, MoveNext, GetRows, Close yöntemleri; EOF, BOF, Fields, RecordCount özellikleri kullanılır.
  • Command: Parametreli sorgu ve saklı yordam çalıştırmak için kullanılır. CommandText, ActiveConnection, Parameters özellikleri ve CreateParameter, Execute yöntemleri bulunur.
  • Field: Recordset içindeki bir sütundur; Name ve Value özelliklerine sahiptir.
Sonucu çalışma sayfasına yazmanın en hızlı yolu Excel'in Range.CopyFromRecordset yöntemidir. Başlıkları ise rs.Fields(i).Name ile ayrıca yazarsınız.

Bağlantı dizesi ve sabitler​

Bağlantı dizesi sağlayıcıyı, dosya yolunu ve Excel için ek özellikleri belirtir. Sık kullanılan biçimler:
VBA 8 satır 
' xlsx dosyası
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Satis.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";

' xlsm dosyası
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Satis.xlsm;Extended Properties="Excel 12.0 Macro;HDR=YES";

' Access veritabanı
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Ik.accdb;
Extended Properties içindeki seçenekler:
  • HDR=YES: İlk satır başlık sayılır ve alan adı olarak kullanılır. HDR=NO verilirse alanlar F1, F2 gibi adlanır.
  • IMEX=1: Karışık veri türü içeren sütunları metin olarak okur.
  • Excel 12.0 Xml: xlsx; Excel 12.0 Macro: xlsm; Excel 12.0: xlsb; Excel 8.0: xls dosyaları içindir.
Geç bağlamada sık kullanılan sayısal sabitler: adOpenForwardOnly = 0, adOpenStatic = 3, adLockReadOnly = 1, adLockOptimistic = 3, adCmdText = 1, adExecuteNoRecords = 128, adVarWChar = 202, adCurrency = 6, adParamInput = 1.

1. Sayfadaki bilgiden bağlantı dizesi ve SQL üretme​

Bu örnek ADO nesnesi oluşturmaz; ADO'da kullanılacak bağlantı dizesini ve SQL sorgusunu sayfadaki dosya yolu, sayfa adı ve şehir bilgisinden üretir. Kodu standart bir modüle (Ekle > Modül) yapıştırın. Tek tırnak içeren değerlerde her tek tırnak SQL'de iki tek tırnak olarak yazılır (tırnak ikilenir).
VBA 21 satır 
Option Explicit

Sub BaglantiDizesiHazirla()
    Dim ws As Worksheet
    Dim r As Long
    Dim dosya As String, sayfa As String, sehir As String

    Set ws = ActiveSheet

    For r = 2 To 4
        dosya = ws.Cells(r, 1).Value
        sayfa = ws.Cells(r, 2).Value
        ' SQL metninde tek tırnak çakışmasın diye ikiye katlanır
        sehir = Replace(ws.Cells(r, 3).Value, "'", "''")

        ws.Cells(r, 4).Value = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dosya & _
            ";Extended Properties=""Excel 12.0 Xml;HDR=YES"";"

        ws.Cells(r, 5).Value = "SELECT * FROM [" & sayfa & "$] WHERE Şehir = '" & sehir & "'"
    Next r
End Sub
ADO
Formülünü incelemek için bir hücre seçin.
ABCDE
1Dosya YoluSayfa AdıŞehirBağlantı DizesiSQL Sorgusu
2C:\Veri\Satis2024.xlsxSatışlarAnkaraProvider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Satis2024.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";SELECT * FROM (Satışlar$) WHERE Şehir = 'Ankara'
3C:\Veri\Stok.xlsxStokİzmirProvider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Stok.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";SELECT * FROM (Stok$) WHERE Şehir = 'İzmir'
4C:\Veri\Personel.xlsxPersonelBursaProvider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Personel.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";SELECT * FROM (Personel$) WHERE Şehir = 'Bursa'
Bağlantı PlanıExcelDepo v1.0.0
Makro her satır için iki metin üretir. İlk satırın bağlantı dizesi D2 hücresinde (Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Satis2024.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";), sorgusu E2 hücresinde (SELECT * FROM [Satışlar$] WHERE Şehir = 'Ankara') yer alır. Bu metinleri Connection.Open ve Connection.Execute çağrılarına doğrudan verebilirsiniz. Sorguda sayfa adının köşeli parantez içinde ve dolar işaretiyle yazıldığına dikkat edin.

2. Recordset ile şehre göre toplam alma​

Bu örnekte açık çalışma kitabındaki Satış Verisi sayfası ADO ile sorgulanır ve şehir bazında tutar toplamı F1 hücresinden başlayarak yazılır. Kodu standart modüle yapıştırın. Kitap .xlsm olarak kaydedilmiş olmalıdır; xlsx ise bağlantı dizesindeki Excel 12.0 Macro ifadesini Excel 12.0 Xml yapın. Bu örnek CreateObject ve dosya yolu kullandığı için burada çalıştırılmaz.
VBA 36 satır 
Option Explicit

Sub SehirBazliToplam()
    Dim cn As Object, rs As Object
    Dim sql As String

    On Error GoTo Hata

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.FullName & _
        ";Extended Properties=""Excel 12.0 Macro;HDR=YES"";"

    sql = "SELECT Şehir, SUM(Tutar) AS Toplam FROM [Satış Verisi$] GROUP BY Şehir"
    Set rs = cn.Execute(sql)

    With ActiveSheet
        .Range("F1").Value = "Şehir"
        .Range("G1").Value = "Toplam"
        ' Kayıt kümesini F2 hücresinden başlayarak yazar
        .Range("F2").CopyFromRecordset rs
        .Range("G2:G10").NumberFormat = "#,##0.00"
    End With

Temizle:
    If Not rs Is Nothing Then
        If rs.State = 1 Then rs.Close
    End If
    If Not cn Is Nothing Then
        If cn.State = 1 Then cn.Close
    End If
    Exit Sub

Hata:
    MsgBox "Sorgu çalıştırılamadı: " & Err.Description
    Resume Temizle
End Sub
ADO
Formülünü incelemek için bir hücre seçin.
ABCD
1ÜrünŞehirAdetTutar
2Klavyeİstanbul123.600,00
3FareAnkara253.125,00
4Monitörİzmir621.000,00
5Kulaklıkİstanbul157.500,00
6YazıcıBursa414.000,00
7Fareİstanbul303.750,00
8KlavyeAnkara103.000,00
9MonitörAnkara517.500,00
Satış VerisiExcelDepo v1.0.0
Makro çalıştırıldığında sorgu GROUP BY ile şehirleri gruplar ve her şehrin tutar toplamını F ve G sütunlarına yazar. Sonuçta Ankara, Bursa, İstanbul ve İzmir için dört satır oluşur. ADO, kitabın diskte kaydedilmiş durumunu okur; kaydedilmemiş değişiklikler sorguya yansımaz. Bu nedenle sorgudan önce kitabı kaydetmeniz gerekir.

3. Command ve transaction ile Access'e kayıt ekleme​

Bu örnek sayfadaki personel listesini bir Access veritabanındaki Personel tablosuna ekler. Parametreli Command kullanıldığı için değerler SQL metnine birleştirilmez; tüm kayıtlar tek bir transaction içinde işlenir. Kodu standart modüle yapıştırın ve dosya yolunu kendi veritabanınıza göre değiştirin. Veritabanı dosyası gerektirdiği için burada çalıştırılmaz.
VBA 46 satır 
Option Explicit

Sub PersoneliAktar()
    Dim cn As Object, cmd As Object
    Dim ws As Worksheet
    Dim r As Long, son As Long

    Set ws = ActiveSheet
    son = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

    On Error GoTo Hata

    Set cn = CreateObject("ADODB.Connection")
    cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Veri\Ik.accdb;"

    Set cmd = CreateObject("ADODB.Command")
    Set cmd.ActiveConnection = cn
    cmd.CommandType = 1   ' adCmdText
    cmd.CommandText = "INSERT INTO Personel (Ad, Birim, Maas) VALUES (?, ?, ?)"

    ' Parametreler sırayla tanımlanır: 202 = adVarWChar, 6 = adCurrency, 1 = adParamInput
    cmd.Parameters.Append cmd.CreateParameter("p1", 202, 1, 50)
    cmd.Parameters.Append cmd.CreateParameter("p2", 202, 1, 30)
    cmd.Parameters.Append cmd.CreateParameter("p3", 6, 1)

    cn.BeginTrans
    For r = 2 To son
        cmd.Parameters(0).Value = ws.Cells(r, 1).Value
        cmd.Parameters(1).Value = ws.Cells(r, 2).Value
        cmd.Parameters(2).Value = ws.Cells(r, 3).Value
        cmd.Execute , , 128   ' adExecuteNoRecords
    Next r
    cn.CommitTrans

Temizle:
    If Not cn Is Nothing Then
        If cn.State = 1 Then cn.Close
    End If
    Exit Sub

Hata:
    On Error Resume Next
    cn.RollbackTrans
    MsgBox "Kayıtlar eklenemedi: " & Err.Description
    Resume Temizle
End Sub
ADO
Formülünü incelemek için bir hücre seçin.
ABC
1Ad SoyadBirimMaaş
2Ayşe DemirMuhasebe42.000,00
3Mehmet KayaSatış38.500,00
4Elif Yıldızİnsan Kaynakları40.000,00
5Can ÖzkanBilgi İşlem47.500,00
6Zeynep ArslanSatış39.000,00
PersonelExcelDepo v1.0.0
Makro, sayfadaki son dolu satıra kadar her kayıt için aynı Command nesnesini yeniden kullanır; yalnızca parametre değerleri değişir. Bu yöntem hem SQL enjeksiyonu ve tırnak sorunlarını önler hem de daha hızlıdır. Bir kayıtta hata olursa RollbackTrans ile o ana kadar eklenen tüm kayıtlar geri alınır, böylece tablo yarım veriyle kalmaz.

Kullanırken dikkat edin​

  • Sağlayıcı ve bit uyumu: ACE sağlayıcısı yüklü değilse bağlantı açılırken hata alırsınız. Office'in 32 bit ya da 64 bit olması ile yüklü sağlayıcının aynı türde olması gerekir; aksi durumda sağlayıcı bulunamadı hatası oluşur.
  • Açık kitabı sorgulamak: ADO, çalışma kitabının diskteki kaydedilmiş durumunu okur. Açık kitaptaki kaydedilmemiş değişiklikler sonuca yansımaz. Aynı kitabı sorgularken önce kaydetmeniz gerekir.
  • Sütun türü tahmini: Excel sürücüsü bir sütunun veri türünü ilk satırlara bakarak tahmin eder. Sayı ve metin karışık olan sütunlarda bazı değerler boş (Null) gelebilir. Bunu önlemek için bağlantı dizesine IMEX=1 ekleyin ya da sütunu tek türde tutun.
  • Bağlantıyı kapatın: Recordset ve Connection nesnelerini işiniz bitince Close ile kapatın, ardından Set nesne = Nothing yazın. Hata durumunda da kapanmaları için hata yönetimi bloğunda temizleme adımı bulundurun. Açık kalan bağlantılar dosyayı kilitleyebilir.
  • Kayıt eklerken ve güncellerken: Excel dosyasına ADO ile INSERT ve UPDATE yapılabilir, ancak DELETE desteklenmez ve biçimlendirme korunmaz. Yoğun yazma işlemleri için Access ya da SQL Server gibi gerçek bir veritabanı kullanmak daha güvenlidir. Kullanıcıdan gelen metinleri SQL'e birleştirmek yerine parametreli Command kullanın.
  • Alan adlarında Türkçe karakter ve boşluk: Başlıklarda boşluk ya da özel karakter varsa alan adını köşeli parantez içine alın, örneğin [Satış Tutarı]. Sayfa adı da her zaman [Sayfa$] biçiminde yazılmalıdır.
  • İlgili konular:
    • Range.CopyFromRecordset: Recordset sonucunu sayfaya tek adımda yazar.
    • CreateObject: ADO nesnelerini geç bağlamayla oluşturur.
    • Veri > Veri Al (Data > Get Data): Kod yazmadan harici veriye bağlanmak için Power Query.
    • QueryTables: Harici veri sorgularını sayfaya bağlayan VBA nesnesi.
    • On Error: Bağlantı hatalarını yönetmek için hata işleme deyimi.
    • DÜŞEYARA: Küçük aramalar için formülle çözüm sunan işlev.
    • ETOPLA: Koşullu toplama için SQL SUM ve GROUP BY'a formüllü alternatif.
Örneklerdeki makroların tamamı indirilebilir Excel dosyasındadır (.xlsm). Dosyayı açtıktan sonra makroları etkinleştirin, ardından Alt + F11 ile Visual Basic Düzenleyicisi'nde kodları inceleyebilir ve Alt + F8 ile çalıştırabilirsiniz.
Yazar
ExcelDepo
İndirme sayısı
0
Gösterim
19
İlk yayın
Güncel

Puanlar

0.00 Oylama 0 İnceleme

Geçtiği konular Geçtiği kaynaklar Geçtiği dosyalar

Geri
Üst