locked
How is the rigth way to open a XML file -- EXCEL 2003, VBA RRS feed

  • Question

  • Hello.

    How is the rigth way to open a XML file without message to choose via option box. Please see the screenshot.

    XML _OPEN

    Is this the rigt way? Have somebody a good example?

    TEST_A

    Regards Andreas

    MYFILE = ThisWorkbook.Path & "\"
        FileNum = FreeFile
    
        'Workbooks.OpenText Filename:=Application.GetOpenFilename( _
        '    "XML Files (*.xml),*.xml", , "Pick the xml logging file"), Origin:=437, _
        '    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
        '    ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
        '    Space:=False, Other:=False, FieldInfo:=Array(1, 2)
       
        Filename = Application.GetOpenFilename( _
            "XML Files (*.xml),*.xml", , "Pick the xml logging file")
            
        Workbooks.OpenText Filename, Origin:=437, _
            StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, Comma:=False, _
            Space:=False, Other:=False, FieldInfo:=Array(1, 2)
       
        Last_Row = Cells(Rows.Count, 1).End(xlUp).Row - 1
        
        
        MyPath = GetPathOrFileName(Filename, True)

    Tuesday, August 7, 2012 10:26 AM

Answers

  • If you want to open this fie by VBA so I can recommend you Phaser control: Mictosoft XML, vX.0 (in references where X is a version 3 to 6)

    File %System32%\msxml6.dll

       Dim xmlDOM As New MSXML2.DOMDocument
       Dim xmlNodes As MSXML2.IXMLDOMNodeList
       Dim xmlNode As MSXML2.IXMLDOMNode
       Dim strXML As String
    
        Set xmlDOM = CreateObject("MSXML2.DOMDocument")
    If xmlDOM.Load(your_file.xml") Then
            strXML = xmlDOM.XML else 
    exit sub 'or error msg
    end if
    
    Set xmlNodes = xmlDOM.selectNodes("/aaa/bbb") 'chains
    'you can use filter like this
    'Set objXMLFound = SearchForNodes(strXML, "where", "what")
    
    'or grab data in chains loop
    For Each xmlNode In xmlNodes 'loop in chains
    DoEvents
    debug.print xmlNode.selectSingleNode("name_of_chain").text
    'you can if or assign to cell.value in range, or to LV control
    next


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, August 7, 2012 11:37 AM
    Answerer

All replies

  • If you want to open this fie by VBA so I can recommend you Phaser control: Mictosoft XML, vX.0 (in references where X is a version 3 to 6)

    File %System32%\msxml6.dll

       Dim xmlDOM As New MSXML2.DOMDocument
       Dim xmlNodes As MSXML2.IXMLDOMNodeList
       Dim xmlNode As MSXML2.IXMLDOMNode
       Dim strXML As String
    
        Set xmlDOM = CreateObject("MSXML2.DOMDocument")
    If xmlDOM.Load(your_file.xml") Then
            strXML = xmlDOM.XML else 
    exit sub 'or error msg
    end if
    
    Set xmlNodes = xmlDOM.selectNodes("/aaa/bbb") 'chains
    'you can use filter like this
    'Set objXMLFound = SearchForNodes(strXML, "where", "what")
    
    'or grab data in chains loop
    For Each xmlNode In xmlNodes 'loop in chains
    DoEvents
    debug.print xmlNode.selectSingleNode("name_of_chain").text
    'you can if or assign to cell.value in range, or to LV control
    next


    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved

    Tuesday, August 7, 2012 11:37 AM
    Answerer
  • Set xmlDOM = CreateObject("MSXML2.DOMDocument")
    If xmlDOM.Load(your_file.xml") Then
            strXML = xmlDOM.XML else 
    exit sub 'or error msg
    end if

    Hello,

    thanks, so we can use CreateObject another way to open a file.

    Set xmlDOM = CreateObject("TXT ?   XXX.DOMDocument")

    Is this way also possible to open a textfile? Similar with CreateObject?

    Which way you prefer?

    My question is answered.

    Regards Andreas


    Tuesday, August 7, 2012 1:12 PM
  • This is vay to import
    If xmlDOM.Load("C:\temp\your_file.xml") Then 'open method in if sentence giving you way to getting out

    but you can do it that way (without err control)

    On Error GoTo brak xmlDOM.Load "c:\temp\youre_file.xml" On Error GoTo 0

    'mode code...

    exit sub

    brak: 'when error

    end sub



    This line only create object from control, you have to do that to import file to it (before assigne file)

    Set xmlDOM = CreateObject("MSXML2.DOMDocument")



    Oskar Shon, Office System MVP

    Press if Helpful; Answer when a problem solved



    Tuesday, August 7, 2012 1:56 PM
    Answerer