none
Write XML entry with Excel VBA RRS feed

  • Question

  • Hi guys,

    I made a userform that allows the person to enter entries in the excel spreadsheet.  Everytime that the person clicks on "Submit" button, the entry is added on a new row in the excel spread sheet.  That works perfectly fine.  Now, I would like to also have a XML file in C:\ to record whatever the person enters in the userform.  I know how to do it in C#, but not VBA.  My workplace has access to Excell VBA only.

    This is my macro for the Excel userform:

    Private Sub SubmitCmd_Click()
    'this is to make sure that all fields are selected

        
        'this is for the submit button, when click, data are put on the excel sheet, may have to play with the A4 and the rows (1,0)
        'This button is to add multiple entries.
        ActiveWorkbook.Sheets("Ticket Tracker").Activate
        Range("F1").Select
        Do

        ActiveCell.Offset(0, 0) = Date  'this should be the first node
        ActiveCell.Offset(0, 1) = Time  'this is the time node
        ActiveCell.Offset(0, 2) = Environ("USERNAME") 'this is the person who enters the entry
        ActiveCell.Offset(0, 3) = Textbox1 'this is the commend fields'
        ActiveCell.Offset(0, 4) = Textbox2 'this is the action taken

        Call UserForm_Initialize

    End Sub

     

    So basically everything is working fine to put the entries on the excel spreadsheet.  I want the submit button to also add an entry in the XML file located in C: drive (for example: C:\record.xml).

    The node is like this :

    <Entry>
       <Date></Date>
       <Time></Time>
       <Name></Name>
       <Comments></Comments>
       <Action></Action>
    </Entry>

     

    Anyone knows a solution for this?

    thank you.

    Thursday, February 2, 2012 1:06 PM

Answers

  • >>>where would i put that code?

    The VBA Code goes into SubmitCmd_Click()

    >>> I want whatever entry that I put on the xls (excel file) to be also recorded on the XML.

    The code that I have given you is a sample on how to create an XML file from VBA. If you want an exact copy of the Excel File as an XML file then you can always save the Excel file as an XML spreadsheet once you finish updating the Excel document.

    For example

        ActiveWorkbook.SaveAs Filename:="C:\Book1.xml", FileFormat:=xlXMLSpreadsheet, _
        ReadOnlyRecommended:=False, CreateBackup:= False
    


    >>>does your code assumes that ive already imported the XML file?

    No. Like I mentioned above, it will create a fresh XML document with the values that you specify in the SubmitCmd_Click()

     


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    • Marked as answer by CSharpNoob2011 Thursday, February 2, 2012 5:25 PM
    Thursday, February 2, 2012 5:10 PM
    Moderator

