XML en Excel

Pagina's in dit artikel

XML in Excel

Wat meer (Office) praktijk nu. Met Excel 2003/2007/2010/2013 is het binnenhalen van XML gegevens erg gemakkelijk. Helaas zijn (bij Excel 2003) de XML faciliteiten alleen beschikbaar in de Excel uitvoering binnen Microsoft Office Professional of als stand-alone toepassing. Wanneer vanuit Excel 2003 Bestand, Openen--> wordt gekozen, dan is te zien dat standaard de XML bestanden ook in de lijst met te openen bestanden zichtbaar zijn.

Figuur 2: Het Bestand Openen venster van Excel 2003.

Indien het bestand test.xml gekozen wordt, dan verschijnt een keuzemenu (zie figuur 3).

Figuur 3: Excel vraagt wat er met het XML bestand moet worden gedaan

Als wordt gekozen voor de derde optie, dan zal Excel het XML bestand analyseren en de gevonden structuur presenteren in een Taakvenster (figuur 4). Het bestand zelf wordt dan niet geopend.

Figuur 4, de structuur in het taakvenster XML-Bron

De elementen uit het XML bestand kunnen nu aan cellen in Excel worden gekoppeld, eenvoudigweg door ze te verslepen van het taakvenster naar een cel in Excel. Zie figuur 5 en 6.

Figuur 5: Verslepen van element naar een Excel cel

Figuur 6 Resultaat na verslepen van een element

Door een element te verslepen dat meer dan 1 sub-elementen kan bevatten (bijvoorbeeld het element "Medewerker"), worden automatisch de sub elementen meegenomen en als lijst in Excel geplaatst (zie figuur 7).

Figuur 7: Lijst gemaakt door het verslepen van het element "Medewerker".

Hiermee is een Excel sjabloon gemaakt dat gegevens uit XML bestanden kan importeren met dezelfde structuur als het bestand test.xml. Omdat deze exercitie is begonnen door het bestand test.xml te openen, kan dit worden gedaan door op het knopje "XML-gegevens Vernieuwen" te klikken (zie figuur 8). De gegevens uit het bestand Test.xml worden in het zojuist opgebouwde sjabloon ingelezen. Figuur 9 toont het resultaat in Excel.

knop XML-gegevens Vernieuwen op de werkbalk Lijst
Figuur 8: knop "XML-gegevens Vernieuwen" op de werkbalk "Lijst"

Figuur 9: resultaat van gegevens importeren.

Om een ander bestand in dezelfde structuur in te lezen kan de optie XML-Gegevens Importeren" worden gebruikt, welke zich op dezelfde werkbalk bevindt. De aanwezige gegevens worden dan vervangen door de gegevens uit het nieuwe bestand.

Vragen, suggesties en opmerkingen

Al het commentaar over deze pagina:

Commentaar van: ton vermeij (8-9-2006 06:45:19) deeplink naar dit commentaar

Bij mij komt niet het tussenscherm wanneer ik in excel de xml file (test.xml) oproep. Ik kan dus niet de keuze maken voor de derde optie zoals genoemd. Heeft dit te maken dat ik minimaal met excel 2003 moet werken en niet met excel 2002 sp3?

Commentaar van: Jan Karel Pieterse (8-9-2006 06:55:06) deeplink naar dit commentaar

Hoi Ton,

Klopt, het artikel is geschreven voor Excel 2003 (Professional).

Groetjes,

Jan Karel Pieterse

Commentaar van: Harry (15-10-2006 01:04:06) deeplink naar dit commentaar

Figuur 3 krijg ik wel te zien, daarna krijg ik een scherm met de melding dat "De opgegeven XML-bron verwijst niet naar een schema. Excel maakt een schema op basis van de XML-brongegevens."
Lijkt alsof ik een schema ergens vandaan moet halen? Wat te doen?

Commentaar van: Jan Karel Pieterse (15-10-2006 02:02:01) deeplink naar dit commentaar

Hallo Harry,
Dat klopt, dat schermpje krijg je inderdaad.
Als je gewoon op OK klikt, dan zal Excel het XML bestand analyseren en een schema voor je maken.
Dat schema kan je overigens niet zien of opslaan, dat blijft "intern Excel".

Commentaar van: Owen (20-11-2006 05:26:04) deeplink naar dit commentaar

Ik krijg figuur 3 niet te zien, bij mij wordt het gelijk geopend en dat is nou net een puinhoop.
Ik werk trouwens wel met Excel 2003 SP2

Commentaar van: Jan Karel Pieterse (20-11-2006 06:02:10) deeplink naar dit commentaar

Hallo Owen,

Ik vermoed dat u dan geen Office professional heeft, klopt dat?

Commentaar van: Edwin (17-12-2006 13:02:41) deeplink naar dit commentaar

