VBA ile XML Dosya Arasında Veri Alışverişi

 In Microsoft Office, Office365

Excel & VBA ile XML dosya arasında veri alışverişi yapmak oldukça kolaydır.  XML dosyaları, metin belgesi (Txt) ve CSV (virgülle ayrılmış değerler) dosyaları dışında en yaygın veri dosyalarından biridir. Hiyerarşik olmayan (XML dosyaları veya JSON) veri dosyalarını okumak nispeten kolaydır. Veri satırında satır ve okuma sütunlarını ayrı ayrı okuyabilirsiniz. XML (ve JSON) ile veri denetlemek pek kolay değildir (şemadaki kayıtlar arasında parent-child (ebeveyn-çocuk) ilişkileri vardır) ve altta yatan düğümlerin sayısı, genellikle sabit sayıda sütun içeren tablo verilerine karşılık olarak değişebilir. Belirli bir sınırlayıcı ile ayrılmıştır.

Neyse ki, VBA‘da MSXML2.DOMDocument nesnesini kullanarak Excel & VBA ile XML dosya arasında veri alışverişi ‘ni rahatlıkla yapabiliriz.
Ancak, her zaman olduğu gibi, XML‘de bir yapının örneklerine dalmadan önce nasıl bir dosya oluşturduğuna dair kısa bir giriş ile başlayalım.

Microsoft Excel, başka veritabanlarında ve uygulamalarda oluşturulmuş Genişletilebilir Biçimlendirme Dili (XML) verilerini içeri aktarmayı, XML şemasından gelen XML öğelerini çalışma sayfası hücrelerine eşlemeyi ve başka veritabanları ve uygulamalarla etkileşim kurmak için düzeltilmiş XML verilerini dışarı aktarmayı kolaylaştırır. Bu XML özelliklerinin, Office Excel’i bildik bir kullanıcı arabirimiyle çalışan bir XML veri dosyaları oluşturucusuna dönüştürdüğü düşünülebilir.

Haydi gelin şu VBA & XML işe bir bakalım.

 

XML Belgesini VBA’ya Yüklemek

MSXML2.DomDocument nesnesi, XML yapısına kolayca ulaşmanızı ve bir XML yapısı ile ihtiyaç duyulan herhangi bir düğümü ve/veya nitelik arasında hareketi sağlar. Aşağıdaki örneğe bakalım.

Sub PEAKUP()
    Dim Xml As Object, root As Object
    Set Xml = CreateObject("MSXML2.DOMDocument")
    Xml.async = False: Xml.validateOnParse = False
    Xml.Load (ThisWorkbook.Path & "\etf.xml")
    Set root = Xml.DocumentElement
End Sub

 

VBA’da XML DOM Düğümleri

XML
belgesi, tüm DOM‘un (XDoc.DocumentElement türünün) kökünü size sağlayacaktır.

Her bir DocumentElement (XML DOM düğümü) aşağıdaki düğüm referanslarını kolaylaştırır:

parentNodeÜst düğüm, DOM hiyerarşisinde en tepedeki düğüm
firstChildİlk alt düğüm, DOM hiyerarşisindeki ilk Child (çocuk) düğümü
lastChildSon alt düğüm, son düğüm DOM hiyerarşisinde son Child (çocuk) düğümü
childNodesGeçerli düğümün tüm alt düğümleri, DOM hiyerarşisindeki tüm düğümler
nextSiblingAynı ana düğümde, DOM hiyerarşisinde aynı seviyedeki düğüm, yani sonraki kardeş düğümdür
previousSiblingAynı ana düğümde, DOM hiyerarşisinde aynı seviyedeki düğüm, yani önceki kardeş düğümdür.

Yukarıdaki tüm referanslar XML DOM içinde veriye kolayca ulaşmanızı sağlar.

childNodes

Örnek olarak ilk düğümü alalım ve XML ve metin içeriklerini MsgBox olarak karşımıza getirelim.
XML DOM‘da düğümleri dolaşmada temel olarak ChildNodes’i kullanıyoruz.

Sub PEAKUP()
    Dim XDoc As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\etf.xml")
    Set lists = XDoc.DocumentElement
    Set ilkdugum = lists.FirstChild
    MsgBox ilkdugum.Xml
    MsgBox ilkdugum.Text
    Set XDoc = Nothing
End Sub

Bu VBA ile XMLdosyasını indirerek aşağıdaki kodları test edebilirsiniz.

Temel bilgileri aldığımıza göre, şimdi XML‘in tüm içeriğini bu kodlarla Excel‘e yazdırabiliriz.

