Web İçin Excel’de Aranabilir Veri Doğrulama Listesi

[vc_row][vc_column][vc_column_text]

ARANABİLİR VERİ DOĞRULAMA LİSTESİ

Geçtiğimiz yıl Aralık ayında Excel’e web tarafında kullanıma sunulan bir özelliğinden bahsetmek istiyorum. Umuyorum en kısa sürede masaüstü sürüme de bu özellik gelir ve dinamik işlerde hız kazanmış oluruz, aynı zamanda biraz da olsa Excel’deki açılır listeleri web sitelerindeki arama kutucukları veya seçilebilir dropdown’lar benzeri kullanabiliriz. Bu da webdeki deneyimi masaüstüne taşımak gibi olacağından göze hoş gelen şirin bir özellik olmuş olacak.

 

VERİLERİ ZAHMETSİZCE BENZERSİZLEŞTİRİN

Bu özellikle beraber, veri doğrulama listenize alacağınız veriler aynı zamanda seçtiğinizde alanda tekrarlı veriler olsa dahi, onları benzersizleştirip listenizi oluşturmuş olursunuz. Ekstra herhangi bir dinamik dizi formülü (BENZERSİZ / UNIQUE) kullanmanız da gerekmez.

 

Örnek bir tablo ile özelliği paylaşmaya çalışayım.

 

Diyelim ki; aşağıdaki gibi bir satış tablonuz var ve J1 hücresinde açılır liste olsun, içerisinde de Ürün Ad alanındaki verilerin benzersizlerinde oluşan bir liste yer alsın istiyoruz. İşte bu yeni gelen özellik, hem Ürün Adlarının benzersizlerinden oluşan bir liste elde etmemizi, hem de o liste içerisinde arama yaparak istediğimiz bir verinin seçilmesine olanak veriyor.

 

Örnek uygulama

[/vc_column_text][/vc_column][/vc_row][vc_row heading_title=”” src=”https://peakup.org/wp-content/uploads/2023/12/office_imza_son.gif” image_size=”full” image_width=”800″ image_height=”350″ svg=”false” lightbox=”false” custom_lightbox=”” group=”” frame_style=”simple” link=”https://peakup.org/global/office-training/” target=”_blank” title=”” desc=”” caption_location=”inside-image” hover_image_overlay=”true” align=”left” margin_bottom=”10″ drop_shadow=”false” drop_shadow_angle=”45″ drop_shadow_distance=”8″ drop_shadow_blur=”20″ drop_shadow_color=”rgba(0,0,0,0.5)” pxs=”false” pxs_x=”0″ pxs_y=”-100″ pxs_z=”0″ pxs_smoothness=”30″ drop_shadow_placeholder=””][vc_column][mk_image src=”https://peakup.org/wp-content/uploads/2023/12/office_imza_son.gif” image_size=”full” link=”https://peakup.org/global/office-training/” target=”_blank”][/vc_column][/vc_row]

Excel ile Web Sitelerinden Veri Çekin

[vc_row][vc_column][vc_column_text css=”.vc_custom_1573717048716{margin-bottom: 0px !important;}”]

Excel ile dilediğiniz web sitesinden veri çekebileceğinizi biliyor musunuz?

 

Excel‘in Veri menüsünden Web Sitelerine bağlanıp oradan veri alma özelliği var, fakat bu özellik maalesef tam olarak bize istediğimiz verileri sağlayamıyor. O yüzden istediğimiz veriyi farklı bir yolla elde etmek durumundayız. Nasıl mı? Tabii ki VBA‘nın yani makroların gücünü kullanarak.

Makro bilmiyor olabilirsiniz, bunu dert etmeyin. Yazacağımız kodların birçok satırı klişedir, olabildiğince basit bir anlatımla bu işin ne kadar kolay olduğunu anlatacağım.

Normal şartlarda, webten veri alma işlerinizi yapabilmeniz için, VBA (Makro) bilginiz olmalı, web sitelerinin HTML kaynaklarından veri alacağımız için temel de olsa HTML yapıya, temel Css bilgisine sahip olmanız gerekmektedir. Fakat hiçbir bilginiz olmasa da, klişe olan noktaları zaten belirteceğim, sonrasında nereden nasıl veri aldığımızı da Document Object Model (DOM) üzerinde göstereceğim.

Bu sizi teşvik etmek adına basit bir başlangıç olsun, geliştirme tarafı size kalsın. Bu yazıda şunu yapacağız; kendi sitemizdeki blog sayfamızda www.peakup.org/blog  yayınlanan postların başlıklarını ve tarihlerini çekelim, başlıklara da o postun linkini köprü olarak ekleyelim ki; tıkladığımızda o linke gidebilelim. Bu resimde nereleri alacağımızı belirtiyorum.

 

Hadi artık başlayalım.

 

Öncelikle Excel‘de herhangi bir sayfadayken ALT + F11 tuşlarına basarak VBE (Visual Basic Editör) penceresine gidelim. Pencerenin üst menülerinden Insert ve Module tıklayıp boş bir modül açalım, kodlarımızı bu boş pencereye yazacağız. Adım adım her bir kod satırının ne anlama geldiğini de size aktarıyor olacağım. Bu arada yazının sonunda kodların son hâlini de bulabilirsiniz.

İlk satırımızda makromuza bir isim veriyoruz. Makromuzun adı PEAKUP_BLOG olsun.
Sub PEAKUP_BLOG()
hemen altına kodlarda kullanacağımız değişkenlerimizi tanımlıyoruz.
Dim ie As Object, meta As Object, i As Integer, basla As Single
Kodların sonunda sayfaya ekleyeceğimiz butona her batığımızda makromuzu çalıştıracağız, yani postları yeniden çektireceğiz, yeni post varsa o da gelecek. O yüzden eski verileri temizlemek için bu satırı kullanıyoruz. A2:B100 hücrelerinde ne varsa temizle diyoruz.
Range("A2:B100").Clear
sonrasında görev yöneticisinde açık olan tüm Internet Explorer pencerelerini kapatıyoruz, açık kalmaları hem uygulamanın şişmesine hem de bazı hatalar almamıza sebep olacağı için bu işlemi yapıyoruz.
Shell "taskkill /f /im iexplore.exe"
sonrasında tüm ie pencerelerinin kapandığından emin olmak için 1 sn. bekletme kodlarını ekliyoruz.
basla = Timer: While (Timer - basla) < 1: Wend
Şimdi Internet Explorer‘ı oluşturuyoruz.
Set ie = CreateObject("InternetExplorer.Application")
oluşturulan uygulamanın ekranda görünmemesi ama arka plânda yine çalışmaya devam etmesi için bu satırı ilave ediyoruz.
ie.Visible = False
sonrasında blog sayfamıza yönlendiriyoruz.
ie.navigate "https://peakup.org/blog/"
biliyorsunuz bir web adresine gittiğinizde o sayfanın tam olarak yüklenmesi biraz zaman alıyor. Bu zamanı aşağıdaki kodlarla kontrol ettiriyoruz ve sayfa tamamen yüklenene kadar dön/bekle diyoruz.
While ie.Busy Or ie.ReadyState < 4: DoEvents: Wend
şu ana kadar, şunları yaptık;

  • Makromuza isim verdik
  • Değişkenlerimizi tanımladık
  • Açık olan tüm IE tarayıcılarını kapattık ve ilave 1 sn. de bekle dedik
  • IE’yi oluşturduk ve gizledik
  • Blog sayfamıza yönlendirdik
  • Sayfanın meşguliyeti bitene ve tamamen yüklenene kadar bekle dedik

artık sayfa tam orak önümüzde olduğuna göre, HTML yapısından faydalanarak istediğimiz verilerin nerede olduğunu tespit edip, bütün postların başlıklarını, başlıkların linklerini ve tarihlerini hızlıca aldırabiliriz.

Blog sayfamız sürekli güncel ve aktif olduğundan dolayı birden fazla postumuz mevcut. Bütün postlara tek tek bakmamız ve başlıklarını almamız gerekiyor. Bunu VBA‘nın olmazsa olmazı Döngüler’i kullanarak yapacağız.  For veya For Each döngüsünden birini burada kullanabiliriz.

Almak istediğimiz başlığa ait, id, name, class, tag gibi özellikleri kullanarak tespit etmemiz gerekiyor. Nasıl yapacağımızı gif resimde görüyorsunuz. Alacağımız yer neresi ise, üzerine gelip sağ click yaparak İncele diyoruz.

Baktığınızda bütün verilerin  <div class=”mk-blog-meta”> altında tutulduğunu göreceksiniz.  HTML yapısı ağaç yapısı şeklindedir.

For döngüsü ile bütün bu mk-blog-meta class ismine sahip olan postları kontrol ettirelim;
Genellikle alacağımız ilk elemanın değeri sıfır olur ve aşağıdaki gibi sıfırdan başlayıp kaç tane ise -1 sayısı kadar dön/bak deriz.
For i = 0 To ie.document.getelementsbyclassname("mk-blog-meta").Length - 1
Biz bu noktada hem başlığı hem de tarihi almak istediğimiz için aynı satırları tekrar tekrar yazmak yerine bir kere set ediyoruz adına da meta diyoruz. Artık meta yazsını gördüğümüzde her bir post aklımıza gelsin. Aşağıdaki satırda parantez içindeki ( i ) ilk posttan  başlayıp kaç tane post varsa hepsinde dönmek için 0-1-2-3-4 … şeklinde ilerlememizi sağlayacak.
Set meta = ie.document.getelementsbyclassname("mk-blog-meta")(i)
Şimdi başlıklarımızı alabiliriz. Bu satırda diyoruz ki, her bir postun altındaki class adı the-title olan metni  A2 hücresinden itibaren alt alta yaz.
Range("A" & i + 2) = meta.getelementsbyclassname("the-title")(0).innertext
Başlıkları aldığı andan itibaren de hemen post linkine köprü yapalım. Bunun için de bu kodu kullanacağız..
Kodda der ki; A2‘ye köprü kur, adres olarak olarak postun altındaki a tagında bulunan linki kullan ve görünen yazı olarak da yine A2’deki metni yaz.
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i + 2), _
Address:=meta.getelementsbytagname("a")(0).href, _
TextToDisplay:=Range("A" & i + 2).Value

