Excel Tablosunda SQL sorgusu – VBA
Bu yazımızda adım adım herhangi bir Excel tablosundan SQL sorgusu yardımıyla nasıl veri çekebileceğimizi göreceğiz. Böylelikle uzun VBA kodlarıyla yapabileceğimiz sorgulama veya filtreleme işlemlerini daha hızlı ve etkili bir biçimde yapıyor olacağız. Oluşturduğumuz uygulamayı buradan indirebilir, kodlara da “Kaynak Kodu” sekmesinden ulaşabilirsiniz.
İlk olarak Excel sayfamıza sorgulamayı yapacağımız verileri ekliyoruz ve bu verileri bir tablo altında topluyoruz.
Verilerimizin bulunduğu alanı seçtik ve tablomuzu oluşturduk. Alan seçerken kolon bazında yapılmış olması oluşabilecek yeni veri ekleme durumlarında daha fazla etkinlik sağlayacaktır.
Tablomuzu oluşturduk. İstediğimiz biçimlemeyi tasarım menüsünden yapabilirsiniz.
Geliştirici menüsünden birer adet button ve textbox ekliyoruz.
Excel sheetimiz kodlarımızı eklemek için gerekli hali aldı.
Geliştirici sekmesinden Kod Görüntüle’yi tıklıyoruz (Kısayol olarak Alt+F11 ‘le ulaşabilirsiniz) ve kod sayfamız açıldı.
Gerekli referansları verebilmek için Araçlar>>Referanslar menüsüden ”Microsoft ActiveX Data Objects 2.8 Library” kutusunu işaretliyoruz.
Not: Verdiğimiz bu referans Excel 2003 (8.0) ve Excel 2007 (12.0) için geçerlidir. Excel 2010 (14.0) sürümü için, bu linkten” Microsoft Access Database Engine 2010 Redistributable” dosyasını indirip, kurmalısınız. 2010 kullanıcıları, aşağıda ki kodlarda bulunan connection string’i
strCon = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & strFile & “;Extended Properties=”"Excel 8.0;HDR=Yes;IMEX=1″”;”
strCon = “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & strFile & “;Extended Properties=”"Excel 14.0;HDR=Yes;IMEX=1″”;” şeklinde değiştirmelidir.
Aşağıda ki metodu kodlarımıza ekliyoruz. Bu metod yardımıyla sheet’imizde ki tabloları liste haline dönüştürmüş olduk.
Peki SQL sorgusunu yaparken hangi tablo ismini kullanacağız? Metodumuzu button kontrolümüze “Call SqlSorgusuListObjects” şeklinde ekliyoruz ve çalıştırıyoruz. Böylelikle tablomuzu liste haline getirmiş olduk. Aşağıda görüldüğü gibi tablo ismimizi öğrenebiliriz. Bu örnekte tablomuz Sql1Tablo1 ismiyle oluştu.
Son olarak aşağıda ki kodları buttonumuza ekliyoruz. Kodların detaylı açıklamalarını aşağıda bulabilirsiniz.
Artık sıra geldi sorgumuzu yapmaya. Örnekte textbox’a “SELECT * FROM Sql1Tablo1 WHERE Dogum_yeri=’Ankara’” SQL kodunu ekleyerek doğum yeri Ankara olan kayıtları getiriyoruz. Hepsi bu kadar…
Private Sub CommandButton1_Click()
Call SqlSorgusuListObjects ' Eklediğimiz metodumuzu çağırarak başlıyoruz
Dim cn As Object ' Bağlantı tanımlıyoruz
Dim rs As Object ' Recordset tanımlıyoruz
Dim strFile$, strCon$, strSQL$
strFile = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name ' Dosyamızın yolunu belirtiyoruz
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFile & ";Extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"
' Connection string tanımlıyoruz
Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open strCon
strSQL = TextBox1.Text ' Sql sorgumuzu textboxtan aldık
rs.Open strSQL, cn ' recordsetimizi verilerle doldurduk
Do While Not rs.EOF ' Döngüyle sheetimize sorgudan dönen değerleri yazdırıyoruz
r = r + 1
For c = 1 To rs.Fields.Count
If r = 1 Then
ActiveSheet.Cells(r + 2, c + 6) = rs.Fields(c - 1).Name
Else
ActiveSheet.Cells(r + 2, c + 6) = rs.Fields(c - 1).Value
End If
Next
If Not r = 1 Then rs.MoveNext
Loop
cn.Close
Set rs = Nothing
Set cn = Nothing
End Sub
Sub SqlSorgusuListObjects()
Const Prefix = "Sql"
Dim x, WSh, i&
With ThisWorkbook
For Each WSh In .Worksheets
i = i + 1
For Each x In WSh.ListObjects
.Names.Add Prefix & i & x.Name, x.Range, True
Next
Next
End With
End Sub