How to map XML columns in a Excel spreadsheet using VBA

[verylucky1]

Hi Folks, I have this requirement from my IT project that there is will be some XML files that need to be mapped to an Spreadsheet via VBA. Some XMLs will contain master data and some will contain detail data. Hence the columns on spreadsheet will not be in tabular format. They will be scattered at different rows and columns. Here are my requirements:

1- Map XML file columns to Excel cells.
2- XML files contain hundreds of rows so need to add a parameter on Excel that should bring one row of data for master and all detail rows.
3- Add a refresh button on the Excel that when clicked should refresh data from the XMLs.

Please let me know if you need more information or sample spreadsheet.
Thanks in advance for any direction and help.

 

[programmerinthedark]

Hi, You'll need to look in the vba help at the ImportXML method. Having done that, you'll need to look at the xmlmap object, and make sure that you have your schema set correctly. Once you've done that, the whole process should be fairly straightforward. As to the refresh button, you just need to attach the macro to it, or at least a stripped down version of it. Would be nice to see the xml and the kind of spreadsheet you are trying to create.
Take care,
PID

 

[verylucky1]

Hi, Thanks for responding. Do you happen to have some sample code that at least maps few columns? That would give me a jump start. Thanks in advance.

 

[SkipVought]

1. Read the VBA Help as directed.

2. TRY an IMPORT on the sheet. When you are comfortable with the process...

3. turn on your macro recorder and record the process.

Post back with your recorded code for further help customizing.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[programmerinthedark]

Hi again,
The importxml method is probably the way I'd go, but if you are using office 2003 or higher, there already exist tools that will help you, and take most of the donkey work out of the project. go to:

http://office.microsoft.com/en-au/excel-help/how-to-use-xml-in-excel-2003-HA001101964.aspx

which describes what you should do far better than I can. I'd suggest that you want to check out the third option, which details how to build your map etc. Once you've done this, it should be easily recordable in a macro.
Best,
PID