Hoe Excel-tabel in Word invoegen met VBA

Hoe

DoorRyan Dube

Laatst geupdate op27 april 2020

Visual Basic for Applications (VBA) is een zeer krachtige tool die u kunt gebruiken om veel werk tussen meerdere Microsoft Office-applicaties te automatiseren. Een veel voorkomende activiteit die u kunt automatiseren met VBA, is het invoegen van een Excel-tabel in een Word-document.

U kunt dit op twee manieren doen. De eerste is het automatiseren van het rechtstreeks kopiëren en plakken van een bestaand bereik vanuit Excel in een nieuwe tabel in een Word-document. De tweede is het uitvoeren van berekeningen in Excel, het maken van een nieuwe tabel in Word en het schrijven van de resultaten naar de tabel.

Je zou kunnen proberen een macro opnemen om dit te doen, maar met macro's kunt u alleen taken binnen Word automatiseren. In dit artikel leert u hoe u VBA-code schrijft om deze acties tussen Excel en Word te automatiseren.

Kopieer en plak een Excel-bereik in Word met VBA

In beide voorbeelden beginnen we met een voorbeeld van een Excel-spreadsheet. Dit voorbeeld is een lijst met inkooporders voor verschillende producten.

Stel dat u het hele cellenbereik in dit werkblad wilt kopiëren en in een Word-document wilt plakken. Om dit te doen, moet u een VBA-functie schrijven die wordt uitgevoerd wanneer u op de knop 'Kopiëren naar Word' klikt.

Selecteer Ontwikkelaar uit het menu en selecteer Invoegen van de groep Controls in het lint. Selecteer in de vervolgkeuzelijst het knopbesturingselement onder ActiveX-besturingselementen.

Teken vervolgens de opdrachtknop aan de rechterkant van het blad. U kunt het bijschrift wijzigen in "Kopiëren naar Word" door met de rechtermuisknop op de knop te klikken en te selecteren Eigendommen. Wijzig de bijschrifttekst en u kunt Lettertype gebruiken om de tekengrootte en -stijl bij te werken.

Notitie: Als je het niet ziet Ontwikkelaar in uw Excel-menu en voeg het dan toe. Selecteer het dossier, Opties, Lint aanpassenen selecteer Alle opdrachten vanaf de linker drop-down. Beweeg dan Ontwikkelaar in het linkerdeelvenster naar rechts en selecteer OK om te voltooien.

Schrijf de VBA-code voor kopiëren en plakken

Nu ben je klaar om VBA-code te schrijven. Dubbelklik op het nieuwe om te beginnen Kopieer naar Word knop om het venster van de code-editor te openen.

Je zou een subroutine moeten zien genaamd Commandbutton1_Click () zoals hieronder getoond.

U wilt elke sectie van onderstaande code kopiëren. Voordat u begint met coderen, moet u de referentiebibliotheek van Microsoft Word inschakelen om Word op uw computer met VBA te kunnen bedienen.

Selecteer in de code-editor Hulpmiddelen uit het menu en selecteer Referenties. Scrol in de lijst met beschikbare referenties naar beneden en schakel in Objectbibliotheek van Microsoft Word 16.0.

Selecteer OK en je bent klaar om te beginnen met coderen. We zullen elke sectie van de code tegelijk doornemen, zodat u begrijpt wat die code doet en waarom.

Eerst moet u de variabelen en objecten maken die het bereik bevatten en waarmee u de Word-toepassing kunt besturen.

Dim tblRange als Excel. Bereik
Dim WordApp als Word. Toepassing
Dim WordDoc als Word. Document
Dim WordTable As Word. Tafel

De volgende coderegel selecteert een specifiek cellenbereik en slaat het op in een Excel Range-object in VBA.

Stel tblRange = ThisWorkbook in. Werkbladen ("Sheet1"). Bereik ("A2: G44")


Vervolgens wilt u controleren of de Word-toepassing al op de computer is geopend. U kunt naar de Word-toepassing verwijzen met behulp van een speciale "klasse" -referentie met de VBA GetObject-opdracht om dit te bereiken. Als Word nog niet is geopend, wordt de volgende regel gestart met de functie CreateObject. De regel 'Bij fout hervatten volgende' voorkomt dat een fout van de eerste GetObject-functie (als Word nog niet is geopend) de uitvoering van de volgende regel in het programma stopt.

Bij fout hervatten volgende
Stel WordApp = GetObject (class: = "Word in. Toepassing")
Als WordApp niets is, stel dan WordApp = CreateObject (class: = "Word in. Toepassing")

Nu de Word-toepassing is gestart, wilt u deze zichtbaar maken voor de gebruiker en activeren voor gebruik.

WordApp. Zichtbaar = waar
WordApp. Activeren


Vervolgens wilt u een nieuw document maken in de Word-applicatie.

Stel WordDoc = WordApp in. Documenten. Toevoegen

Ten slotte kopieer en plak je het cellenbereik in een nieuwe tabel in het Word-document.

tblRange. Kopiëren
WordDoc. Paragrafen (1) Bereik. PasteExcelTable _
LinkedToExcel: = False, _
WordFormatting: = False, _
RTF: = False


