Merhaba Arkadaşlar,
Belirli bir formatta ki şablona MS Sql Server veritabanından bilgileri çekmek. Bu işlemleri yaparken de aşağıdaki konuları kullanmanız yeterli olacaktır. Bu konular kısaca şu şekilde
- Excel’de Macro ile Veritabanı bağlantısı kurma
- Excel’de Macro ile Excel sütunlarını okuma ve yazma
- Excel’de Macro ile karar yapıları (İf, For Each)
- Excel’de Macro ile siteden (Url ile ) fotoğraf yükleme
- Excel’de Macro ile Split işlemi
Excel’de macro geliştirmek için excel ortamını hazırlamamız gerekiyor. Benim kullandığım ofis Türkçe ve anlatımı buna göre yapacağım.
Geliştirici (Developer) Sekmesi
Excel’de macro geliştirmek için Geliştirici (Developer) sekmesine geçmemiz gerekiyor. Ancak bu sekme her zaman aktif olmayabiliyor. Eğer aktif değilse öncelikle Dosya / Seçenekler / Şeridi Özelleştir seçeneğinden Geliştirici sekmesini seçip tamam diyerek sekmeyi aktif hale getiriyoruz.
Excel Macro Oluşturma
Yeni aktif hale getirdiğimiz geliştirici sekmesinden Visual Basic ve Makrolar botunları bulunmaktadır. Makrolar butonuna basıyoruz. Açılan ekranda mevcut makrolar gelmektedir. Yeni makro oluşturma için Makro Adı alanına makro ismini girip Oluştur butonuna basıyoruz.
Makro geliştirmelerini Visual Basic programlama diliyle yapabiliyoruz.
Excel Makroda hücrelerin içindeki verileri okumak ve verileri değiştirmek için aşağıdaki kodları kullanıyoruz.
Makro Kayıt Etme
Makro kaydetmek yapılışta çok basit ama çok kritik ve önemli olan bir hususa dikkat etmezseniz yaptığınz tüm makrolar çöp olabilir. İki gün boyunca uğraştığım makrolar bu sebepten ötürü silinmiş oldu ve herşeyi yeniden yazmam gerekti.
Makroları kayıt ederken normal *.xlsx olarak kayıt etmemeniz gerekiyor. *.xlsxm uzantılı kaydetmeniz gerekiyor.
Ofis 2007 ve öncesinde bu sorun olmuyormuş ama sonraki versiyonlarda *.xlsxm formatında kayıt etmeyince makrolar siliniyor ve malesef kurtarılamıyor.
Verileri Okuma
Dim productCode As String
productCode = Range(“C9”).Value
Verileri Yazma
Range(“C9”).Value = “Yeni Değer”
Makro Güvenliği
Konumuz veritabanından veri çekmek olduğu için buraya yazacağımız komutları ya da içerisinde bulunan kullanıcı bilgilerinin kimsenin görmesini istemeyebiliriz. Bu gibi durumlar için Makro dosyasına şifre koyabiliriz. Makro dosyasına şifre koymak için Geliştirici / Developer sekmesinden Visual Basic butonuna basıyoruz. Açılan ekranda sol tarafda bulunan VBAProject altında ki ilgili modüle sağ tıklayıp VBAProject Properties seçeneğine tıklıyoruz. Açılan ekranda Protection sekmesine geçiyoruz ve burada bulunan Lock Project alanında ki Lock project for viewing seçeneğini seçili hale getiriyoruz ve Password/ConfirmPassword alanlarını doldurup OK butonuna basıyoruz.
Veritabanı bağlantısının kurulması
Dim oCon As ADODB.Connection
Dim oRS As ADODB.Recordset
Set oCon = New ADODB.Connection
oCon.ConnectionString = “Provider=SQLOLEDB;Server=sunucu;Database=dbName;User ID=userName;Password=password;”
oCon.Open
Burada da dikkat etmemiz gereken bir husus var!
User-defined type not defined hatası alıyorsunuz reference bölümünden Microsoft ActiveX Data Objects 2.7 Library kütüphanesini aktif hale getirmelisiniz.
Tools / References
Veritabanından Veri Çekme
Bizim ihtiyacımız ürüne ait detayların istediğimiz hücrelere yazdırılmasıydı. Bu yüzden sorgumuzda geriye tek kayıt dönen bir sorgulama yapacağım. Yani veritabanından tek kayıt geldiğinde hangi senaryoları izleyeceğiz bunu anlatacağım.
Set oRS = New ADODB.Recordset
oRS.ActiveConnection = oCon
oRS.Source = “SELECT * FROM report_product_detail WHERE Code= ‘” + productCode + “‘;”
oRS.Open
Şuan veritabanından veriği çekmiş olduk ve oRS nesnesi ile gelen fileds(alanlar) değerlerini okuyup istediğimiz değerlere yazdıracağız.
Veri okumadan önce sorguya uygun bir kayıt var mı yok mu kontrolünü yapmak için “If Not oRS.EOF Then” kodunu kullanın, Eğer kayıt varsa anlamına gelmektedir.
Makro yazarken kullandığım kodlar ve anlamları aşağıdaki gibidir;
RecordSet üzerinde ki veriyi okuma
oRS.Fields(“KolonAdi”).Value
Veri içerisindeki bazı alanları değiştirme, REPLACE komutu
Replace(oRS.Fields(“IlgiliKolon”).Value, “DegisecekDeger”, “YeniDeger”)
Split komutu ile verileri diziye aktarma
Dim dizi() As String
dizi = Split(oRS.Fields(“KolonAdi”).Value, “,”)
Kelimeleri “,” değerine göre bölerek bir diziye aktarır. Örn: Marka: HP, Samsung, DELL
Dizide ki elemanları okuma
For Each eleman In dizi
MsgBox eleman
Next can
Excel Macro ile Fotoğraf yükleme
Set image_column = Worksheets(1).UsedRange.Columns(“E”).Cells(17)
With image_column.Worksheet.Pictures.Insert(photo_url)
.Width = 140
.Height = 140
.Left = 545
.Top = image_column.Top
End With
End If
Genel olarak kullandığım makro kodları bu şekildedir. Merak ettiğiniz ya da ihtiyaç duyduğunuz konular varsa yorum bölümünden benimle irtibata geçebilirsiniz.
