Geliştirici

Excel Makro VBA ile Web ‘den Veri Getirme


Microsoft Excel ‘in kıymetli yeteneklerinden biri olan Excel Dış Veri Alma seçeneklerine değinmek istiyorum. Geçenlerde Excel Web sayfasından veri alma hakkında başlangıç konusu olarak paylaşmış olduğum Excel Döviz Kuru Aktarma | TCMB başlıklı makalede sorgu yaparak Excel sayfasına nasıl döviz getirebileceğimizi incelemiştik. Bugün makalemizde ise Makro VBA ile Excel Web sorgusu yaparak değişken bir web sitesinden istediğimiz başlıkları, verileri nasıl getirebiliriz bunu inceleyeceğim.


 

  • Web sitesinden arama yaparak istediğimiz sonuçları Makro VBA ile Excel ‘e aktarma nasıl yapılır ?

işlemlere başlamadan önce işin özetini açıklayalım sonra da örnek konuya geçelim. Yapmak istediğimiz işlem, herhangi bir web sitesi üzerinden arama yaparak gelen sonuçlardan yalnızca istediğimiz başlıkları, değerleri, bilgileri  vb. ne istersek bunları Excel ‘e aktarmaktır. Bu işlemleri yapabilmek adına birazcık HTML bilgisine ihtiyacımız olacak. Bunları da konu içerisinde örneklerle anlatacağım, hep birlikte inceleyelim. 

Excel Makro ile Web 'den Sorgulama
Sorgu yapacağım site: www.hepsiburada.com istediğim sonuçlar : Arama yapılan terimler hakkında Excel sayfasına konu başlıklarını getirmek. Aranacak terim : Excel , Makro , VBA , iphone , samsung , Nike , Adidas vs. değişken bir web sitsinde herşey olabilir…

 

Başlayalım; aşağıdaki örnek tabloda bazı hücrelere Ad Tanımlama yapıyorum. Bunun hakkında daha önceden hazırlamış olduğum Excel Formüllerde Ad Tanımlama ve Kullanma hakkında eğitim makalemi inceleyebilirsiniz. Bunun hakkında yeterli bilgiye sahip olan kullanıcılara yönelik Ad Tanımlama aşamasını aşağıdaki gibi hazırlıyorum.

Hazırladığım bu Ad Tanımlama isimlerini Visual Basic Editör ‘de kullanacağız ve bize daha anlaşılır dilden makro kodları oluşturmamızı sağlayacak. Hücre tanımlamalarımızı yaptıktan sonra Alt + F11 kısayoluyla VBA penceresine geçiş yapıyoruz, Sayfa1 isimli dosyaya çift tıklıyoruz ve kod penceresi hazır durumdadır. Gelen pencereden aşağıdaki örnek resimde kırmızı renkle işaretlediğim alanlardan Worksheet + SelectionChange seçiyorum böylece private Sub adı altında kodlar oluşuyor.

Yukarıdaki örnekte Private Sub komutuyla başlayan kodlar ne işe yarar gibisinden düşünenleri görmekteyim, bunun anlamı makroyu bitirdiğimizde Excel sayfasında belirlenen hücre aralığında değişiklik yapıldığı zaman makronun çalışacağını anlatır. iki satırın arasına makro kodları ekleyerek kullanmaya başlayacağız. ilk ekleyeceğimiz kod yapısı If Then End If olacak.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Row = Range("Kelime").Row And _
 Target.Column = Range("Kelime").Column Then

End If

End Sub

Daha sonra Web ‘den sorgu yapabilmemiz için Excel içerisinde bulunan internet explorer eklentisini çalıştıracağız. Bu sayede istediğimiz Excel içerisinden Web sitesine bağlanıp veri çekme işlemini yapabiliriz. internet explorer ayarlarını yapmak için Set komutunu kullanacağım. Aşağıdaki kodlarda IE.Visible = False yerine IE.Visible = True yazabilirsiniz. Bunun anlamı Web sorgusu yaparken , Excel ‘de internet explorer penceresinin görünüp görünmeyeceğine karar verir.

