Parsing excel data ( containing xml ) in VBA

Question

I have a simple excel file containing rows and columns. One of the column contains rows of data which is string like this (XML data)

<Employee Name="R1" Designation="Developer">
   <SkillSet Language="C#"/>
</Employee>
<Employee Name="E2" Designation="Developer">
   <SkillSet Language="Java"/>
</Employee>

I would like to read this information in the excel file, parse it based on particular skillset and put them in the same excel workbook in adjacent column. I may need to provide a button click which may trigger the action.

How do I approach this problem.

Should I write a macro or should I write an excel addin. The excel can be either 2003 or earlier/2007/2010.

I can think of writing a user defined function to read the cell data but, how do I read through the columns in vb and also how do I re-use my function across different excel-workbooks.

Community · Accepted Answer · 2017-05-23 11:52:13Z

2

Although this maybe coming to you at a late hour, however I had thesame problem as you mention. I'm also kind of new to VBA. My client wanted a database application in Access 2003. But after some persuation, I was able to convince them to adapt Access 2007. Which by the way, it's still not reliable, to develop a multi-lingual application.

Anyway to cut the story short, I was able to achieve same, after some google search and using the code I found here;

http://www.freevbcode.com/ShowCode.asp?ID=2922

and here

Parsing XML in VBA

Hope you find this links helpful cheers.

edited May 23, 2017 at 11:52

CommunityBot

11 silver badge

answered May 9, 2013 at 14:34

user28864

3,4731 gold badge28 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Steven · Accepted Answer · 2013-05-05 18:02:18Z

1

Add a reference (Tools-->References) to an XML parser, e.g., Microsoft XML, v6.0 -- my MS Access 2010 installation has a seven XML parsers provided by Microsoft. Declare an instance of the parser and use its properties and methods as you need.

answered May 5, 2013 at 18:02

Steven

1181 bronze badge

1 Comment

Sandepku Over a year ago

thanks, I shall try that out as an option. But the good thing is the client is ready to use Excel 2007 and above.. So I started creating excel addins using VSTO.. I now read XElement.Parse and get my thing done quite easily.. I sure will try this out. but until then I cannot vote this as answer.. But sure will upvote !

Collectives™ on Stack Overflow

Parsing excel data ( containing xml ) in VBA

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related