Sub PEAKUP()
    Dim XDoc As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    XDoc.async = False: XDoc.validateOnParse = False
    XDoc.Load (ThisWorkbook.Path & "\etf.xml")
    Set lists = XDoc.DocumentElement
    For Each satirNode In lists.ChildNodes
        For Each alanNode In satirNode.ChildNodes
            a = a + 1
            Cells(a, 1) = "[" & alanNode.BaseName & "] = [" & alanNode.Text & "]"
        Next alanNode
    Next satirNode
    Set XDoc = Nothing
End Sub

Aşağıdaki farklı örneklerle de bu işlemin yapılabildiğini göreceksiniz.

 

VBA’da XML Öznitelikleri

XML Node‘ları içerisinde mevcut öznitelik olarak belirtilen öznitelik attribute değerini alabilirsiniz.
Şöyle bir XML yapınız olduğunu varsayalım;

<?xml version="1.0" encoding="utf-8"?>
<AnaDugum>
    <Liste>
        <Adi attribute="PEAKUP">ORG</Adi>

XPath kullanarak aşağıda gösterildiği gibi öz niteliği kolayca alabiliriz.

Set adialani = XDoc.SelectNodes("//AnaDugum/Liste[0]/Adi")
MsgBox adialani(0).Attributes(0).Text

 

XML Belge Oluşturmak

ub XML_Belge_Olusturma()
    Dim XDoc As Object, root As Object, elem As Object, rel As Object
    Set XDoc = CreateObject("MSXML2.DOMDocument")
    Set root = XDoc.createElement("Root")
        XDoc.appendChild root
    Set elem = XDoc.createElement("Child")
        root.appendChild elem
    Set rel = XDoc.createAttribute("Oznitelik")
        rel.NodeValue = "PEAKUP"
        elem.setAttributeNode rel
    XDoc.Save ThisWorkbook.Path & "\yeni.xml"
End Sub

Yukarıdaki bilgilere ilave olarak, Xml dosyasından Veri Alma, Arama ve Silme yapmak için kullanılabilecek birkaç kod bloğu paylaşmak istiyorum.

Xml dosyasındaki tüm verileri listelemek için;

Sub XML_Dosyasını_Okuma()
    Dim Etf As Object, ayır() As String, i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    If Etf.Load(ThisWorkbook.Path & "\etf.xml") Then
        ayır = Split(Etf.Text, " ")
        For i = 0 To UBound(ayır)
            Cells(i + 1, 1) = ayır(i)
        Next i
    End If
    Set Etf = Nothing: Erase ayır: i = Empty
End Sub

Xml dosyasında, yonetim Node‘undaki tüm verileri listelemek için;

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim/*")
    For Each Item In Nod
        i = i + 1
        Cells(i, 1) = Item.Text
    Next Item
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

Xml dosyasında, yonetim Node‘undaki adı Murat olan kişinin bilgilerini listelemek için;

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim[ad='Murat']")
    For Each Item In Nod
        i = i + 1
        Cells(i, 1) = Item.Text
    Next Item
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

Xml dosyasında, yonetim Node‘undaki Şehir adı Istanbul olan kişilerin bilgilerini listelemek için;

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim[sehir='Istanbul']")
    For Each Item In Nod
        i = i + 1
        Cells(i, 1) = Item.Text
    Next Item
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

Xml dosyasında, yonetim Node‘undaki adı Emre olan kişinin bilgilerini SelectsingleNode yöntemi ile listelemek için;

DefObj I, N, R
Sub XML_Dosyasından_Veri_Alma()
    Dim i As Integer
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectSingleNode("//yonetim[ad='Emre']")
    Range("A1").Value = Nod.Text
    Set Etf = Nothing: Set Nod = Nothing
    Set Item = Nothing: i = Empty
End Sub

Xml dosyasında, yonetim Node‘undaki site_yönetimi grubundaki “Murat OSMA” yı listeden silmek ve yeni.xml adında farklı kaydetmek için; (Eski dosyanın üzerine de yazabilirsiniz.)

DefObj I, N, R
Sub Nod_Silme()
    Set Etf = CreateObject("MSXML2.DOMDocument")
    Etf.async = False
    Etf.Load (ThisWorkbook.Path & "\etf.xml")
    Set Nod = Etf.SelectNodes("//yonetim[ad='Murat']")
    For Each Item In Nod
        Item.ParentNode.RemoveChild Item
    Next Item
    Etf.Save ThisWorkbook.Path & "\yeni.xml"
End Sub

 

Daha fazla detay için Microsoft Docs‘taki kaynağa bakabilirsiniz.

 

Diğer yazılarımızda görüşmek üzere, hoşça kalın. 🙋🏻‍♂️
Bu yazımızı çevrenizle paylaşarak birçok kişinin de bilgi sahibi olmasını sağlayabilirsiniz. 👍🏻

Önerilen Yazılar

Start typing and press Enter to search

Power BI Mart Ayı En Beğendiklerimizexcel ve outlook toplu mail
X