Set IE = CreateObject("InternetExplorer.Application")
IE.Visible = False
IE.navigate "Buraya sorgu adresi yazılır"

 

  • Excel Makro VBA & HTML bağlantısı nasıl yapılır ?

Şimdi HTML kısmında önemli bir detaya geldik. IE.navigate “Sorgu adresi” kısmına ne yazılacağını, Web adresini kontrol ederek belirleyeceğiz. Önce siteye bağlanıyoruz ardından arama yapıyoruz. Anlaşılır olması açısından sizlere gif sunum hazırladım.

Görüldüğü gibi “iphone” kelime araması yaptım. Yukarıdaki adres çubuğunda arama terimi yaptıktan sonra oluşan link yapısını fark ediyorum ve buradan “iphone” kelimesi hariç arama linkini kopyalıyorum. Bu adresi makro kodu içerisinde IE.navigate “Sorgu” bölümüne ekliyorum. Kodların son hali aşağıdaki gibi olacak.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = Range("Kelime").Row And _
 Target.Column = Range("Kelime").Column Then

Set IE = CreateObject("InternetExplorer.Application")
 IE.Visible = False
 IE.navigate "http://www.hepsiburada.com/ara?q=" & Range("Kelime").Value

End If

End Sub 

Dikkat ettiyseniz yukarıdaki kodlarda sorgu adresinin sonunda Range eklemesi yaptım. Bunun anlamı site arama linkini, Ad Tanımlama yaptığımız kelime hücresiyle birleştir demektir.

Gelelim “iphone” kelimesini site üzerinde arama yaptıktan sonra, hangi verileri alacağımıza ilişkin meseleye yani HTML bilgisine ihtiyacımız olan bölüme… Web sorgu yaptıktan sonra çıkan sonuçlardan birine sağ tıklayıp incele diyoruz. Karşımıza HTML sayfa yapısı gelir.

 

 

 

 

Sonrasında açılan pencereden ürün başlığını içeren tag etiketinin h3 olduğunu görebilirsiniz. Yani ne demek oluyor bu h3 tagı derseniz, ürünün Web sitesinde yayınladığı başlık demektir. Buraya ne yazılırsa sitede o görünür demektir.

(bkz. aşağıdaki örnek resim)

H3 tagını kullanarak Excel sayfamızda ürün başlığını çekebilirim. Bunu yapmak için ” getElementBy ” komutunu kullanacağım. Bu komutu daha sonra detaylı incelemeyi düşünüyorum fakat konumuz içerisinde direk kullanacağım o yüzden örneğini aşağıda veriyorum.

  • Makro VBA ile Web ‘den sorgulama yaparak ürün başlıkları nasıl getirilir ?

Dim doc As HTMLDocument
 Set doc = IE.document

Range("Sonuc1").Value = doc.getElementsByTagName("h3")(0).innerText

yukarıdaki oluşumda getElementsByTagName komutunu kullanıyorum, çünkü h3 tag etiketlerini HTML üzerinden Excel sayfasına çekeceğim. Özetle anlatmak gerekirse, HTML içerisindeki h3 taglarından (0) olarak belirttiğimiz ilk değeri, Ad Tanımlama yaptığımız Sonuc1 hücresine getirir. Web ‘den makro ile veri getirme işlemi bu mantıkla hazırlanabilir. Benzer şekilde ilave örnek yapalım, mesela bu ürünün satış fiyatını da aynı şekilde Web ‘den sorgu yaparak makro ile veriyi getirme imkanımız bulunuyor. Bunun için aynı HTML yapısında ürün fiyatının nerede yazdığını bulmanız yeterli olacaktır. Örnekte fiyat bilgisinin “price product-price” span class satırında yer aldığını görebilirsiniz. O halde getElementsByClassname komutunu kullanacağız. Makro komutu ile Web ‘den sorgu yaparak fiyat bilgisini getirmeniz için şu satırı yazabilirsiniz.

Range(“Fiyat1”).Value = doc.getElementsByClassName(“price product-price”)(0).innerText

Anlamı “price product-price” satırındaki ilk fiyat bilgisini Fiyat1 tanımlı hücresine getirir.

