Bijgewerkt door Brad Jones op 25 augustus 2017.
Tijdens je werkweek zijn er waarschijnlijk heel vaak dat je informatie uit Microsoft Excel in Word kopieert en plakt, of andersom.
Dit is hoe mensen vaak geschreven rapporten produceren op basis van gegevens die zijn verzameld en bijgewerkt in een spreadsheet. Excel is een geweldig programma voor alles, van het maken van professionele grafieken 6 Nieuwe Excel-grafieken en hoe ze te gebruiken 6 Nieuwe Excel-grafieken en hoe ze te gebruiken Wilt u meer uit uw Excel-diagrammen halen? Hier is een overzicht van enkele nieuwe grafieken die in de nieuwste versie van Excel zijn geïntroduceerd. Lees meer, om een projectmanagementtracker in te stellen 10+ Nuttige Excel-sjablonen voor Project Management & Tracking 10+ Nuttige Excel-sjablonen voor Project Management & Tracking Templates zijn voor projectmanagers wat messen zijn voor professionele chefs: onmisbaar. We laten u zien hoe u succesvolle projecten kunt repliceren door kant-en-klare sjablonen te gebruiken in Microsoft Excel en verder. Lees meer - maar als het gaat om het schrijven van een rapport, is Word een veel betere tool.
In dit artikel gaan we dieper in op VBA-scripting op de achtergrond waarmee u verbindingen kunt programmeren tussen gegevens die zijn opgeslagen in een Excel-bestand en Word-documenten waar u rapporten aan het produceren bent.
Het is verrassend eenvoudig om Microsoft Excel-gegevens te integreren in Microsoft Word als u eenmaal weet hoe u de juiste referenties moet toevoegen en hoe u de syntaxis van de VBA-achtergrondcode kunt indelen.
Het werkblad instellen
In dit voorbeeld begin ik met een vrij eenvoudige Excel-spreadsheet. In de praktijk kan het Excel-bestand bestaan uit meerdere spreadsheets met veel gegevens - het maakt niet uit.
Zolang je weet waar je de gegevens in de spreadsheet kunt vinden, kun je deze bereiken en pakken met VBA. E-mails verzenden vanuit een Excel-spreadsheet VBA-scripts gebruiken E-mails verzenden vanuit een Excel-spreadsheet VBA-scripts gebruiken U zult zien hoe u geautomatiseerde e-mails vanuit Excel kunt instellen met behulp van Collaboration Data Objects, (CDO) en VBA-scripts. Onze codesjablonen maken dit een stuk eenvoudiger dan het klinkt! Lees verder .
Dit is hoe mijn voorbeeld-spreadsheet eruit ziet. Het is een lijst met onkostennota's die het hele jaar door zijn berekend.
Het Word-document opstellen
Stel dat u een manager heeft die graag een mooi opgemaakt rapport ziet waarin de uitgaven worden beschreven, waarbij items als items worden gegroepeerd en de informatie wordt gepresenteerd in een lay-out die iets esthetischer is.
U kunt dit doen door objecten zoals tekstvakken en labels op te nemen in uw Word-document. Wanneer u in Word bent, klikt u gewoon op het tabblad Ontwikkelaarmenu en selecteert u vervolgens "Ontwerpmodus" in het gedeelte Besturingselementen . Gebruik het vervolgkeuzepictogram Legacy-tools om verschillende elementen in uw document in te voegen.
Gebruik dit menu om een label in te voegen.
Zodra u het label in het document hebt geplaatst waar u het wilt hebben (niet altijd een gemakkelijke taak), bent u klaar om de datafeed te programmeren. Maar eerst moet u het label een naam geven zodat de VBA het kan identificeren. Klik met de rechtermuisknop op het label en ga naar Eigenschappen . Zoek het veld (Naam) en noem het iets dat u kunt onthouden.
Voeg nu een opdrachtknop toe vanuit dezelfde vervolgkeuzelijst met Legacy-tools en dubbelklik erop om de VBA-editor te openen. Wanneer u uw code later laat werken, kunt u deze zo wijzigen dat de code wordt uitgevoerd op de gebeurtenis Document Open () . U ziet dat in de vervolgkeuzelijsten van het object in het editorvenster.
Werken met VBA
Om te beginnen met het verbinden van Word met Excel, moet je ervoor zorgen dat je naar de laatste kunt verwijzen in VBA. De Excel VBA-programmeerhandleiding voor beginners De Excel VBA-programmeerhandleiding voor beginners VBA is een Microsoft Office-gereedschap. U kunt het gebruiken om taken met macro's te automatiseren, triggers in te stellen en nog veel meer. We zullen u kennis laten maken met Excel visuele basisprogrammering met een eenvoudig project. Lees verder .
Bereid de Excel-gegevensimport voor
Klik op Extra en vervolgens op Verwijzingen . Blader door de lijst totdat u de Microsoft Excel 16.0-objectbibliotheek ziet en selecteer deze.
Als je dit eenmaal hebt gedaan, is de rest gewoon een kwestie van een belachelijk eenvoudig VBA-script schrijven om gegevens uit een Excel-spreadsheet op te halen en de labeltitel automatisch bijwerken met de gegevens.
Hier is de code:
Private Sub CommandButton1_Click () Dim objExcel als nieuw Excel.Application Dim exWb Als Excel.Workbook Set exWb = objExcel.Workbooks.Open ("C: \ Users \ Brad \ Desktop \ expenses.xlsx") ThisDocument.total_expenses.Caption = exWb. Spreadsheets ("Sheet1"). Cellen (12, 2) exWb.Sluiten Set exWb = Niets eindigt op Sub Zie je hoe dat werkt? Het Excel-toepassingsobject "exWb" opent het Microsoft Excel-bestand op het pad dat u opgeeft en komt rechtstreeks in het specifieke blad- en celnummer, extraheert de gegevens en plaatst het in de eigenschap Bijschrift van het label dat ik heb genoemd total_expenses . Het enige dat u in uw script moet bewerken, is het bestandspad en de labelnaam.
Test uw macro
Om uw opdrachtknop uit te testen, slaat u uw document op en onthoudt dat u opgeeft dat u een Word-document met ingeschakelde macro wilt zodat uw code werkt.
Hier is de VBA-macro in actie.
Integreer Excel-labels
Het moeilijkste aan het omgaan met labels in Word is dat het soms moeilijk is om het aan het einde van een zin of naast een andere tekst uit te lijnen.
Een manier om dat te overwinnen, is dat een deel van de tekst naast de gegevens in de VBA-code zelf wordt opgenomen. Zoals je hier kunt zien, heb ik de statische tekst direct in het Bijschrift geplaatst toen ik het label zelf maakte.
Nu hoeft u alleen die tekst toe te voegen wanneer u het label bijwerkt met uw VBA-script en de gegevens uit het Microsoft Excel-bestand toe te voegen aan het einde van die tekst.
Hier is hoe dat soort code eruit zou zien.
Dim objExcel als nieuw Excel.Application Dim exWb als Excel.Workbook Set exWb = objExcel.Workbooks.Open ("c: \ Users \ Brad \ Desktop \ expenses.xlsa") ThisDocument.total_expenses.Caption = exWb.Sheets ("Sheet1" ) .Cells (12, 2) ThisDocument.total_hotels.Caption = "Hotels:" & exWb.Sheets ("Sheet1"). Cells (5, 2) ThisDocument.total_dining.Caption = "Uit eten:" & exWb.Sheets ( "Blad1"). Cellen (2, 2) ThisDocument.total_tolls.Caption = "Tolls:" & exWb.Sheets ("Sheet1"). Cells (3, 2) ThisDocument.total_fuel.Caption = "Fuel:" & exWb. Spreadsheets ("Sheet1"). Cellen (10, 2) exWb.Sluiten Set exWb = Niets U kunt het tekenreeks-aaneenschakeling "&" -symbool gebruiken om de statische tekst te verbinden met de gegevens die zijn geëxtraheerd uit het Excel-blad. Dit is hoe de eindresultaten eruitzien in het bijgewerkte Word-document:
Dingen verder brengen
Als u uw capaciteiten wilt testen, waarom uw rapport niet automatiseren 5 Bronnen voor Excel-macro's om uw spreadsheets te automatiseren 5 Bronnen voor Excel-macro's om uw spreadsheets te automatiseren Zoeken naar Excel-macro's? Hier zijn vijf sites die hebben wat je zoekt. Meer lezen nog verder? U kunt die lelijke grijze opdrachtknop uit uw Microsoft Word-document verwijderen door het script voor de gegevensupdate gewoon op Document.Open () uit te voeren - het hele proces zal achter de schermen plaatsvinden.
Dit betekent dat u in veel gevallen het eerste document één keer kunt maken en daarna nooit opnieuw hoeft te maken. Het enige wat u hoeft te doen is het openen en alle labels worden automatisch bijgewerkt voor u met de gegevens uit het bijgewerkte Microsoft Excel-bestand. Klik gewoon op Afdrukken en verzend het rapport naar uw manager. Een taak van 30 minuten is zojuist omgezet in een afdruk van één minuut!
Kun je nog andere coole toepassingen bedenken voor deze data-integratietechniek met VBA? Deel een aantal van uw eigen ideeën en gedachten in de opmerkingen hieronder.
Beeldcredits: Punyaphat Larpsomboon via Shutterstock.com