Even bij Microsoft de addin voor Excel 2003 downloaden en dan kun je XML ook inlezen.

<a target="_blank" href="http://www.microsoft.com/downloads/details.aspx?familyid=72852247-6AFD-425C-83B1-1F94E4AC2775&displaylang=en">XML Tools Add-in</a>

Commentaar van: Jan Karel Pieterse (18-12-2006 01:20:54) deeplink naar dit commentaar

Hoi Edwin,

Bedankt voor de link.

Commentaar van: Frank (4-10-2007 15:59:23) deeplink naar dit commentaar

Jan Karel,

t/m figuur 5 loopt alles prima mee, figuur 6 is bij mij een twijvelgeval (wel het figuurtje zeg maar, maar geen tekst), en figuur 7 blijft bij mij leeg, en de rest laat zic dus raden.

Nu vrees ik dat dit komt omdat ik met office 2003 basic werk. in de help van MS staat "Microsoft Office Professional Edition 2003 en Microsoft Office Excel 2003". zoals dat hier staat denk ik dat Excel als ik Excel 2003 gebruik dit dus zou moeten werken. Uit jouw tekst denk ik echter te lezen dat dit allen bij Excel 2003 werkt, als er verder geen office onderdelen zijn geïnstalleerd.

Klopt dit? En zo ja, is het dan een optie om office te deinstalleren, en alleen excel te installeren, of moet je dan echt een losse Excel versie kopen? Bestaat dat?

Alvast bedankt
Frank

Commentaar van: Jan Karel Pieterse (4-10-2007 21:19:57) deeplink naar dit commentaar

Hallo Frank,

Het komt inderdaad door je Excel versie. Alleen Excel installeren is niet voldoende helaas, je zal echt 1 van beide versies moeten aanschaffen.

Commentaar van: hans schreurs (21-10-2009 01:09:32) deeplink naar dit commentaar

Goede en correcte uitleg.
Bedankt

Commentaar van: mariska (30-1-2010 03:33:54) deeplink naar dit commentaar

ik wil excel gaan leren, heb een boek gekocht met oefeningen. moet de oefeningen downloaden, maar krijg ze niet geopend, alleen vreemde tekens , beginnend met < en eindigend met > snap er niets meer van, wat DOE ik fout, of wat IS er fout? iemand die me kan helpen?? heb windows 7 en heb microsoft office 2007 prof

Commentaar van: Jan Karel Pieterse (30-1-2010 08:00:11) deeplink naar dit commentaar

Hallo Mariska,

Dit gebeurt soms als je Excel 2007 bestanden download van internet en direct probeert te openen.
Kies de optie opslaan bij het downloaden en probeer de opgeslagen bestanden te openen met Excel.

Commentaar van: mariska (30-1-2010 08:08:36) deeplink naar dit commentaar

dank u wel voor het snelle antwoord. het is nu idd gelukt. hooop dat ik u niet meer nodig heb, maar anders weet ik jullie te vinden,
dank je wel

Commentaar van: Jan Karel Pieterse (30-1-2010 11:14:16) deeplink naar dit commentaar

Hallo Mariska,

Je kan dit soort vragen heel goed kwijt op het Nederlandse Office forum:

http://office.webforums.nl/forum/index.php

Commentaar van: DEVOS RUDY (3-8-2010 12:34:05) deeplink naar dit commentaar

ik ben op zoek naar een manier om data in Excel om te zetten in xml-format. Ik ben een leek op dat vlak - kan iemand helpen.

Commentaar van: Pim (11-8-2010 05:14:21) deeplink naar dit commentaar

Ik heb een XML file inglezen in excel en alle gegevens zitten erin, maar niet zoals ik wil. De structuur wordt tijdens het inlezen automatisch overgenomen uit het xml bestand.

Van sommige items worden namelijk meerdere regels aangemaakt, omdat verschillende variabelen van die betreffende items per kolom voorkomen. Als hij dus 2 of meer waarden van de betreffende kolomkop heeft maakt hij voor elk item en nieuwe regel en begint vervolgens ook nog eens op een nieuwe regel voor de volgende variabele van de volgende kolom.

Klein simpel voorbeeld:Stel: een regel uit jullie voorbeeld laat voor Jan Janssen 2 verschillende huisnummers zien.
Dan worden er 3 regels aangemaakt.

1: de regel met het 1e huisnummer
2: de regel met het 2e huisnummer
3. een nieuwe regel zonder de huisnummers maar met de varibalen van een volgende kolom.

MEt andere woorden: ik krijg nooit 1 regel te zien waarin alle kolommen gevuld zijn.
Hoe krijg ik dat toch voor elkaar?

Commentaar van: Jan Karel Pieterse (16-8-2010 03:27:57) deeplink naar dit commentaar

Hallo Rudy,

