Link to home
Start Free TrialLog in
Avatar of Ackles
AcklesFlag for Switzerland

asked on

XML to Excel & back to XML

I have an xml exported from an application, made to create user accounts.
My requirement is to be able to attributes in excel, so the file has to be read in excel.
The most important thing is that I should be able to create more xml with same attributes from this excel.

What I mean is that, I should be able to create more users in same excel by just changing the name & be able to export them as xml for user creation.

Please find attached sample xml file.

Avatar of ste5an
Flag of Germany image

Just do with a text editor with syntax highlighting like Notepad++ or sublime.
Avatar of Ackles


I have more than thousand users, you really don't want me to do it that way
Then use a database (SQL Server [Express]), then the export is pretty simple. Otherwise create a list of your users with the necessary information as XML and use XSLT to transform it to your desired output.

Otherwise you can do it also in Excel, but then you need some VBA.
Avatar of Ackles


Thanks for your reply, unfortunately I am not very proficient with it.
Can you please outline the process....

I would prefer VBA, if you would be patient to walk me through.

Populate your user list in your Excel sheet. For simplicity select the list.

Then you can enumerate it in VBA as

Option Explicit

Public Sub Test()

  Dim Row As Excel.Range
  Dim Cell As Excel.Range
  For Each Row In Selection.Rows
    Debug.Print Row.Address
    For Each Cell In Row.Columns
      Debug.Print Chr(9); Cell.Value
    Next Cell
  Next Row

End Sub

Open in new window

Define a template function which takes as parameters your user information and returns the XML fragment. Write it to a new text file.
Avatar of Ackles


I am sorry, but can you be please more detailed, I am not acquainted with vba....
I took your XML data and pasted into a Notepad file.
Saved the file with the XML extension.
Opened the file in Excel - which created a schema for the data
Saved the data as Excel
Excel can also save this back as XML in the Save As... window.

Files attached.
Avatar of Ackles


Thanks, but this makes one xml, I'm looking for way to make multiple xml from one excel based on column value
I re-read your question and what you are trying to create is not clear to me.
If you can open the XML file in Excel - you can edit the XML file with the Excel program and can save as many XML files as needed.
Avatar of Ackles
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Close the question, but note that my solution answered the question.
Avatar of Ackles


I had to get it written in java.
I am happy to share the file , however the type is not allowed to be shared