none
convert EXCEL to XML

    Question

  • I try to convert EXCEL to XML format like below but I still have problem on the tag name DNDBIDs.  I have also placed the VBA script here.

    <CfgData xxxxxxxxxxxxxxxxxxxxxxxxxxxx >

                <CfgReference>

                            <CfgTenantRef xxxxxxxxxxxxxxxxxxxxxxxxxx >

                            <CfgDNRef xxxxxxxxxxxxxxxxxxxxxxxxxx >

                            <CfgDNRef xxxxxxxxxxxxxxxxxxxxxxxxxx >

                            <CfgFolderRef xxxxxxxxxxxxxxxxxxxxxxxxxxx >

                </CfgReference>

                <CfgCreate>

    <CfgPlace xxxxxxxxxxxxxxxxxxxxx >

    <DNDBIDs xmlns=”http://www.genesyslab.com/cs” >

                <CfgPlaceDN linkDBID=”xxxxxxxxxxx” />

                <CfgPlaceDN linkDBID=”xxxxxxxxxxx” />

    </DNDBIDs>

    </CfgPlace>

                </CfgCreate>

                <CfgReference>

                            <CfgTenantRef xxxxxxxxxxxxxxxxxxxxxxxxxx >

                            <CfgDNRef xxxxxxxxxxxxxxxxxxxxxxxxxx >

                            <CfgDNRef xxxxxxxxxxxxxxxxxxxxxxxxxx >

                            <CfgFolderRef xxxxxxxxxxxxxxxxxxxxxxxxxxx >

                </CfgReference>

                <CfgCreate>

    <CfgPlace xxxxxxxxxxxxxxxxxxxxx >

    <DNDBIDs xmlns=”http://www.genesyslab.com/cs” >

                <CfgPlaceDN linkDBID=”xxxxxxxxxxx” />

                <CfgPlaceDN linkDBID=”xxxxxxxxxxx” />

    </DNDBIDs>

    </CfgPlace>

                </CfgCreate>

    </CfgData>

     

    Sub ConvertToCfgXML()
       
        Dim CfgXml, root, cfgref, cfgcreate, node, attr
       
        Let StartRowIndex = 8
        Let StartColIndex = 1
        Let IsAgentColIndex = 5
        Let FirstNameColIndex = 2
        Let LastNameColIndex = 3
        Let EIDColIndex = 4
        Let AgentIDCntr = 1
        Let DNID = 4
        Let DNName = 5
        Let SchemaName = "http://www.genesyslab.com/cs"
       
        Set CfgXml = CreateObject("MSXML2.DOMDocument")
        Set node = CfgXml.createProcessingInstruction("xml", "version='1.0'")
        CfgXml.appendchild node
        Set node = Nothing
        
        Set root = CfgXml.createNode(1, "CfgData", SchemaName)
        Set attr = CfgXml.createAttribute("version")
        attr.Value = "7.2.000.05"
        root.setAttributeNode attr
        Set attr = Nothing
        CfgXml.appendchild root
       
        RowIndex = StartRowIndex
        Set curcell = Worksheets(1).Cells(RowIndex, StartColIndex)
       
    ' DO WHILE LOOP
        Do While curcell.Value <> ""
           
            Set curLocation = Worksheets(1).Cells(RowIndex, 1)
            Set curSite = Worksheets(1).Cells(RowIndex, 2)
            Set curPCID = Worksheets(1).Cells(RowIndex, 3)
            Set curDN = Worksheets(1).Cells(RowIndex, 4)

        ' ++ Begin of CfgReference tag
            Set cfgref = CfgXml.createNode(1, "CfgReference", SchemaName)
            root.appendchild cfgref
           
        ' ++ BEGIN of CfgTenantRef
            Set node = CfgXml.createNode(1, "CfgTenantRef", SchemaName)
            Set attr = CfgXml.createAttribute("id")
            attr.Value = "TheTenantID"
            node.setAttributeNode attr
            Set attr = Nothing
           
            Set attr = CfgXml.createAttribute("name")
            attr.Value = "101"
            node.setAttributeNode attr
            Set attr = Nothing
        ' ++ END of CfgTenantRef
       
            cfgref.appendchild node
       
        ' ++ BEGIN of CfgDNRef DN Tag

            Set node = CfgXml.createNode(1, "CfgDNRef", SchemaName)
            Set attr = CfgXml.createAttribute("id")
            attr.Value = "CfgDN" & curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
           
            Set attr = CfgXml.createAttribute("name")
            attr.Value = curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
                     
        ' ++ END of CfgDNRef DN Tag
       
            cfgref.appendchild node
       
        ' ++ BEGIN of CfgDNRef ACD POS Tag

            Set node = CfgXml.createNode(1, "CfgDNRef", SchemaName)
            Set attr = CfgXml.createAttribute("id")
            attr.Value = "CfgDN" & "838" & curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
           
            Set attr = CfgXml.createAttribute("name")
            attr.Value = "838" & curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
                     
        ' ++ END of CfgDNRef ACD POS Tag
       
            cfgref.appendchild node
       
        ' ++ BEGIN Folder Ref
            Set node = CfgXml.createNode(1, "CfgFolderRef", SchemaName)
            Set attr = CfgXml.createAttribute("id")
            attr.Value = "cfgFolderRef392"
            node.setAttributeNode attr
            Set attr = Nothing
           
            Set attr = CfgXml.createAttribute("name")
            attr.Value = "Bulk_DN_wong"
            node.setAttributeNode attr
            Set attr = Nothing
                     
        ' ++ END of Folder Ref
       
            cfgref.appendchild node
        ' ++ END of CfgReference tag
       
           
    ' BEGIN of CfgCreate TAG
            Set cfgcreate = CfgXml.createNode(1, "CfgCreate", SchemaName)
            root.appendchild cfgcreate
           
    ' BEGIN OF CfgPlace TAG
            Set node = CfgXml.createNode(1, "CfgPlace", SchemaName)
           
            Set attr = CfgXml.createAttribute("id")
            attr.Value = "CfgPlace" & "838" & curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
            
            Set attr = CfgXml.createAttribute("name")
            attr.Value = "place_" & "838" & curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
                   
            Set attr = CfgXml.createAttribute("ownerDBID")
            attr.Value = "TheTenantID"
            node.setAttributeNode attr
            Set attr = Nothing
                         
            cfgcreate.appendchild node
           
    ' END OF cfgPlace

    ' BEGIN OF DNDBIDs TAG
    '        Set DNDBIDs = CfgXml.createNode(1, "DNDBIDs", SchemaName)
    '        root.appendchild DNDBIDs

            Set node = CfgXml.createNode(1, "DNDBIDs", SchemaName)
            Set attr = CfgXml.createAttribute("xmlns")
            attr.Value = "http://www.genesyslab.com/cs"
            node.setAttributeNode attr
            Set attr = Nothing
           
            cfgcreate.appendchild node
           
    ' BEGIN OF CfgPlaceDN
            Set node = CfgXml.createNode(1, "CfgPlaceDN", SchemaName)
            Set attr = CfgXml.createAttribute("linkDBID")
            attr.Value = "CfgDN" & curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
           
            cfgcreate.appendchild node
           
            Set node = CfgXml.createNode(1, "CfgPlaceDN", SchemaName)
            Set attr = CfgXml.createAttribute("linkDBID")
            attr.Value = "CfgDN2" & "838" & curDN.Value
            node.setAttributeNode attr
            Set attr = Nothing
           
            cfgcreate.appendchild node

    'END OF cfgcreate TAG

            RowIndex = RowIndex + 1
            Set curcell = Worksheets(1).Cells(RowIndex, StartColIndex)
        Loop
       
        DefaultFileName = ActiveWorkbook.Path & "\" & "Place_Data_C.xml"
       
        fileSaveName = Application.GetSaveAsFilename(DefaultFileName, "XML Files (*.xml), *.xml")
        If fileSaveName <> False Then
            CfgXml.Save fileSaveName
        End If
       
    End Sub

     

    Thursday, March 22, 2007 9:40 PM