Şimdi hazırladığımız makro kodlarını toparlayalım ve son hali nasıl oldu göz atalım.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Row = Range("Kelime").Row And _
 Target.Column = Range("Kelime").Column Then

Set IE = CreateObject("InternetExplorer.Application")
 IE.Visible = False
 IE.navigate "http://www.hepsiburada.com/ara?q=" & Range("Kelime").Value

Do
 DoEvents
 Loop Until IE.readyState = READYSTATE_COMPLETE
 Dim doc As HTMLDocument
 Set doc = IE.document
 Dim sDD As String

Sonuc = Trim(doc.getElementsByTagName("h3")(0).innerText)
 MsgBox Sonuc

Range("Sonuc1").Value = doc.getElementsByTagName("h3")(0).innerText
 Range("Fiyat1").Value = doc.getElementsByClassName("price product-price")(0).innerText

Columns.AutoFit

End If
 End Sub
Parantez içindeki (0) değeri HTML kodlarında çıkan ilk “h3” tagını getirir. Parantez içindeki sayıyı arttırabilirsiniz. Örneğin (1) yazarsanız HTML yapısındaki 2.sonuç gelir.

Örnek makro kodlarında Range(“Urun1”) yerine B2 ya da istediğiniz hücre adresini yazabilirsiniz.

Birde ekleme ilave yaparak gelen sonucu bildirim uyarısı olarak alabilirsiniz.Bunun için kodlar içerisinde Sonuc MsgBox satırlarını inceleyebilirsiniz.

Columns.AutoFit , makro ile web ‘den sorgulama yaptıktan sonra en son sütunların uygun hizaya getirilmesini sağlar.

Makro VBA ile Web ‘den nasıl sorgulama yapılır , Makro VBA ile Web ‘den Excel ‘e nasıl veri aktarımı sağlanır konularını aşama aşama makalemiz içerisinde incelemiş olduk. Yukarıda yapılan çalışmalar hakkında yorum kısmından örnek dosya talebi yapabilirsiniz. Alternatif seçeneklerle çoğaltılabilir.

 

 

 

 

 

 


Önemli Not: Yukarıdaki makronun çalışması için, Visual Basic Editor penceresinden Tools > References  VBA Project sekmesine tıklayın ve açılan küçük pencereden, aşağıdaki sekmeleri aktif etmeniz gerekmektedir.

  • Microsoft internet controls ,
  • Microsoft HTML object Library , 

 

Faydalı olması dileklerimle…

[ Excel , Makro , VBA , Web Sorgulama , Excel ‘e Aktar , HTML , getElementsByTagName , getElementsByClassName , getElementByID , get ElementsByName , Web Veri Alma , Veri Getirme , Veri Çekme , webten al, webten getir, webten veri çek,]

