XML to Excel & back to XML

Hello,
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.

Regards,
AUser-List.xml
LVL 11
AcklesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
Just do with a text editor with syntax highlighting like Notepad++ or sublime.
AcklesAuthor Commented:
I have more than thousand users, you really don't want me to do it that way
ste5anSenior DeveloperCommented:
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.
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

AcklesAuthor Commented:
Hello,
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.

Regards,
A
ste5anSenior DeveloperCommented:
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.
AcklesAuthor Commented:
I am sorry, but can you be please more detailed, I am not acquainted with vba....
Richard DanekeTrainerCommented:
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.
UserAccts.xml
UserAccts.xlsx
AcklesAuthor Commented:
Thanks, but this makes one xml, I'm looking for way to make multiple xml from one excel based on column value
Richard DanekeTrainerCommented:
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.
AcklesAuthor Commented:
I got a java program written which does what I want in just one click by providing the Source Excel.


Thanks a lot all of you for your help.
A

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Richard DanekeTrainerCommented:
Close the question, but note that my solution answered the question.
AcklesAuthor Commented:
I had to get it written in java.
I am happy to share the file , however the type is not allowed to be shared
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.