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,]

49 yorum

Bir Cevap Yazın

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