Ik kan zeker helpen, stuur svp een email met je specifieke verzoek. Zie adres onderaan deze pagina.

Commentaar van: Jan Karel Pieterse (16-8-2010 04:33:46) deeplink naar dit commentaar

Hallo Pim,

Dit kan alleen door de XML in VBA te verwerken, je kan niet beinvloeden op welke manier Excel de XML importeert in het werkblad.

Commentaar van: Chas (18-6-2013 23:25:41) deeplink naar dit commentaar

Hallo Jan Karel,
kwam al zoekend op je site. Handige aanwijzing. Maar heb een vervolgvraag. Ik heb van een leverancier een Cd gekregen met meerdere xml files. Deze horen bij elkaar en vormen, volgens de leverancier, een groot databestand. Ik heb al van alles geprobeerd.
Het maken van xsd, xsl files etc. Hoe kan ik dit het beste doen.

Mvgr Chas.

Commentaar van: Jan Karel Pieterse (19-6-2013 07:38:02) deeplink naar dit commentaar

Hoi Chas,

Heb je van die leverancier geen specificatie gekregen of zo?
Je kunt die xml bestanden gewoon openen in Excel om de data te bekijken, is dat niet genoeg?

Commentaar van: Chas (19-6-2013 21:01:07) deeplink naar dit commentaar

Hallo Jan Karel,
dank voor je snelle respons. Mijn antwoord op je vraag is helaas nee. Ik kan de xml files wel per stuk inlezen en bekijken. Maar blijkbaar verwijzen de verschillende files op een bepaalde naar elkaar. Zo zijn er files: Propeties, Sentences, References en Products. Ik vermoed dat de daadwerkelijke data in de file Products zit en dat de andere files op een bepaalde wijze de informatie uit Products halen. Er zijn 12 Products files en deze zijn
behoorlijk groot. Ik heb geprobeerd een totaal xsd file te maken in een poging een totaal schema te krijgen. Ik heb een xsd file kunnen maken met 1 Products file. Met meer kreeg de Pc een geheugenprobleem. Dus hoe kan ik nu te werk gaan met multiple xml files die duidelijk bij elkaar horen?
Mvgr Chas

Commentaar van: Jan Karel Pieterse (20-6-2013 13:05:33) deeplink naar dit commentaar

Hallo Chas,

Ik heb het sterke vermoeden dat die XML bestanden samen een database vormen (met gerelateerde tabellen). Je zou ze dus eigenlijk in MSAccess of een dergelijk programma moeten importeren wil je er echts iets aan hebben.

Commentaar van: Delphine (25-6-2014 23:34:01) deeplink naar dit commentaar

Beste Jan Karel,

Ik zit met een xml vraagje.
Ik zou graag vanuit excel een xml aanmaken. Ik heb een specifiek xsd schema. Helaas krijg ik steeds de melding met als fout' kan niet geëxporteerd worden wegens lijst van lijsten'. Kan jij mij daarmee helpen ? Ik vermoed dat het op te lossen is met vba maar daar ben ik helemaal niet in thuis.

Alvast bedankt voor je respons!
Delphine

Commentaar van: Jan Karel Pieterse (26-6-2014 07:05:02) deeplink naar dit commentaar

Hallo Delphine,

Natuurlijk. Neem s.v.p. contact op middels het email adres onderaan deze pagina.

Commentaar van: Roel (20-3-2015 11:20:37) deeplink naar dit commentaar

Hallo Jan Karel,

Ik weet dat het een verouderd bericht is maar ik heb een kort vraagje.

Kan ik de handeling uit figuur 8 ook met vba uit kunnen voeren?
Het gaat over een vaste tabel die maandelijks ververst moet worden in office 2010

Alvast bedankt
Groeten Roel

Commentaar van: Jan Karel Pieterse (20-3-2015 13:41:45) deeplink naar dit commentaar

Hoi Roel,

Uiteraard. Heb je al geprobeerd een macrootje op te nemen terwijl je die handeling doet?

Commentaar van: Roel (20-3-2015 19:31:04) deeplink naar dit commentaar

Hallo Jan Karel,

Dat was inderdaad het eerste wat ik geprobeerd heb maar er werd niks opgenomen. Daarom ben ik het internet aan het afstruinen naar de code maar ik kan niks vinden helaas.

Groeten Roel

Commentaar van: Jan Karel Pieterse (23-3-2015 10:56:35) deeplink naar dit commentaar

Hi Roel,

De syntax is:

Worksheets("Sheet1").ListObjects("Table1").XmlMap.DataBinding.Refresh

Commentaar van: Roel (23-3-2015 14:52:33) deeplink naar dit commentaar

Geweldig.
Het werkt meteen.
Nu hoef ik het niet meer handmatig te vernieuwen

Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: excelexperts.nl/forum/index.php.