Een grafiek maken van onbewerkte gegevensbestanden in elk Office-product

Zou het niet fijn zijn om de mogelijkheid te hebben om gewoon een Excel-spreadsheet of een Word-document te openen, en zonder iets te hoeven doen, worden gegevens rechtstreeks uit een tekst- of CSV-gegevensbestand gelezen en rechtstreeks in een ingesloten grafiek geladen op uw spreadsheet of Word-document? Dit is een vorm van automatisering met behulp van Office-producten, want als het mogelijk is om het in kaart brengen van gegevens in Office te automatiseren, denk dan eens aan de mogelijkheden. U kunt automatisch gegevens in een grafiek laden voor een rapport dat u samenstelt voor uw baas - geen gegevensinvoer vereist. Of u kunt gegevens rechtstreeks naar een grafiek in een e-mail in Outlook laden.

De techniek om dit te bereiken omvat het installeren van de add-in Office Web Components. Bovendien hoeft u slechts een paar dingen te configureren binnen de Office-toepassing die u wilt gebruiken om de gegevens te importeren en bent u klaar om uw rapportagewerk te automatiseren. Ik heb een aantal elementen behandeld die we in dit artikel in eerdere VBA-artikelen zullen gebruiken. Hoe kunt u uw eigen eenvoudige app maken met VBA Hoe kunt u uw eigen eenvoudige app maken met VBA Voor degenen onder u die echt dol zijn om je eigen applicatie te kunnen schrijven, maar nog nooit een enkele regel code hebt getypt, zal ik je helpen om je hele ... Lees hier meer bij MakeUseOf. Een paar daarvan waren het doorgeven van gegevens tussen applicaties met het klembord. Alle informatie tussen VBA-applicaties passeren Het klembord gebruiken Alle informatie tussen VBA-applicaties doorgeven Het klembord gebruiken Een van de meest frustrerende aspecten van het werken met VBA in specifieke applicaties, is dat het niet altijd gemakkelijk is om twee applicaties te krijgen “praten” naar elkaar. U kunt proberen voor zeer snelle transacties ... Lees meer, Outlook-taken exporteren naar Excel Hoe uw Outlook-taken exporteren naar Excel met VBA Hoe uw Outlook-taken exporteren naar Excel met VBA Of u nu een fan bent van Microsoft, een goede zaak dat kan worden gezegd over MS Office-producten, tenminste, is hoe gemakkelijk het is om elk van hen te integreren met elkaar ... Lees meer en verzenden van e-mails vanuit een Excel Hoe e-mails versturen vanuit een Excel-spreadsheet VBA-scripts gebruiken Hoe e-mails verzenden van een Excel-spreadsheet met VBA-scripts Onze codesjabloon helpt u bij het instellen van geautomatiseerde e-mails vanuit Excel met behulp van Collaboration Data Objects (CDO) en VBA-scripts. Meer lezen script.

Wat ik u laat zien, is hoe u Office Web Components combineert met tekstbestandsscripting om een ​​naadloze, geautomatiseerde gegevensstroom van een vlak tekstbestand op uw computer naar uw Office-product te maken (in ons geval Excel). U kunt deze zelfde techniek gebruiken - met een paar kleine wijzigingen - in Word, Outlook of zelfs PowerPoint. Elk Office-product (of een ander product voor die kwestie) met een VBA-back-end voor ontwikkelaars, stelt u in staat om deze techniek te gebruiken.

Importeer en maak een grafiek uit gegevensbestanden

Voordat u een grafiek kunt maken op basis van gegevensbestanden, moet u eerst een aantal dingen inschakelen. In dit voorbeeld laat ik u zien hoe u deze functies in Excel kunt inschakelen, maar het proces is vrijwel identiek in elk ander Office-product.

Ten eerste, voordat u een VBA-ontwikkeling kunt uitvoeren, moet u het tabblad Ontwikkelaar in de werkbalk inschakelen (als u dit nog niet hebt gedaan). Om dit te doen, gaat u gewoon naar het menu Opties en klikt u op “Lint aanpassen” en schakel de “Ontwikkelaar” tab.