This Post Has 53 Comments

  1. anıl k. Reply

    hocam merhabalar,
    öncelikle elinize sağlık. mümkünse örnek dosyayı ve elinizde bu konuyla ilgili başka dosyalar varsa mail gönderme şansınız var mıdır
    bymarduk@hotmail.de

  2. Çizgi Reply

    Merhaba Anıl Bey,

    ilginize teşekkür ederim, bu ve benzeri çalışmaların devamı gelecektir, toplu dosya paylaşımı henüz yok bu konuda ileriye yönelik bir düşüncem olabilir, isterseniz makro kategorisindeki konulara göz atın her konuda örnek dosya mevcuttur. Bununla alakalı olan örnek dosyayı gönderiyorum.

    Faydalı olması dileğiyle…

    • anıl k. Reply

      merhaba gönderebildiniz mi? mail gelmemiş hala da. kodları çalıştırırken hatalar alıyorum o yüzden lazım

  3. Anıl k. Reply

    merhabalar tekrardan
    peki burada arama yaptıktan sonra çıkan sonuçların linklerini de D sütununa sıralayabilir miyiz
    yani “iphone” diye arattıktan sonra çıkan her bir sonucun hepsiburada linkini de yanına yazsın. nasıl mümkün olur acaba?

  4. Taha Reply

    Ellerinize sağlık çok güzel olmuş benimde ihtiyacım var bu çalışmaya yollamanız mümkün mü acaba.Şimdiden teşekkürler

  5. Mustafa Reply

    syntax error hatası alıyorum sebebi ne olabilir?

  6. Mustafa Sülerya Reply

    Merhaba örnek dosyayı gönderebilir misiniz acaba?

    mustafasulerya@gmail.com

    Teşekkür ederim şimdiden. Elinize emeğinize sağlık.

  7. Çizgi Reply

    Merhaba arkadaşlar, konu içerisinde Ad Tanımlama yöntemi kullanılmıştır.
    Bir hücreye ad tanımlaması yaparak “Kelime” şeklinde tanımlama yapılması gerekir.
    İkinci konu ise makalenin sonunda belirttiğim Önemli not kısmında yazan detayı uygulamalısınız. Bu adımları uyguladınızda herhangi bir hata ile karşılaşmazsınız.
    Saygılarımla

  8. Mustafa Sülerya Reply

    Bunu seçmeli liste halinde değil de Örneğin; A2 – A3 – A4 – A5 hücrelerine 4 tane aranacak kelimeyi yazsak ve karşısındaki B2-B3-B4-B5 – C2-C3-C4-C5 vb. sütünlarda Ürün adı ve Fiyatlarını verecek şekilde yapmaya kalsak nasıl bir kod yapısı oluşturmamız gerekir ?

  9. Erkan ÖZTÜRK Reply

    Bende örnek dosya rica edebilirmiyim. Emeğinize sağlık Başarılar

  10. Çizgi Reply

    Talepte bulunan kullancılarımıza dosyaları gönderdim. Sorularınız olursa çekinmeden yazabilirsiniz.

    Saygılarımla

  11. mustafa Reply

    hocam teşekkürler
    bende istesem örnek dosya gönderirmisiniz
    birde aşşağıdaki kod kırmızı gözüküyor, neden
    IE.navigate “http://www.hepsiburada.com/ara?q=” & Range(“Kelime”).Value

  12. mehmetali Reply

    Selamunaleyküm iyi çalışmalar mümkünse çalışma dosyasını banada gönderebilir misiniz hata aldım alone_prince28@hotmail.com

  13. M.Cihat Reply

    Hocam selamlar,
    Çok güzel bir çalışma, merakla sayfanızı takip ediyorum. Rica etsem örnek dosyayı mcapower@gmail.com adresine gönderebilir misiniz?

  14. Levent Önal Reply

    Çok güzel bir çalışma olmuş, elinize sağlık. VBA ile gittigidiyor, n11 ve hepsiburada.com sitelerinden tek butona dokunarak siparişleri excel’e aktarmak istiyorum. Web’den veri çekme yöntemi yani bu makaledeki yöntemle bu işlem yapılabilir mi? Teşekkürler.

  15. ALİ RIZA ŞAHİN Reply

    Merhaba kolay gelsin hocam; güzel bir çalışma olmuş. Bende buna benzer bir şey yapmak istiyorum. Rica etsem örnek dosyayı bana da atar mısınız ? Teşekkürler şimdiden.
    ali_riza_sahin@hotmail.com

  16. Pingback: Excelde Makro ile Döviz Kurlarını Getirmek | | Çizgi Akademi - İleri Düzey Excel Eğitimleri

  17. Sadin Reply

    İnternette araştırıyordum kaç zamandır sonunda aradığım konuyu buldum, kimse böyle detaylı anlatmıyor hocam elinize yüreğine sağlık excelde favori bloğum burası artık :))

  18. Halil34 Reply

    hocam emeğinize sağlık anlattığınız gibi yaptım fakat dosyayı çalıştıramadım, site uzantısını yazdığımız satır hata verdi.

    Syntax eror

  19. Ufuk Yılmaz Reply

    hocam merhabalar,
    öncelikle elinize sağlık. mümkünse örnek dosyayı ve elinizde bu konuyla ilgili başka dosyalar varsa mail gönderme şansınız var mıdır

  20. Oğuz Reply

    Aynı şeyi gazete web sayfalarından sorgulatıp buldurduğumuz haberler içinde yapmak mümkün mü?

  21. ÖZHAN ÇOLAK Reply

    rica etsem çalışma dosyasını gönderebilirmisiniz “SYNTAX ERROR” hatası aldım.
    ozhancolak308732@hotmail.com

  22. erdinc Reply

    selam
    güzel örnek fakat çalıştıramadım bence. örnek dosyayı upload ederseniz herkes yararlanır.
    eğer mail atarsanız netkit q yahoo.com

  23. kkaraca Reply

    güzel çalışma, ama bende de aynı hatayı verdi , karacakamil@gmail.com adresine de gönderir misiniz?

  24. Kubilay Reply

    Merhaba
    Örnek dosyayı bende alabilir miyim?
    bilgisayarsistemi@gmail.com

  25. fatih Reply

    Örnek dosyayı ben de istiyorum. Rica etsem çalışma dosyasını gönderebilirmisiniz “SYNTAX ERROR” hatası aldım.

  26. Murat t Reply

    Örnek Dosyayı mrttzn37@gmail.com adresime iletebilir misiniz?

  27. Murat t Reply

    Örnek Dosyayı mrttzn37@gmail.com adresime iletebilir misiniz?

  28. Onur Reply

    Merhaba
    Eklentiler içerisinde internet explorer eklentisi diye birşey yok hocam. O kısıma kadar geldim yardımınızı rica ederim.

  29. Onur Reply

    Birde bu kodları yazdıktan sonra kaydediyor muyuz? yoksa direk pencereyi kapatıyor muyuz?
    2- internet explorer eklentisi nedir, nerededir acaba? işlemin sonunda son gifinizdeki gibi “iphone” kelimesi tabloya gelmedi. Önce sordugum şeylerde bir hata yapmış olabilirim yanıtlar mısınız lütfen 🙂

  30. Selman Reply

    Merhaba ben de syntax error hatası aldım, makalenin sonunda önemli not kısmını uygulamama rağmen. Bana da atar mısınız karşılaştırmak için? teşekkürler.

    m.selman.ay@gmail.com

  31. Mehmet Reply

    Merhaba
    Örnek dosyayı alabilir miyim? mehmetyasti23@gmail.com
    Şimdiden teşekkürler

  32. Selman Ay Reply

    Merhaba, çok rica etsem bana da atar mısınız herşeyi denedim error veriyor. Çok memmun olurum atarsanız şimdiden teşekkürler.

    m.selman.ay@gmail.com

  33. Süleyman Reply

    Merhaba,
    Örnek dosyayı bende alabilir miyim?
    suleymanbilge91@gmail.com

  34. Serdar Üretme Reply

    Merhaba örnek dosya bende alabilir miyim?

    İlgi ve desteğiniz için şimdiden teşekkürler.

  35. Asena Berker Reply

    Ben de örnek dosyayı alabilir miyim?

  36. Asena Berker Reply

    Daha evvel almış arkadaşlar da belki dosyayı bana da iletirler. Yardımlarınızı rica ediyorum.
    asenaberker@gmail.com

  37. serkan uçar Reply

    Örnek Dosyayı gönderebilir misiniz ?
    serucars@gmail.com

  38. Muharrem Reply

    Hocam örnek taslak için teşekkürler. Örnekte veri doğrulama olarak ürün isimlerinin fiyatlarını bir hücre içinden aldırtmışsınız. Biz bu örnekte hepsiburada.com sitesinde satılan tüm ürünlerin isimlerini siteden çektirebilir miyiz? Teşekkürler.

    • Çizgi Reply

      Merhaba
      Bunun için panel girişi, yeni kodlamalar gerekli

      • Muharrem Reply

        HTML’nin içinde bütün ürün isimlerini içeren bir tablo yok mudur? Ben aradım bulamadım. Tablo olsaydı direkt alabilirdik. Gözden kaçırmış olabilir miyim isim tablosunu hocam? ne der ne tavsiye edersiniz?

        • Çizgi Reply

          Açılan sayfaya göre liste değişir, Örnek veriyorum Ayakkabı kategorisine tıklandığında 1.Sayfa sonuçlarını içeren HTML kodlarını getirir.

Bir Cevap Yazın

E-posta hesabınız yayımlanmayacak. Gerekli alanlar * ile işaretlenmişlerdir