Evet başlığımızı alıp köprümüzü kurduk. Şimdi tarih kısmını da B sütununa alalım. Aşağıdaki satırda, her bir postun altındaki mk-blog-meta-wrapper class’ındaki metni alıyoruz.
Cells(i + 2, "B") = meta.getelementsbyclassname("mk-blog-meta-wrapper")(0).innertext
Next i
satırı ile For döngüsünden çıkıp sonlandırıyoruz.
ie.Quit
satırı ile IE tarayıcıyı kapatıyoruz.
MsgBox "Islem Tamamlandi.", vbInformation, "PEAKUP BLOG"
İşimiz bitince İşlem Tamamlandı mesajı verdiriyoruz.
End Sub
satırı ile de makromuzu sonlandırıyoruz.

 

Her bir kod satırının ne anlama geldiğini yazdıktan sonra kodlarımızı bir toparlayıp görelim.


Sub PEAKUP_BLOG()
Dim ie As Object, meta As Object, i As Integer, basla As Single
Range("A2:B100").Clear
Shell "taskkill /f /im iexplore.exe"
basla = Timer: While (Timer - basla) < 1: Wend
Set ie = CreateObject("InternetExplorer.Application")
ie.Visible = False
ie.navigate "https://peakup.org/blog/"
While ie.Busy Or ie.ReadyState < 4: DoEvents: Wend
For i = 0 To ie.document.getelementsbyclassname("mk-blog-meta").Length - 1
Set meta = ie.document.getelementsbyclassname("mk-blog-meta")(i)
Range("A" & i + 2) = meta.getelementsbyclassname("the-title")(0).innertext
ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i + 2), _
Address:=meta.getelementsbytagname("a")(0).href, _
TextToDisplay:=Range("A" & i + 2).Value
Cells(i + 2, "B") = meta.getelementsbyclassname("mk-blog-meta-wrapper")(0).innertext
Next i
ie.Quit
MsgBox "Islem Tamamlandi.", vbInformation, "PEAKUP BLOG"
End Sub

Artık oluşturduğumuz kodları çalıştırmak kaldı. Onun için Ekle menüsündeki Şekillerden butona benzer bir şekil ekleyin ve sağ tıklayıp Makro Ata deyin, açılan pencereden PEAKUP_BLOG makrosunu seçip Tamama basın.

Oluşturduğumuz bu butona her bastığınızda eski verileri temizleyip yeni post varsa onlarla beraber yeniden çekecektir.

Sitemizdeki postları takipte kalmanız umuduyla, kodların da içinde olduğu Excel dosyasını resme tıklayarak indirebilirsiniz.

Daha fazla bilgi için Excel ile Webten Veri Alma Eğitim talebinde bulunabilirsiniz.
Eğitim sonrasında her türlü, haber, finans, spor, e-ticaret vb. sitelerden veri çekebilir hâle gelebilirsiniz.

Faydalı olması dileğiyle.. [/vc_column_text][/vc_column][/vc_row]