Terug in Excle, zult u nu zien “Ontwikkelaar” verschijnen in de menu's. Klik erop en klik op “Ontwerpmodus”. Klik vervolgens op “Bekijk code” om de VBA-editor te zien.

In de editor moet je de referenties activeren die je nodig hebt voor de code die ik je ga geven om te werken. Zorg ervoor dat u de Office Web Components-invoegtoepassing hebt geïnstalleerd voordat u dit doet, anders is het Microsoft Chart-object niet beschikbaar.

Klik op Extra en vervolgens op Verwijzingen en u ziet een lijst met alle verwijzingen die beschikbaar zijn op uw systeem. Als u niet weet wat dit zijn - referenties zijn in feite bibliotheken met code en objecten die u kunt invoegen in uw eigen project. Hiermee kun je heel coole dingen doen, afhankelijk van welke referentie je inschakelt. Als u zojuist Office Web Components op uw systeem hebt geïnstalleerd, moet u dit als een nieuwe bibliotheek toevoegen. Klik daarom op de knop Bladeren om het juiste dll-bestand te vinden.

Als u Office Web Components hebt geïnstalleerd, wordt het DLL-bestand OWC11.dll genoemd en opgeslagen in c: \ program files \ common files \ microsoft shared \ web components \ 11 \

Klik op het selectievakje voor de “Microsoft Office Web Components 11.0” referentie, en vergeet ook niet om te selecteren “Microsoft Scripting Runtime” ook, die u toegang geeft tot het lezen of schrijven van gegevensbestanden.

Nu u de referentie hebt toegevoegd, is het tijd om de eigenlijke grafiek aan uw blad toe te voegen. In Excel kunt u bedieningselementen toevoegen door op te klikken “invoegen” in het menu Ontwikkelaar en klik op het pictogram voor kleine gereedschappen in de onderstaande hoek “ActiveX-besturingselementen”.

Ga naar “Microsoft Office Chart 11.0” en klik op OK.

We komen eindelijk aan het werk. Dit is wat de MS Web Component-grafiek lijkt te zijn ingesloten in een spreadsheet. Het ziet er hetzelfde uit in een Word-document of iets anders.

Dus in het geval van Excel wil ik dat het diagram onmiddellijk gegevens uit het gegevensbestand laadt bij het openen van het werkmapbestand. Om dit te doen, ga je naar de code-editor door op te klikken “Bekijk code” in het ontwikkelaarsmenu en dubbelklik op de werkmap om de werkboekcode te zien. Verander de juiste dropdown naar “Open”. Dit is het script dat zal worden uitgevoerd wanneer het werkmapbestand voor het eerst wordt geopend.

Om het diagram met gegevens uit code te laden, heeft het diagram zelf een naam nodig. Ga terug naar de spreadsheet, klik met de rechtermuisknop op het diagram en kies Eigenschappen. Je zult het zien “Naam” veld met zoiets “ChartSpace1”. U kunt dit in alles veranderen. Ik heb de mijne gebeld “MyChart”.

Ook, zodat u weet hoe het gegevensbestand is - mijn is een tekstbestand gevuld met gegevenswaarden in door komma's gescheiden indeling. Dit bestand kan alles zijn - laboratoriumgegevens die worden geëxporteerd van sensoren, financiële gegevens die handmatig in het bestand worden ingevoerd door stagiairs of iets anders. Je leest het bestand in met je code, dus het maakt niet uit hoe de gegevens eruit zien, zolang je maar weet hoe elke regel eruit zal zien wanneer je programma het inleest.

Dus nu voor het leuke gedeelte. Ik ga je de code in kleine delen laten zien, dus het is niet overweldigend, en leg uit wat de code doet. De bovenkant van de code leest eerst alle waarden uit het tekstbestand en slaat deze op in twee arrayx, één voor x variabelen (xVar) en één voor y-variabelen (yVar).

Dim als nieuwe FileSystemObject Dim fnum Dim MyFile als string Dim strDataLine als string Dim xVar () als Variant Dim yVar () Als Variant Dim intNumOfLines Als geheel getal MyFile = "c: \ files \ MyData.txt" fnum = FreeFile () Open MyFile Voor Input als # 1 intNumOfLines = 0 Do While Not EOF (1) intNumOfLines = intNumOfLines + 1 Input # 1, strDataLine Input # 1, strDataLine Loop Close # 1 ReDim xVar (intNumOfLines) ReDim yVar (intNumOfLines) Open MyFile For Input As # 1 intNumOfLines = 0 Do while Not EOF (1) Input # 1, xVar (intNumOfLines) Input # 1, yVar (intNumOfLines) intNumOfLines = intNumOfLines + 1 Loop Close # 1

Deze code loopt in principe tweemaal door het databestand - de eerste keer dat de arrays worden gedimensioneerd, zodat ze exact de lengte hebben die nodig is om de gegevens op te slaan, en vervolgens een tweede keer om de gegevens in die arrays te lezen. Als u niet weet wat een array is, is het een variabele of een opslaggebied dat een lange lijst met waarden bevat waartoe u toegang hebt met behulp van de volgorde waarin de waarde in de array is opgeslagen. Het ene geladen derde zou bijvoorbeeld (3) zijn.

Nu dat u twee van deze arrays hebt geladen met alle waarden uit uw gegevensbestand, kunt u die waarden laden in de grafiek die u al hebt ingesloten. Hier is de code die dat doet.

Met Sheet1.MyChart .Clear. Vernieuw Set oChart = .Charts.Add oChart.HasTitle = True oChart.Title.Caption = "Mijn gegevenswaarden" 'oChart.Interior.Color = "blue" oChart.PlotArea.Interior.Color = " white "Set oSeries = oChart.SeriesCollection.Add With oSeries .Caption =" My Data Values ​​".SetData chDimCategories, chDataLiteral, xVar. SetData chDimValues, chDataLiteral, yVar .Line.Color =" blue ".Line.DashStyle = chLineDash .Line .Weight = 2. Type = chChartTypeLine End With oChart.HasLegend = True oChart.Legend.Position = chLegendPositionBottom End With

Het is de “Sheet1.MyChart” die de code verbindt met het daadwerkelijke diagram dat u hebt ingesloten. Het is gebaseerd op wat je het noemde. Dit is het geval wanneer u het in Word, PowerPoint of een ander Office-product insluit. U zult het niet gebruiken met “Sheet1”, maar in plaats daarvan welk element de grafiek in dat geval bevat, zoals “document1” in Word bijvoorbeeld.

De bovenstaande code stelt vervolgens het labelen en inkleuren van de grafiek in en laadt vervolgens de waarden met behulp van de “.setData” methode voor zowel x- als y-waarden van de tweedimensionale gegevensset. Zodra de bovenstaande code klaar is, verschijnt de volgende grafiek.

Deze gegevens komen rechtstreeks uit het tekstbestand. Het enige nadeel hier is dat de gegevensbestanden alleen tweedimensionaal hoeven te zijn als u de bovenstaande code wilt gebruiken. U kunt meer waarden toevoegen aan de gegevensset, maar u moet de bovenstaande code wijzigen om de derde waarde elke keer in de lus te lezen en vervolgens de “SeriesCollection.Add” sectie om nog een reeks te maken en deze op dezelfde manier aan het diagram toe te voegen.

Het lijkt misschien ingewikkeld om de bovenstaande code te lezen, maar als je eenmaal een van deze hebt gedaan, is het een fluitje van een cent om deze aan te passen voor wat je nodig hebt. U kunt dezelfde grafiek en soortgelijke code gebruiken om een ​​staafdiagram, een spreidingsdiagram of elk ander diagramtype te maken dat u met ditzelfde object wilt gebruiken. Het is veelzijdig en flexibel - en het is een krachtig hulpmiddel in uw arsenaal als u een collega-fan bent van automatisering voor een hogere productiviteit.

Speel rond met de bovenstaande code en kijk of je gegevens automatisch in je applicaties kunt laden. Welke creatieve toepassingen kunt u bedenken voor dit soort automatisering? Deel uw mening en feedback in de opmerkingen hieronder!

Ontdek meer over: Microsoft Excel, Microsoft PowerPoint, Microsoft Word.