Wie analysieren XML-VBA

stimmen
62

Ich arbeite in VBA, und will einen String zB analysieren

<PointN xsi:type='typens:PointN' 
xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' 
xmlns:xs='http://www.w3.org/2001/XMLSchema'>
    <X>24.365</X>
    <Y>78.63</Y>
</PointN>

und erhalten Sie die X- und Y-Werte in zwei getrennte Integer-Variablen.

Ich bin ein Neuling, wenn es um XML kommt, da ich in VB6 und VBA bin stecken, weil der Bereich I in Arbeit.

Wie mache ich das?

Veröffentlicht am 14/08/2008 um 17:41
quelle vom benutzer
In anderen Sprachen...              

9 antworten

stimmen
65

Vielen Dank für die Hinweise.

Ich weiß nicht, ob dies der beste Ansatz, um das Problem oder nicht, aber hier ist, wie ich es zu arbeiten. I verweisen den Microsoft XML, v2.6 DLL in meinem VBA, und dann wird der folgende Code-Schnipsel, gibt mir die erforderlichen Werte

Dim objXML As MSXML2.DOMDocument

    Set objXML = New MSXML2.DOMDocument

    If Not objXML.loadXML(strXML) Then  'strXML is the string with XML'
        Err.Raise objXML.parseError.ErrorCode, , objXML.parseError.reason
    End If

Dim point As IXMLDOMNode
Set point = objXML.firstChild

Debug.Print point.selectSingleNode("X").Text
Debug.Print point.selectSingleNode("Y").Text
Beantwortet am 14/08/2008 um 18:40
quelle vom benutzer

stimmen
49

Das ist ein bisschen eine komplizierte Frage, aber es scheint, als ob der direkteste Weg das XML-Dokument oder XML-String über MSXML2.DOMDocument zu laden wäre, die dann erlauben Sie den XML-Knoten zuzugreifen.

Sie können auf den folgenden Seiten mehr über MSXML2.DOMDocument finden:

Beantwortet am 14/08/2008 um 17:47
quelle vom benutzer

stimmen
9

In Bezug Ausbau-> Referenzen Microsoft XML, 6.0 und Sie können Beispielcode verwenden:

    Dim xml As String

    xml = "<root><person><name>Me </name> </person> <person> <name>No Name </name></person></root> "
    Dim oXml As MSXML2.DOMDocument60
    Set oXml = New MSXML2.DOMDocument60
    oXml.loadXML xml
    Dim oSeqNodes, oSeqNode As IXMLDOMNode

    Set oSeqNodes = oXml.selectNodes("//root/person")
    If oSeqNodes.length = 0 Then
       'show some message
    Else
        For Each oSeqNode In oSeqNodes
             Debug.Print oSeqNode.selectSingleNode("name").Text
        Next
    End If 

seien Sie vorsichtig mit XML-Knoten // Root / Person ist mit // root / Person nicht gleich, auch selectSingleNode ( "Name"). Text ist nicht dasselbe mit selectSingleNode ( "name"). Text

Beantwortet am 13/10/2015 um 13:29
quelle vom benutzer

stimmen
7

Dies ist ein Beispiel OPML-Parser arbeitet mit Feeddemon OPML-Dateien:

Sub debugPrintOPML()

' http://msdn.microsoft.com/en-us/library/ms763720(v=VS.85).aspx
' http://msdn.microsoft.com/en-us/library/system.xml.xmlnode.selectnodes.aspx
' http://msdn.microsoft.com/en-us/library/ms256086(v=VS.85).aspx ' expressions
' References: Microsoft XML

Dim xmldoc As New DOMDocument60
Dim oNodeList As IXMLDOMSelection
Dim oNodeList2 As IXMLDOMSelection
Dim curNode As IXMLDOMNode
Dim n As Long, n2 As Long, x As Long

Dim strXPathQuery As String
Dim attrLength As Byte
Dim FilePath As String