All replies

  • Here is a simple example on how to achieve it. I have hardcoded the values. I am sure you can adapt it to your code.

    '~~> Set reference to Microsoft XML version X.X
    
    Private Sub Sample()
       Dim objDom As DOMDocument
       Dim objXMLRootelement As IXMLDOMElement
       Dim objXMLelement As IXMLDOMElement
       Dim objXMLattr As IXMLDOMAttribute
       
       Set objDom = New DOMDocument
       
       '~~> Creates root element
       Set objXMLRootelement = objDom.createElement("Entry")
       objDom.appendChild objXMLRootelement
       
       '~~> Create Date element
       Set objXMLelement = objDom.createElement("Date")
       objXMLRootelement.appendChild objXMLelement
       
       '~~> Creates Attribute to the Date Element and set value
       Set objXMLattr = objDom.createAttribute("Value")
       objXMLattr.NodeValue = "3/2/2012"
       objXMLelement.setAttributeNode objXMLattr
    
       '~~> Create Time element
       Set objXMLelement = objDom.createElement("Time")
       objXMLRootelement.appendChild objXMLelement
       
       '~~> Creates Attribute to the Time Element and set value
       Set objXMLattr = objDom.createAttribute("Value")
       objXMLattr.NodeValue = "12 PM"
       objXMLelement.setAttributeNode objXMLattr
       
       '~~> Creates Name element
       Set objXMLelement = objDom.createElement("Name")
       objXMLRootelement.appendChild objXMLelement
       
       '~~> Creates Attribute to the Name Element and set value
       Set objXMLattr = objDom.createAttribute("Value")
       objXMLattr.NodeValue = "CSharpNoob2011"
       objXMLelement.setAttributeNode objXMLattr
       
       '~~> Creates Comments element
       Set objXMLelement = objDom.createElement("Comments")
       objXMLRootelement.appendChild objXMLelement
       
       '~~> Creates Attribute to the Comments Element and set value
       Set objXMLattr = objDom.createAttribute("Value")
       objXMLattr.NodeValue = "Sample to Create an XML Doc"
       objXMLelement.setAttributeNode objXMLattr
       
       '~~> Creates Action element
       Set objXMLelement = objDom.createElement("Action")
       objXMLRootelement.appendChild objXMLelement
       
       '~~> Creates Attribute to the Action Element and set value
       Set objXMLattr = objDom.createAttribute("Value")
       objXMLattr.NodeValue = "Do Nothing"
       objXMLelement.setAttributeNode objXMLattr
       
       '~~> Saves XML data to a file
       objDom.Save ("C:\CSharpNoob2011.xml")
    End Sub
    



    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    Thursday, February 2, 2012 4:33 PM
    Moderator
  • where would i put that code?

    I have 2 files. 1 xml, 1 xls.   I want whatever entry that I put on the xls (excel file) to be also recorded on the XML.  It is even possible?

    Thursday, February 2, 2012 4:58 PM
  • does your code assumes that ive already imported the XML file?
    Thursday, February 2, 2012 4:58 PM
  • >>>where would i put that code?

    The VBA Code goes into SubmitCmd_Click()

    >>> I want whatever entry that I put on the xls (excel file) to be also recorded on the XML.

    The code that I have given you is a sample on how to create an XML file from VBA. If you want an exact copy of the Excel File as an XML file then you can always save the Excel file as an XML spreadsheet once you finish updating the Excel document.

    For example

        ActiveWorkbook.SaveAs Filename:="C:\Book1.xml", FileFormat:=xlXMLSpreadsheet, _
        ReadOnlyRecommended:=False, CreateBackup:= False
    


    >>>does your code assumes that ive already imported the XML file?

    No. Like I mentioned above, it will create a fresh XML document with the values that you specify in the SubmitCmd_Click()

     


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    • Marked as answer by CSharpNoob2011 Thursday, February 2, 2012 5:25 PM
    Thursday, February 2, 2012 5:10 PM
    Moderator
  • amazing.

    Thank you very much sir.

    Thursday, February 2, 2012 5:26 PM
  • It works great, however, it puts only 1 entry.  DO you know how to add addition entries in the xml isntead of overwriting it?

    Also, the format would be

    <Entry>
       <Date></Date>
       <Time></Time>
       <Name></Name>
       <Comments></Comments>
       <Action></Action>
    </Entry>

    not

    <Entry><Date></Date><Time></Time><Name></Name><Comments></Comments>
       <Action></Action>/Entry>

    Thursday, February 2, 2012 5:33 PM
  • >>>It works great, however, it puts only 1 entry.  DO you know how to add addition entries in the xml isntead of overwriting it?

    There are two ways that I can think of.

    1) One is opening the existing XML document and appending data to it OR

    2) Once the user has finished entering all the data into the workbook then export the Excel Range at once to the XML File.

    Option 2 is far much more easier than option 1.

    >>>Also, the format would be

    IMHO the format you suggested or I suggested is irrelevant till the time the tags are perfect. If you open both the xml files in a webbrowser, they will look same :)


    Sid (A good exercise for the Heart is to bend down and help another up) Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in VB.Net/Excel forum and email me the link and I will help you if I can.
    Thursday, February 2, 2012 5:44 PM
    Moderator
  • We can try the others as below (TEXT file ==. XML)

       Dim FSO As Object
        Dim NewFile As Object
        Dim FullPath As String
        Dim XMLFileText As String

        FullPath = "F:\chkTest.xls"
        'On Error GoTo Err:

        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set NewFile = FSO.CreateTextFile(FullPath, 1, 1)

        XMLFileText = ""
        XMLFileText = XMLFileText & "<?xml version=" & Chr(34) & "1.0" & Chr(34) & "?>" & vbNewLine
        XMLFileText = XMLFileText & "<DATAROOT>" & vbNewLine
        NewFile.Write (XMLFileText)

        'Data in Excell files
        XMLFileText = "<DATA>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "<C10>" & Range("C10") & "</C10>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "<D10>" & Range("D10") & "</D10>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "<E10>" & Range("E10") & "</E10>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "</DATA>" & vbNewLine
        NewFile.Write (XMLFileText)

        ' Each element
        XMLFileText = "<DATA>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "<C11>" & Range("C11") & "</C11>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "<D11>" & Range("D11") & "</D11>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "<E11>" & Range("E11") & "</E11>" & vbNewLine
        NewFile.Write (XMLFileText)

        XMLFileText = "</DATA>" & vbNewLine
        NewFile.Write (XMLFileText)



        XMLFileText = "</DATAROOT>" & vbNewLine
        NewFile.Write (XMLFileText)

        NewFile.Close

                                                                                              
    Wednesday, April 24, 2013 9:31 AM
  • This TEXT solution from the guy above is just awesome. How to have it creating multiple files? Say the parameters for each XML will be placed in each line - every new line would be a new XML. That would make my day! 
    Wednesday, July 3, 2013 10:51 PM