De schakelaars in de bovenstaande functie voegen een niet-gekoppelde tabel in met behulp van bron-Excel-opmaak (niet Word-opmaak) en zonder gebruik te maken van rich text-opmaak.

Om tot slot Excel-bereiken te behandelen die breder zijn dan het document, moet u de nieuwe tabel automatisch aanpassen zodat deze binnen de marges van uw nieuwe Word-document past.

Stel WordTable = WordDoc in. Tafels (1)
WordTable. AutoFitBehavior (wdAutoFitWindow)


En nu ben je klaar! Sla het bestand op als een macro-ingeschakeld Excel-bestand (.xlsm-extensie). Sluit de editor, sla het originele Excel-bestand opnieuw op en klik vervolgens op uw opdrachtknop om uw code in actie te zien!

Schrijf Excel-resultaten in een woordtabel met VBA

In deze volgende sectie schrijft u VBA-code die berekeningen uitvoert op waarden in Excel en deze schrijft naar een tafel in Word.

Voor dit voorbeeld trekken we 10 rijen aan gegevens, berekenen we en schrijven we de resultaten naar een tabel in een Word-document. De oorspronkelijke tabel bevat ook vier kolommen en de VBA-code haalt de eerste tien rijen met gegevens uit dat bereik.

Net als in de laatste sectie, zullen we elke sectie tegelijk doornemen, zodat u begrijpt wat die code doet en waarom.

Maak eerst de variabelen en objecten die de gegevens bevatten, zodat u naar de Word-toepassing kunt schrijven.

Dim tblRange als Excel. Bereik
Dim WrdRange als Word. Bereik
Dim WordApp als Word. Toepassing
Dim WordDoc als Word. Document
Dim WordTable As Word. Tafel
Dim intRows
Dim intColumns
Dim strDate As String
Dim strItem As String
Dim intUnits als variant
Dim intCost als variant
Dim intTotal als variant

Stel vervolgens het totale aantal kolommen en rijen in dat u uit het Excel-bereik wilt lezen.


intNoOfRows = 10
intNoOfColumns = 5


Herhaal dezelfde code als de laatste sectie die Word opent als deze nog niet is geopend.

Bij fout hervatten volgende
Stel WordApp = GetObject (class: = "Word in. Toepassing")
Als WordApp niets is, stel dan WordApp = CreateObject (class: = "Word in. Toepassing")
WordApp. Zichtbaar = waar
WordApp. Activeren
Stel WordDoc = WordApp in. Documenten. Toevoegen


De volgende vier regels maken een tabel binnen dat nieuw geopende Word-document.

Stel WrdRange = WordDoc in. Bereik (0, 0)
WordDoc. Tabellen. Voeg WrdRange, intNoOfRows, intNoOfColumns toe
Stel WordTable = WordDoc in. Tafels (1)
WordTable. Grenzen. Inschakelen = waar


Ten slotte voert de volgende lus deze acties uit:

1. Zet voor elke rij de orderdatum, artikel, eenheden en kosten in variabelen
2. Bereken de kosten per eenheid (totale verkoop) en sla die op in een variabele
3. Schrijf voor elke kolom de waarden naar de Word-tabel, inclusief de berekende totale verkoop in de laatste cel
4. Ga verder met de volgende rij en herhaal de bovenstaande procedure

Zo ziet die code eruit:

Voor i = 1 Naar intNoOfRows
Voor j = 1 Naar intNoOfColumns
Als j = 1 Dan
strDate = tblRange. Cellen (i + 1, j). Waarde
strItem = tblRange. Cellen (i + 1, j + 1). Waarde
intUnits = Val (tblRange. Cellen (i + 1, j + 2). Waarde)
intCost = Val (tblRange. Cellen (i + 1, j + 3). Waarde)
intTotal = intUnits * intCost
Stop als
Selecteer Case j
Geval is = 1
WordTable. Cel (i, j) Bereik. Tekst = strDate
Geval is = 2
WordTable. Cel (i, j) Bereik. Tekst = strItem
Geval is = 3
WordTable. Cel (i, j) Bereik. Tekst = intUnits
Geval is = 4
WordTable. Cel (i, j) Bereik. Tekst = intCost
Geval is = 5
WordTable. Cel (i, j) Bereik. Tekst = intTotaal
Zaak anders
Einde selecteren
De volgende
De volgende


De "Cells" -functie in het eerste deel trekt de celwaarden uit Excel. Cellen (x, y) betekent dat het de waarde van de cel in rij x en kolom y ophaalt.
De functie "Cel" in het laatste deel schrijft naar de cellen in de Word-tabel met dezelfde rij- en kolomtoewijzingen.

Zodra u deze VBA-code hebt opgeslagen en uitgevoerd, ziet u de resultaten in uw nieuw gemaakte Word-document.

Zoals u kunt zien, is het niet al te ingewikkeld om een ​​nuttige automatisering tussen Excel en Word te creëren. Het is gewoon een kwestie van begrijpen hoe de verschillende 'objecten' werken die zowel de Excel- als de Word-toepassingen op uw computer kunnen maken en beheren.