FilePath = "rss.opml"

xmldoc.Load CurrentProject.Path & "\" & FilePath

strXPathQuery = "opml/body/outline"
Set oNodeList = xmldoc.selectNodes(strXPathQuery)

For n = 0 To (oNodeList.length - 1)
    Set curNode = oNodeList.Item(n)
    attrLength = curNode.Attributes.length
    If attrLength > 1 Then ' or 2 or 3
        Call processNode(curNode)
    Else
        Call processNode(curNode)
        strXPathQuery = "opml/body/outline[position() = " & n + 1 & "]/outline"
        Set oNodeList2 = xmldoc.selectNodes(strXPathQuery)
        For n2 = 0 To (oNodeList2.length - 1)
            Set curNode = oNodeList2.Item(n2)
            Call processNode(curNode)
        Next
    End If
        Debug.Print "----------------------"
Next

Set xmldoc = Nothing

End Sub

Sub processNode(curNode As IXMLDOMNode)

Dim sAttrName As String
Dim sAttrValue As String
Dim attrLength As Byte
Dim x As Long

attrLength = curNode.Attributes.length

For x = 0 To (attrLength - 1)
    sAttrName = curNode.Attributes.Item(x).nodeName
    sAttrValue = curNode.Attributes.Item(x).nodeValue
    Debug.Print sAttrName & " = " & sAttrValue
Next
    Debug.Print "-----------"

End Sub

Dieser nimmt Multi-Level-Bäume von Ordnern (Awasu, NewzCrawler):

...
Call xmldocOpen4
Call debugPrintOPML4(Null)
...

Dim sText4 As String

Sub debugPrintOPML4(strXPathQuery As Variant)

Dim xmldoc4 As New DOMDocument60
'Dim xmldoc4 As New MSXML2.DOMDocument60 ' ?
Dim oNodeList As IXMLDOMSelection
Dim curNode As IXMLDOMNode
Dim n4 As Long

If IsNull(strXPathQuery) Then strXPathQuery = "opml/body/outline"

' http://msdn.microsoft.com/en-us/library/ms754585(v=VS.85).aspx
xmldoc4.async = False
xmldoc4.loadXML sText4
If (xmldoc4.parseError.errorCode <> 0) Then
   Dim myErr
   Set myErr = xmldoc4.parseError
   MsgBox ("You have error " & myErr.reason)
Else
'   MsgBox xmldoc4.xml
End If

Set oNodeList = xmldoc4.selectNodes(strXPathQuery)

For n4 = 0 To (oNodeList.length - 1)
    Set curNode = oNodeList.Item(n4)
    Call processNode4(strXPathQuery, curNode, n4)
Next

Set xmldoc4 = Nothing

End Sub

Sub processNode4(strXPathQuery As Variant, curNode As IXMLDOMNode, n4 As Long)

Dim sAttrName As String
Dim sAttrValue As String
Dim x As Long

For x = 0 To (curNode.Attributes.length - 1)
    sAttrName = curNode.Attributes.Item(x).nodeName
    sAttrValue = curNode.Attributes.Item(x).nodeValue
    'If sAttrName = "text"
    Debug.Print strXPathQuery & " :: " & sAttrName & " = " & sAttrValue
    'End If
Next
    Debug.Print ""

If curNode.childNodes.length > 0 Then
    Call debugPrintOPML4(strXPathQuery & "[position() = " & n4 + 1 & "]/" & curNode.nodeName)
End If

End Sub

Sub xmldocOpen4()

Dim oFSO As New FileSystemObject ' Microsoft Scripting Runtime Reference
Dim oFS
Dim FilePath As String

