Transfer Partial Excel Data into XML and viceversa

[robin786]

I am using Excel 2007. I have an Excel Sheet "Sheet1" in the workbook "Employee.xlsx".
It has about 20 columns and the number of rows varies. I wish to transfer only the first 10 columns ( all the rows included ) into an XML file ( "C:\Employee.xml" ).
The first row in Excel Sheet has title headers. The actual data starts from row number 2.

Could some one suggest me an Excel VBA Code for this.



I also wish to work on the opposite phonomenon also.
I have another XML file "C:\Dept.xml".

It is of the format

<dept>
<deptno>10</deptno>
<deptname>Accounting</deptname>
<deptloc>Dallas</deptloc>
<deptmanager>David Smith</deptmanager>
<deptno>20</deptno>
<deptname>Finance</deptname>
<deptloc>Miami</deptloc>
<deptmanager>Roger Brown</deptmanager>
</dept>

The XML file has several number of records, However I wish to obtain only records where
deptloc = 'New York' and transfer those records into an Excel Sheet "C:\Department.xslx".

I do not wish to have other records.

Please suggest me an Excel VBA Code to transfer partial XML data into Excel

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[robin786]

Dim objDom As DOMDocument
Dim objXMLRootelement As IXMLDOMElement
Dim objXMLelement As IXMLDOMElement
Dim objXMLattr As IXMLDOMAttribute

Set objDom = New DOMDocument
Dim i As Integer
Dim j As Integer
Dim str As String

'~~> Creates root element
Set objXMLRootelement = objDom.createElement("Employee")
objDom.appendChild objXMLRootelement





For j = 2 To 20


For i = 1 To 10

str = Worksheets("sheet2").Cells(1, i)
Set objXMLelement = objDom.createElement(str)
objXMLRootelement.appendChild objXMLelement
'objXMLelement.nodeName = Worksheets("sheet2").Cells(j, i)

Set objXMLattr = objDom.createAttribute("Value")
objXMLattr.NodeValue = Worksheets("sheet2").Cells(j, i)
objXMLelement.setAttributeNode objXMLattr
Next

Next

'~~> Saves XML data to a file
objDom.Save ("C:\temp\Employee.xml")




But..... This is giving wrong results

 

[SkipVought]

exactly what wrong results? please be specific.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[combo]

You get a plain xml file with a series of nodes attached to the root node, is this what you intended to have? If so, a text file would be simpler.
If your data is arranged into single table, with rows descripting ad employee, one of two (simple) solutions would be more suitable:
1.
each node describes an employee, values are stored in nodes named as headers,
2.
each node describes an employee, values are stored in attributes named as headers.

Sample codes:

Dim objDom As DOMDocument
Dim objXMLRootelement As IXMLDOMElement
Dim objXMLElement As IXMLDOMElement
Dim objXMLElement2 As IXMLDOMElement

Set objDom = New DOMDocument
Dim i As Integer
Dim j As Integer
Dim str As String

'~~> Creates root element
Set objXMLRootelement = objDom.createElement("Employees")
objDom.appendChild objXMLRootelement
'~~> Creates employee nodes
For j = 2 To 20
    Set objXMLElement = objDom.createElement("Employee")
    For i = 1 To 10
        str = Worksheets("WorksheetName").Cells(1, i)
        Set objXMLElement2 = objDom.createElement(str)
        objXMLElement2.Text = Worksheets("WorksheetName").Cells(j, i)
       objXMLElement.appendChild objXMLElement2
    Next
objXMLRootelement.appendChild objXMLElement
Next
'~~> Saves XML data to a file
objDom.Save ("PathAndFileName.xml")

Dim objDom As DOMDocument
Dim objXMLRootelement As IXMLDOMElement
Dim objXMLElement As IXMLDOMElement

Set objDom = New DOMDocument
Dim i As Integer
Dim j As Integer
Dim str As String

'~~> Creates root element
Set objXMLRootelement = objDom.createElement("Employees")
objDom.appendChild objXMLRootelement
'~~> Creates employee nodes
For j = 2 To 20
    Set objXMLElement = objDom.createElement("Employee")
    For i = 1 To 10
        str = Worksheets("WorksheetName").Cells(1, i)
       objXMLElement.setAttribute str, Worksheets("WorksheetName").Cells(j, i)
    Next
objXMLRootelement.appendChild objXMLElement
Next
'~~> Saves XML data to a file
objDom.Save ("PathAndFileName.xml")

BTW, there is a separate XML forum: forum426


combo