Hvordan sette inn Excel-tabell i Word med VBA

Hvordan

AvRyan Dube

Sist oppdatert den27. april 2020

Visual Basic for Applications (VBA) er et veldig kraftig verktøy du kan bruke til å automatisere mye arbeid mellom flere Microsoft Office-applikasjoner. En vanlig aktivitet du kan automatisere ved hjelp av VBA er å sette inn en Excel-tabell i et Word-dokument.

Det er to måter du kan gjøre dette på. Den første er å automatisere en rett kopi og lim inn av et eksisterende område fra Excel til en ny tabell i et Word-dokument. Det andre er å utføre beregninger i Excel, lage en ny tabell i Word og skrive resultatene til tabellen.

Du kan prøve å gjøre det ta opp en makro for å gjøre dette, men makroer lar deg bare automatisere oppgaver i Word. I denne artikkelen lærer du hvordan du skriver VBA-kode for å automatisere disse handlingene mellom Excel og Word.

Kopier og lim inn et Excel-område i Word med VBA

I begge eksemplene starter vi med et eksempel på Excel-regnearket. Denne prøven er en liste over innkjøpsordrer for en rekke produkter.

La oss si at du vil kopiere og lime inn hele cellene i dette regnearket i et Word-dokument. For å gjøre dette, må du skrive en VBA-funksjon som kjører når du klikker på en "Copy To Word" -knapp.

Å velge Utvikler fra menyen og velg Sett inn fra kontrollgruppen i båndet. I rullegardinlisten velger du knappkontrollen under ActiveX-kontroller.

Deretter tegner du kommandoknappen på høyre side av arket. Du kan endre bildeteksten til “Kopier til Word” ved å høyreklikke på knappen og velge Eiendommer. Endre bildeteksttekst, og du kan bruke Font til å oppdatere skriftstørrelse og stil.

Merk: Hvis du ikke ser det Utvikler i Excel-menyen, legg deretter til den. Å velge Fil, alternativer, Tilpass bånd, og velg Alle kommandoer fra venstre rullegardin. Flytt deretter Utvikler fra venstre rute til høyre og velg OK for å fullføre.

Skriv kopien og lim inn VBA-koden

Nå er du klar til å begynne å skrive VBA-kode. Dobbeltklikk på det nye for å komme i gang Kopier til Word -knappen for å åpne vinduet med kodeditor.

Du bør se en subroutine som heter Commandbutton1_Click () som vist nedenfor.

Du vil kopiere hver del av koden nedenfor. Før du begynner å kode, for å kontrollere Word på datamaskinen din med VBA, må du aktivere Microsoft Word-referansebiblioteket.

I kodeditoren velger du Verktøy fra menyen og velg referanser. I listen over tilgjengelige referanser blar du ned og aktiverer Microsoft Word 16.0 Object Library.

Velg OK, så er du klar til å begynne å kode. Vi vil gå gjennom hver del av koden om gangen, slik at du forstår hva den koden gjør og hvorfor.

Først må du opprette variabler og objekter som holder rekkevidden og lar deg kontrollere Word-applikasjonen.

Dim tblRange Som Excel. Område
Dim WordApp som Word. applikasjon
Dim WordDoc som Word. Dokument
Dim WordTable Som Word. Bord

Den neste kodelinjen velger et bestemt område med celler og lagrer det i et Excel Range-objekt i VBA.

Set tblRange = ThisWorkbook. Regneark ( "Ark1") treet. ( "A2: G44")


Deretter vil du sjekke om Word-applikasjonen allerede er åpen på datamaskinen. Du kan referere til Word-applikasjonen ved å bruke en spesiell "klasse" -referanse med VBA GetObject-kommandoen for å oppnå dette. Hvis Word ikke allerede er åpnet, vil den neste linjen starte det ved hjelp av CreateObject-funksjonen. Linjen "On Error Resume Next" forhindrer at feil fra den første GetObject-funksjonen (hvis Word ikke allerede er åpen) stopper utførelsen av neste linje i programmet.

Ved feil Fortsett neste
Angi WordApp = GetObject (klasse: = "Word. Applikasjon")
Hvis WordApp ikke er noe, angi deretter WordApp = CreateObject (klasse: = "Word. Applikasjon")

Nå som Word-applikasjonen er lansert, vil du gjøre den synlig for brukeren og aktivere den for bruk.

WordApp. Synlig = sant
WordApp. Aktiver


Deretter vil du opprette et nytt dokument i Word-applikasjonen.

Angi WordDoc = WordApp. Dokumenter. Legge til

Til slutt vil du kopiere og lime inn cellene i en ny tabell i Word-dokumentet.

tblRange. Kopiere
WordDoc. Avsnitt (1). Ordne. PasteExcelTable _
LinkedToExcel: = Falske, _
WordFormatting: = Falske, _
RTF: = False


Bryterne i funksjonen over vil sette inn en ikke-lenket tabell ved hjelp av Excel-formatering (ikke Word-formatering) og ikke med rik tekstformat.

Til slutt, for å håndtere Excel-områder som er bredere enn dokumentet, må du autofitre den nye tabellen slik at den passer innenfor marginene til det nye Word-dokumentet.

Sett WordTable = WordDoc. Tabeller (1)
WordTable. AutoFitBehavior (wdAutoFitWindow)


Og nå er du ferdig! Lagre filen som en makroaktivert Excel-fil (.xlsm-utvidelse). Lukk redigeringsprogrammet, lagre den originale Excel-filen igjen, og klikk deretter kommandoknappen for å se koden din i aksjon!

Skriv Excel-resultater i et ordtabell med VBA

I dette neste avsnittet skriver du VBA-kode som utfører beregninger på verdier i Excel, og skriver dem til en tabell i Word.

For dette eksempelet vil vi trekke 10 rader med data, beregne og skrive resultatene til en tabell i et Word-dokument. Den originale tabellen vil også inneholde fire kolonner, og VBA-koden vil trekke de ti første radene med data fra det området.

Akkurat som i den siste delen, vil vi gå gjennom hver seksjon om gangen slik at du forstår hva den koden gjør og hvorfor.

Først lager du variablene og objektene som inneholder dataene og lar deg skrive til Word-applikasjonen.

Dim tblRange Som Excel. Område
Dim WrdRange Som Word. Område
Dim WordApp som Word. applikasjon
Dim WordDoc som Word. Dokument
Dim WordTable Som Word. Bord
Dempe intRows
Demp intColonner
Dim strDate Som streng
Dim strItem As String
Dim intUnits As Variant
Dim intCost Som Variant
Dim intTotal Som Variant

Deretter angir du de totale kolonnene og radene du vil lese fra Excel-området.


intNoOfRows = 10
intNoOfColonner = 5


Gjenta den samme koden som den siste delen som åpner Word hvis den ikke allerede er åpen.

Ved feil Fortsett neste
Angi WordApp = GetObject (klasse: = "Word. Applikasjon")
Hvis WordApp ikke er noe, angi deretter WordApp = CreateObject (klasse: = "Word. Applikasjon")
WordApp. Synlig = sant
WordApp. Aktiver
Angi WordDoc = WordApp. Dokumenter. Legge til


De neste fire linjene lager en tabell i det nyåpnede Word-dokumentet.

Angi WrdRange = WordDoc. Område (0, 0)
WordDoc. Tabeller. Legg til WrdRange, intNoOfRows, intNoOfColonner
Sett WordTable = WordDoc. Tabeller (1)
WordTable. Borders. Aktiver = sant


Til slutt vil følgende sløyfe utføre disse handlingene:

1. For hver rad legger du ordredato, element, enheter og kostnad i variabler
2. Beregn enhetstider kostnad (totalt salg) og lagre det i en variabel
3. For hver kolonne, skriv verdiene til Word-tabellen, inkludert det beregnede totale salget i den siste cellen
4. Gå videre til neste rad, og gjenta fremgangsmåten over

Slik ser koden ut:

For i = 1 til intNoOfRows
For j = 1 Til intNoOfColonner
Hvis j = 1 Da
strDate = tblRange. Celler (i + 1, j). Verdi
strItem = tblRange. Celler (i + 1, j + 1). Verdi
intUnits = Val (tblRange. Celler (i + 1, j + 2). Verdi)
intCost = Val (tblRange. Celler (i + 1, j + 3). Verdi)
intTotal = intUnits * intCost
Slutt om
Velg sak j
Sak er = 1
WordTable. Cell (i, j). Ordne. Tekst = strDate
Sak er = 2
WordTable. Cell (i, j). Ordne. Tekst = strItem
Sak er = 3
WordTable. Cell (i, j). Ordne. Tekst = intUnits
Sak er = 4
WordTable. Cell (i, j). Ordne. Tekst = intCost
Sak er = 5
WordTable. Cell (i, j). Ordne. Tekst = intTotal
Sak annet
Slutt Velg
neste
neste


"Cells" -funksjonen i den første delen trekker celleverdier ut av Excel. Celler (x, y) betyr at den trekker verdien til cellen i rad x og kolonne y.
"Cell" -funksjonen i den siste delen skriver til cellene i Word-tabellen ved å bruke de samme rad- og kolonnetildelingene.

Når du lagrer og kjører denne VBA-koden, vil du se resultatene i det nyopprettede Word-dokumentet.

Som du ser er det ikke for komplisert å lage noen nyttige automatiseringer mellom Excel og Word. Det er bare et spørsmål om å forstå hvordan de forskjellige “objektene” fungerer som kan lage og kontrollere både Excel- og Word-applikasjonene på datamaskinen din.