FilePath = "rss_awasu.opml"
Set oFS = oFSO.OpenTextFile(CurrentProject.Path & "\" & FilePath)
sText4 = oFS.ReadAll
oFS.Close

End Sub

oder besser:

Sub xmldocOpen4()

Dim FilePath As String

FilePath = "rss.opml"

' function ConvertUTF8File(sUTF8File):
' http://www.vbmonster.com/Uwe/Forum.aspx/vb/24947/How-to-read-UTF-8-chars-using-VBA
' loading and conversion from Utf-8 to UTF
sText8 = ConvertUTF8File(CurrentProject.Path & "\" & FilePath)

End Sub

aber ich verstehe nicht, warum xmldoc4 jedes Mal geladen werden soll.

Beantwortet am 09/05/2010 um 03:19
quelle vom benutzer

stimmen
6

Sie können eine XPath-Abfrage verwenden:

Dim objDom As Object        '// DOMDocument
Dim xmlStr As String, _
    xPath As String

xmlStr = _
    "<PointN xsi:type='typens:PointN' " & _
    "xmlns:xsi='http://www.w3.org/2001/XMLSchema-instance' " & _
    "xmlns:xs='http://www.w3.org/2001/XMLSchema'> " & _
    "    <X>24.365</X> " & _
    "    <Y>78.63</Y> " & _
    "</PointN>"

Set objDom = CreateObject("Msxml2.DOMDocument.3.0")     '// Using MSXML 3.0

'/* Load XML */
objDom.LoadXML xmlStr

'/*
' * XPath Query
' */        

'/* Get X */
xPath = "/PointN/X"
Debug.Print objDom.SelectSingleNode(xPath).text

'/* Get Y */
xPath = "/PointN/Y"
Debug.Print objDom.SelectSingleNode(xPath).text
Beantwortet am 30/12/2014 um 11:24
quelle vom benutzer

stimmen
2

Hier ist eine kurze Unter eine Microstation TriForma XML-Datei zu analysieren, die Daten für Baustähle Formen enthält.

'location of triforma structural files
'c:\programdata\bentley\workspace\triforma\tf_imperial\data\us.xml

Sub ReadTriformaImperialData()
Dim txtFileName As String
Dim txtFileLine As String
Dim txtFileNumber As Long

Dim Shape As String
Shape = "w12x40"

txtFileNumber = FreeFile
txtFileName = "c:\programdata\bentley\workspace\triforma\tf_imperial\data\us.xml"

Open txtFileName For Input As #txtFileNumber

Do While Not EOF(txtFileNumber)
Line Input #txtFileNumber, txtFileLine
    If InStr(1, UCase(txtFileLine), UCase(Shape)) Then
        P1 = InStr(1, UCase(txtFileLine), "D=")
        D = Val(Mid(txtFileLine, P1 + 3))

        P2 = InStr(1, UCase(txtFileLine), "TW=")
        TW = Val(Mid(txtFileLine, P2 + 4))

        P3 = InStr(1, UCase(txtFileLine), "WIDTH=")
        W = Val(Mid(txtFileLine, P3 + 7))

        P4 = InStr(1, UCase(txtFileLine), "TF=")
        TF = Val(Mid(txtFileLine, P4 + 4))

        Close txtFileNumber
        Exit Do
    End If
Loop
End Sub

Von hier aus können Sie die Werte verwenden, um die Form in Micro 2d oder tun es in 3d und extrudieren es zu einem Feststoff zu ziehen.

Beantwortet am 12/01/2015 um 18:30
quelle vom benutzer

stimmen
0

Oft ist es einfacher, ohne VBA zu analysieren, wenn Sie nicht wollen, Makros aktivieren. Dies kann mit der Ersetzen-Funktion erfolgen. Geben Sie Ihre Start- und Zielknoten in Zellen B1 und C1.

Cell A1: {your XML here}
Cell B1: <X>
Cell C1: </X>
Cell D1: =REPLACE(A1,1,FIND(A2,A1)+LEN(A2)-1,"")
Cell E1: =REPLACE(A4,FIND(A3,A4),LEN(A4)-FIND(A3,A4)+1,"")

Und das Ergebnis Linie E1 wird Sie analysiert Wert hat:

Cell A1: {your XML here}
Cell B1: <X>
Cell C1: </X>
Cell D1: 24.365<X><Y>78.68</Y></PointN>
Cell E1: 24.365
Beantwortet am 30/11/2016 um 22:13
quelle vom benutzer

stimmen
0

Aktualisieren

Das Verfahren unten dargestellt gibt ein Beispiel für XML mit VBA Parsen der XML - DOM - Objekte verwenden. Code basiert auf einem Anfänger Führung des XML - DOM .

Public Sub LoadDocument()
Dim xDoc As MSXML.DOMDocument
Set xDoc = New MSXML.DOMDocument
xDoc.validateOnParse = False
If xDoc.Load("C:\My Documents\sample.xml") Then
   ' The document loaded successfully.
   ' Now do something intersting.
   DisplayNode xDoc.childNodes, 0
Else
   ' The document failed to load.
   ' See the previous listing for error information.
End If
End Sub

Public Sub DisplayNode(ByRef Nodes As MSXML.IXMLDOMNodeList, _
   ByVal Indent As Integer)

   Dim xNode As MSXML.IXMLDOMNode
   Indent = Indent + 2

   For Each xNode In Nodes
      If xNode.nodeType = NODE_TEXT Then
         Debug.Print Space$(Indent) & xNode.parentNode.nodeName & _
            ":" & xNode.nodeValue
      End If

      If xNode.hasChildNodes Then
         DisplayNode xNode.childNodes, Indent
      End If
   Next xNode
End Sub

Nota Bene - Diese erste Antwort zeigt die einfachste mögliche , was ich (zu der Zeit war ich auf einem ganz bestimmten Thema arbeiten) vorstellen kann. Natürlich mit Hilfe der XML - Einrichtungen in den VBA XML Dom gebaut wäre viel besser. Sehen Sie die Updates oben.

Original Antwort

Ich weiß, dass dies eine sehr alte Post, aber ich wollte meine einfache Lösung für diese komplizierte Frage teilen. In erster Linie habe ich grundlegende String-Funktionen für den Zugriff auf die XML-Daten verwendet.

Dies setzt voraus, einige XML-Daten (in der temporären Variable), die innerhalb einer VBA-Funktion zurückgegeben wurden. Interessanterweise kann man auch sehen, wie ich in einen XML-Web-Dienst an Verknüpfung den Wert abgerufen werden. Die Funktion des im Bild gezeigt nimmt auch einen Lookup-Wert, da diese Funktion von Excel VBA innerhalb einer Zelle zugegriffen werden kann = Function (Wert1, Wert2) Werte über den Web-Service in ein Tabellenkalkulations zurückzukehren.

Beispielfunktion


openTag = "<" & tagValue & ">"
closeTag = "< /" & tagValue & ">" 

' Locate the position of the enclosing tags startPos = InStr(1, temp, openTag) endPos = InStr(1, temp, closeTag) startTagPos = InStr(startPos, temp, ">") + 1 ' Parse xml for returned value Data = Mid(temp, startTagPos, endPos - startTagPos)

Beantwortet am 21/04/2011 um 17:30
quelle vom benutzer

stimmen
-9

XML-Parsing-Code

Option Explicit
    Dim Path As String ' input path name
    Dim FileName As String ' input file name
    Dim intColumnCount As Integer ' column counter
    Dim intLoop As Integer ' Looping integer
    Dim objDictionary As Scripting.Dictionary ' dictionary object to store column identification for id, method, query string etc
    Dim intPrevRequest_id As Integer 'stores previous request id
    Dim intCurrRequest_id As Integer 'stores current request id

    Dim strWholeReq As String ' Full request that is ready to be written to file
    Dim strStartQuotes As String ' Placeholder which holds starting double quotes
    Dim strEndQuotes As String ' Placeholder which holds ending double quotes
    Dim strStepName As String ' First line of the Parsed_XML_Function. e.g. Parsed_XML_Function("Step5",
    'Here 5 comes from intStepNum variable

    Dim strUrl As String ' contains URL and Query string
    Dim strQueryStr As String ' Query string
    Dim strMethod As String ' Method part of request
    Dim strBody As String 'Body attributes
    Dim strMisc As String ' Misc items such as Resource, Snapshot number etc
    Dim strContentType As String ' Content type of request
    Dim intStepNum As Integer ' iterative count to identify step
    Dim objFileSys As Scripting.FileSystemObject ' file system object
    Dim objFile As Scripting.File 'file object
    Dim objTextStr As Scripting.TextStream 'text stream object
    Dim ActionFileName As String ' destination action name
'this funciton is the main function which calls other functions
Sub Main()

    Path = Worksheets(1).Cells(1, 2).Value
    FileName = Worksheets(1).Cells(2, 2).Value
    ActionFileName = Worksheets(1).Cells(3, 2).Value
    'open xml file
    Workbooks.Open FileName:=Path & "\" & FileName
    'activate the workbook
    Windows(FileName).Activate

    'delete first row
    Rows("1:1").Select
    Selection.Delete Shift:=xlUp
    Range(Selection, Selection.End(xlToRight)).Select
    ActiveSheet.Name = "PARSINGVS_XML"

    'get total columns and analyze the columns
    intColumnCount = Worksheets("PARSINGVS_XML").UsedRange.Columns.Count
    Set objDictionary = New Dictionary

    intLoop = 1
    For intLoop = 1 To intColumnCount
        If InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "Request/#id", 1) > 0 Then
            objDictionary.Add "Req_id", intLoop

         ElseIf InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "Request/@Method", 1) > 0 Then
            objDictionary.Add "Req_method", intLoop

         ElseIf InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "Request/@Url", 1) > 0 Then
            objDictionary.Add "Req_url", intLoop

         ElseIf InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "FormPostHttpBody/@ContentType", 1) > 0 Then
            objDictionary.Add "Req_contenttype", intLoop

         ElseIf InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "FormPostParameter/@Name", 1) > 0 Then
            objDictionary.Add "Req_itemdata_name", intLoop

         ElseIf InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "FormPostParameter/@Value", 1) > 0 Then
            objDictionary.Add "Req_itemdata_value", intLoop

        ElseIf InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "QueryStringParameter/@Name", 1) > 0 Then
            objDictionary.Add "Req_querystring_name", intLoop

        ElseIf InStr(1, Worksheets("PARSINGVS_XML").Cells(1, intLoop), "QueryStringParameter/@Value", 1) > 0 Then
            objDictionary.Add "Req_querystring_value", intLoop
        End If
    Next

    'Loop through all requests and capture querysting, itemdata, url, method, action and content type
    '-----------------------------------------------
    'Initialize variables ot default value at start
    '-----------------------------------------------
    intPrevRequest_id = 1
    intCurrRequest_id = 1
    strStartQuotes = """"
    strEndQuotes = """," & vbCrLf
    intStepNum = 1
    strQueryStr = ""
    strBody = ""

    Set objFileSys = New Scripting.FileSystemObject
    objFileSys.CreateTextFile (Path & "\" & ActionFileName)
    Set objFile = objFileSys.GetFile(Path & "\" & ActionFileName)
    Set objTextStr = objFile.OpenAsTextStream(ForAppending, TristateUseDefault)

    intLoop = 2 'first line is the header
    For intLoop = 2 To Worksheets("PARSINGVS_XML").UsedRange.Rows.Count
        If objDictionary.Exists("Req_id") Then
            intCurrRequest_id = Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_id")).Value)
        Else
            MsgBox "XML do nto contain Request id column"
            Exit Sub
        End If

        'if current and previous request id are not same OR we are at end of steps the write to file
        If (intPrevRequest_id <> intCurrRequest_id) Or (intLoop = Worksheets("PARSINGVS_XML").UsedRange.Rows.Count) Then
            Call WriteToFile
            'iterate to next step
            intStepNum = intStepNum + 1
            strQueryStr = ""
            strBody = ""
            intPrevRequest_id = intCurrRequest_id
        End If

        Call Write_Remaining_DESTINATIONVS_Req ' build the DESTINATIONVS request apart from Body & Query string
        Call WriteQuery_Body 'build hte body and querystring
    Next
    MsgBox "Completed"
    Set objDictionary = Nothing
    objTextStr.Close

    Set objTextStr = Nothing
    Set objFile = Nothing
    Set objFileSys = Nothing
    Windows(FileName).Close (False)
End Sub
'funciton to write contents to file
Sub WriteToFile()

    strWholeReq = strWholeReq & vbCrLf & strStepName & strUrl

    If strQueryStr <> "" Then
        strWholeReq = strWholeReq & "?" & strQueryStr
    End If

    strWholeReq = strWholeReq & strEndQuotes & strMethod & strContentType & strMisc

    If strBody <> "" Then
        strWholeReq = strWholeReq & strStartQuotes & "Body=" & strBody & strEndQuotes
    End If

    strWholeReq = strWholeReq & " LAST);" & vbCrLf

    objTextStr.WriteLine strWholeReq

    strWholeReq = ""
End Sub
'function to build the querystring and body part which are iterative
Sub WriteQuery_Body()
    If objDictionary.Exists("Req_querystring_name") Then
        If Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_querystring_name")).Value) <> "" Then
            If strQueryStr <> "" Then
                strQueryStr = strQueryStr & "&"
            End If
            'Querystring
            strQueryStr = strQueryStr & Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_querystring_name")).Value) & "=" & _
               Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_querystring_value")).Value)
        End If
    End If
    If objDictionary.Exists("Req_itemdata_name") Then
        If Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_itemdata_name")).Value) <> "" Then
            If strBody <> "" Then
                strBody = strBody & "&"
            End If
            'Body
            strBody = strBody & Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_itemdata_name")).Value) & "=" & _
               Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_itemdata_value")).Value)
        End If
    End If
End Sub
'function which creates remaining part of web_custom request other than querystring and body
Sub Write_Remaining_DESTINATIONVS_Req()
    'Name of Parsed_XML_Function("Step2",
    strStepName = "Parsed_XML_Function(" & strStartQuotes & "Step" & intStepNum & strEndQuotes

    If objDictionary.Exists("Req_url") Then
        '"URL = "
        strUrl = strStartQuotes & _
        "URL=" & Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_url")).Value)
    End If

    If objDictionary.Exists("Req_method") Then
        'Method =
        strMethod = strStartQuotes & _
        "Method=" & Trim(Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_method")).Value)) & strEndQuotes
    End If

    If objDictionary.Exists("Req_contenttype") Then
        'ContentType =
        If Trim(Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_contenttype")).Value)) <> "" Then
        strContentType = strStartQuotes & _
        "RecContentType=" & Trim(Trim(Worksheets("PARSINGVS_XML").Cells(intLoop, objDictionary("Req_contenttype")).Value)) & strEndQuotes
        Else
           strContentType = strStartQuotes & "RecContentType=text/html" & strEndQuotes
        End If
    Else
        strContentType = strStartQuotes & "RecContentType=text/html" & strEndQuotes
    End If
    'remaining all
    strMisc = strStartQuotes & "TargetFrame=" & strEndQuotes & _
         strStartQuotes & "Resource=0" & strEndQuotes & _
         strStartQuotes & "Referer=" & strEndQuotes & _
         strStartQuotes & "Mode=HTML" & strEndQuotes & _
         strStartQuotes & "Snapshot=t" & intStepNum & ".inf" & strEndQuotes
End Sub
Beantwortet am 02/10/2013 um 16:56
quelle vom benutzer