Results 1 to 12 of 12
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    XML Mapping and related details (Excel 2003)

    Hi all,

    I noticed some course material that had details about XML Import/Exporting. And also a section on XML Mapping. I have never used this before and it seemed quite complicated in the manual I browsed. Can anyone give me some details about what XML Mapping is all about (in laymens terms) and tell me if this is just a gimmick...or if its really a useful feature!

    Tx
    Regards,
    Rudi

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: XML Mapping and related details (Excel 2003)

    You could try this from MS.
    Download details: Excel 2003: XML Mapping Video

    I do not have XL2003 so have never used this new feature...

    Steve

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XML Mapping and related details (Excel 2003)

    Tx for the link...

    It seems that not many loungers use/work with this feature. I was hoping to get some "inside" info from loungers who have used this to find out their experiences with mapping and XML!

    Cheers
    Regards,
    Rudi

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML Mapping and related details (Excel 2003)

    There is a little book called "Excel 2003 programming, a developers notebook" published by O'Reilly that has some fairly good stuff about XML in Excel.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XML Mapping and related details (Excel 2003)

    Is this book as "little" as you say? Most programming books are like bibles...over a 1000 pages of technical detail!!
    Regards,
    Rudi

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML Mapping and related details (Excel 2003)

    294 pages. Because it is just about:

    - Excel and XML
    - Sharepoint lists and workspaces
    - Get data from web (webservices)
    - Program Excel with .NET
    - Security
    - Infopath (no idea why that chapter is in the book though)

    Mostly aimed at the programmer: VBA code and like.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XML Mapping and related details (Excel 2003)

    Tx for the details Jan Karel.
    Much abliged!
    Regards,
    Rudi

  8. #8
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML Mapping and related details (Excel 2003)

    Rudi,

    I couldn't live without it!

    I have a large amount of UK bus stop data which comes with an XML schema ("Map" in XL2003 terms). I often want to look at bits of it without seeing all the data, and it's sooooo useful. First of all, I use the XML pane to "add" a map (the schema). Then I drag the elements I want from the map to the top of individual columns. I can even add formulae (e.g. if I have dragged "Eastings" to A1 and "Northings" to B1, then in C2 I can enter =B1 & "," & C1 to get a full grid reference). Then just click and import an XML file against this map, and presto! Just the data I want to see - with the formula repeated and calculated down every line.

    Want an extra field? No problem, drag it in and click "refresh" and up it comes.

    The best bit, though, is what you can do with vlookup. I have bus stops in one file with "local" IDs and grid refs. I also have the same stops (I hope) in a national data set with unique national IDs and grid refs. But there is a separate file for each authority area. So, I create the map file as above, and in the list of local IDs, I do a VLOOKUP based on the grid ref to get the matching unique national ID. I can go for each admin area in turn, and as the size of the mapped file changes (as it would, since different areas have different numbers of stops) so the bottom of the looked-up range changes, AND SO DO MY VLOOKUP FORMULAE. It's fantastic - I always had to go and do it maually before this came along!

    Of course, I have had the map defined for me, but even so - great feature!!

    Hope that helps.

    Stuart

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XML Mapping and related details (Excel 2003)

    Stuart, you make it sound very impressive! Based on what you've said, I should really investigate this further. I have had no exposure to XML mapping and schemas...
    It sounds as if you need an XML schema (or file) available that you can open. From this schema, you can create a custom map on the spreadsheet and then import data from the schema to the customised map (so as only to see data you are interested in!) I hope I have this analagy correct??

    If this is the case, i must look deeper into it, as it sounds really flexible and could be great in various business solutions!

    Tx for the detailed reply...and for your personal comments. I appreciate it!
    Cheers
    Regards,
    Rudi

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML Mapping and related details (Excel 2003)

    Hi Rudi,

    I have a basic article on xml in Excel 2003. But it is in Dutch. Let me know if you can deal with that <img src=/S/smile.gif border=0 alt=smile width=15 height=15>.

    Also, check out this site:

    New Tools for Working with Excel and Custom XML Schema
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    3 Star Lounger
    Join Date
    Feb 2001
    Location
    Leigh on Sea, Essex, England
    Posts
    263
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: XML Mapping and related details (Excel 2003)

    Glad you thought it sounded interesting. It is certainly that!

    You don't *have* to have a schema - Excel can generate one for you if you open an Excel file without a schema. It is obviously better to have one, though, if you can manage it.

    Cheers,

    Stuart

  12. #12
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: XML Mapping and related details (Excel 2003)

    If you can send me the articale...it would be great....Any words I don't understand i'll have my father translate for me when I visit him as he is Dutch.
    Tx

    I think I removed my email from my profile. if you don't have it, just drop me a PM!

    Cheers




    Tx again Stuart....you really got me interested!
    Regards,
    Rudi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •