Jak wstawić tabelę Excel do słowa za pomocą VBA

Jak

PrzezRyan Dube

Ostatnia aktualizacja dnia27 kwietnia 2020 r

Visual Basic for Applications (VBA) to bardzo potężne narzędzie, którego można użyć do automatyzacji dużej ilości pracy między wieloma aplikacjami Microsoft Office. Jednym z typowych działań, które można zautomatyzować za pomocą VBA, jest wstawienie tabeli Excel do dokumentu Word.

Można to zrobić na dwa sposoby. Pierwszym z nich jest automatyzacja prostej kopii i wklejania istniejącego zakresu z Excela do nowej tabeli w dokumencie Word. Drugim jest wykonywanie obliczeń w programie Excel, tworzenie nowej tabeli w programie Word i zapisywanie wyników w tabeli.

Możesz spróbować nagrać makro aby to zrobić, ale makra pozwolą ci tylko zautomatyzować zadania w programie Word. W tym artykule dowiesz się, jak napisać kod VBA, aby zautomatyzować te akcje między Excelem a Wordem.

Skopiuj i wklej zakres Excela do słowa za pomocą VBA

W obu przykładach zaczniemy od przykładowego arkusza kalkulacyjnego Excel. Ta próbka to lista zamówień zakupu różnych produktów.

Załóżmy, że chcesz skopiować i wkleić cały zakres komórek w tym arkuszu do dokumentu programu Word. Aby to zrobić, musisz napisać funkcję VBA, która uruchomi się po kliknięciu przycisku „Kopiuj do słowa”.

Wybierz Deweloper z menu i wybierz Wstawić z grupy Kontrolki na wstążce. Z rozwijanej listy wybierz Kontrolkę przycisku w Kontrolki ActiveX.

Następnie narysuj przycisk polecenia po prawej stronie arkusza. Możesz zmienić podpis na „Kopiuj do Worda”, klikając prawym przyciskiem myszy przycisk i wybierając Nieruchomości. Zmień tekst podpisu, a możesz użyć Czcionki, aby zaktualizować rozmiar i styl czcionki.

Uwaga: Jeśli nie widzisz Deweloper w menu Excela, a następnie dodaj go. Wybierz Plik, Opcje, Dostosuj wstążkęi wybierz Wszystkie polecenia z lewej listy rozwijanej. Następnie rusz się Deweloper z lewego panelu po prawej i wybierz OK, aby zakończyć.

Napisz Skopiuj i Wklej Kod VBA

Teraz możesz zacząć pisać kod VBA. Aby rozpocząć, kliknij dwukrotnie nowy Skopiuj do Worda przycisk, aby otworzyć okno edytora kodu.

Powinieneś zobaczyć podprogram o nazwie Commandbutton1_Click (), jak pokazano poniżej.

Musisz skopiować każdą sekcję kodu poniżej. Przed rozpoczęciem kodowania, aby kontrolować Word na komputerze za pomocą VBA, musisz włączyć bibliotekę referencyjną Microsoft Word.

W edytorze kodu wybierz Przybory z menu i wybierz Bibliografia. Na liście dostępnych referencji przewiń w dół i włącz Biblioteka obiektów Microsoft Word 16.0.

Wybierz OK i możesz rozpocząć kodowanie. Przejrzymy każdą sekcję kodu na raz, abyś zrozumiał, co robi ten kod i dlaczego.

Najpierw musisz utworzyć zmienne i obiekty, które będą zawierały zakres i umożliwią sterowanie aplikacją Word.

Dim tblRange As Excel. Zasięg
Dim WordApp As Word. Podanie
Dim WordDoc As Word. Dokument
Dim WordTable As Word. Stół

Następny wiersz kodu wybiera określony zakres komórek i zapisuje go w obiekcie Excel Range w VBA.

Ustaw tblRange = ThisWorkbook. Arkusze robocze („Arkusz 1”). Zakres („A2: G44”)


Następnie chcesz sprawdzić, czy aplikacja Word jest już otwarta na komputerze. Możesz odwołać się do aplikacji Word za pomocą specjalnego odwołania do „klasy” za pomocą komendy VBA GetObject, aby to osiągnąć. Jeśli program Word nie jest jeszcze otwarty, następny wiersz uruchomi go za pomocą funkcji CreateObject. Wiersz „Po błędzie Wznów następny” zapobiega wszelkim błędom z pierwszej funkcji GetObject (jeśli program Word nie jest jeszcze otwarty) przed zatrzymaniem wykonywania następnego wiersza w programie.

Po błędzie Wznów dalej
Ustaw WordApp = GetObject (class: = "Word. Podanie")
Jeśli WordApp jest niczym, ustaw WordApp = CreateObject (class: = "Word. Podanie")

Teraz, gdy aplikacja Word jest uruchomiona, chcesz ją wyświetlić dla użytkownika i aktywować do użytku.

WordApp. Widoczny = Prawda
WordApp. Aktywuj


Następnie chcesz utworzyć nowy dokument w aplikacji Word.

Ustaw WordDoc = WordApp. Dokumenty Dodaj

Na koniec skopiujesz i wkleisz zakres komórek do nowej tabeli w dokumencie Word.

tblRange. Kopiuj
WordDoc. Akapity (1). PasteExcelTable _
LinkedToExcel: = False, _
WordFormatting: = False, _
RTF: = Fałsz


Przełączniki w powyższej funkcji wstawią niepowiązaną tabelę przy użyciu źródłowego formatowania Excela (bez formatowania Word) i bez użycia tekstu sformatowanego.

Wreszcie, aby poradzić sobie z zakresami Excela szerszymi niż dokument, musisz automatycznie dopasować nową tabelę, aby mieściła się w marginesach nowego dokumentu Word.

Ustaw WordTable = WordDoc. Stoły (1)
WordTable. AutoFitBehavior (wdAutoFitWindow)


A teraz gotowe! Zapisz plik jako plik Excel z obsługą makr (rozszerzenie .xlsm). Zamknij edytor, ponownie zapisz oryginalny plik Excel, a następnie kliknij przycisk polecenia, aby zobaczyć kod w akcji!

Zapisz wyniki Excela w tabeli słów za pomocą VBA

W następnej sekcji napiszesz kod VBA, który wykonuje obliczenia wartości w Excelu i zapisuje je w tabela w programie Word.

W tym przykładzie pobieramy dane o wartości 10 wierszy, obliczamy i zapisujemy wyniki do tabeli w dokumencie Word. Oryginalna tabela będzie również zawierać cztery kolumny, a kod VBA wyciągnie pierwsze dziesięć wierszy danych z tego zakresu.

Podobnie jak w ostatniej sekcji, przejrzymy każdą sekcję na raz, abyś zrozumiał, co robi ten kod i dlaczego.

Najpierw utwórz zmienne i obiekty, które będą przechowywać dane i umożliwią pisanie w aplikacji Word.

Dim tblRange As Excel. Zasięg
Dim WrdRange As Word. Zasięg
Dim WordApp As Word. Podanie
Dim WordDoc As Word. Dokument
Dim WordTable As Word. Stół
Dim intRows
Dim intColumns
Dim strDate As String
Dim strItem As String
Dim intUnits As Variant
Dim intCost As Variant
Dim intTotal As Variant

Następnie ustaw całkowitą liczbę kolumn i wierszy, które chcesz odczytać z zakresu Excela.


intNoOfRows = 10
intNoOfColumns = 5


Powtórz ten sam kod co ostatnia sekcja, która otworzy program Word, jeśli jeszcze nie jest otwarty.

Po błędzie Wznów dalej
Ustaw WordApp = GetObject (class: = "Word. Podanie")
Jeśli WordApp jest niczym, ustaw WordApp = CreateObject (class: = "Word. Podanie")
WordApp. Widoczny = Prawda
WordApp. Aktywuj
Ustaw WordDoc = WordApp. Dokumenty Dodaj


Następne cztery wiersze tworzą tabelę w tym nowo otwartym dokumencie Word.

Ustaw WrdRange = WordDoc. Zakres (0, 0)
WordDoc. Stoły Dodaj WrdRange, intNoOfRows, intNoOfColumns
Ustaw WordTable = WordDoc. Stoły (1)
WordTable. Granice Włącz = Prawda


Wreszcie następująca pętla wykona następujące działania:

1. Dla każdego wiersza umieść datę zamówienia, pozycję, jednostki i koszt w zmiennych
2. Oblicz koszt jednostkowy razy (całkowita sprzedaż) i zapisz to w zmiennej
3. Dla każdej kolumny zapisz wartości do tabeli Word, w tym obliczoną całkowitą sprzedaż w ostatniej komórce
4. Przejdź do następnego rzędu i powtórz powyższą procedurę

Oto jak wygląda ten kod:

For i = 1 To intNoOfRows
Dla j = 1 do intNoOfColumns
Jeśli j = 1 to
strDate = tblRange. Komórki (i + 1, j). Wartość
strItem = tblRange. Komórki (i + 1, j + 1). Wartość
intUnits = Val (tblRange. Komórki (i + 1, j + 2). Wartość)
intCost = Val (tblRange. Komórki (i + 1, j + 3). Wartość)
intTotal = intUnits * intCost
End If
Wybierz Case j
Przypadek jest = 1
WordTable. Komórka (i, j). Text = strDate
Przypadek jest = 2
WordTable. Komórka (i, j). Text = strItem
Przypadek jest = 3
WordTable. Komórka (i, j). Text = intUnits
Przypadek jest = 4
WordTable. Komórka (i, j). Text = intCost
Przypadek jest = 5
WordTable. Komórka (i, j). Text = intTotal
Case Else
End Wybierz
Kolejny
Kolejny


Funkcja „Komórki” w pierwszej części ściąga wartości komórek z Excela. Komórki (x, y) oznaczają, że pobiera wartość komórki z wiersza x i kolumny y.
Funkcja „Komórka” w ostatniej części zapisuje do komórek w tabeli Word, używając tego samego przypisania wiersza i kolumny.

Po zapisaniu i uruchomieniu tego kodu VBA zobaczysz wyniki w nowo utworzonym dokumencie Word.

Jak widać, nie jest zbyt skomplikowane stworzenie użytecznej automatyzacji między Excelem a Wordem. To tylko kwestia zrozumienia, jak działają różne „obiekty”, które mogą tworzyć i kontrolować zarówno aplikacje Excel, jak i Word na komputerze.