Automatyzacja pracy z Wordem — import danych adresowych z Excela

W dzisiejszym wpisie chciałbym pokazać w jaki sposób można pobierać i wstawiać do Worda dane zewnętrzne. Niniejszy przykład jest z pewnością zaledwie jednym z wielu możliwych sposobów na zrealizowanie takiego zdania. Ma on swoje ograniczenia (dobry do stosunkowo niewielkiej ilości danych). Zaznaczam również, że nie należy traktować moich wpisów na zasadzie sztywnej instrukcji obsługi VBA. Pragnę jedynie zademonstrować szerokie spektrum zastosowań tego niedocenianego składnika Office'a, a przy okazji podpowiedzieć jak się można zabrać za wykonanie pewnych rzeczy. Na każdym etapie podawanego przeze mnie rozwiązania, można zastosować jakiś inny sposób i dostosować całość do swoich potrzeb.

Przygotowanie dokumentu do importu danych

Na początek przygotuję w dokumencie Worda miejsce, do którego będę wstawiać dane. W tym celu, w miejscu gdzie mają się znajdować dane adresata, z zakładki Deweloper wstawiam formant zawartości. Ja użyłem list rozwijanych. Aby edytować poszczególne listy trzeba włączyć tryb projektowania na wstążce a następnie stojąc na wybranej liście nacisnąć ze wstążki właściwości. W zależności do potrzeb i typu importowanych danych można nadać tytuły poszczególnym listom. Ja swoje nazwałem: adresat, ulica, miejscowość. W dalszej części pokażę w jaki sposób manipulować zawartością tych pól z poziomu VBA,

Przygotowanie danych w Excelu

Po stronie Excela wprowadzam dane kilku przykładowych adresatów. Dla uproszczenia kodu VBA, każdą cząstkę danych, która ma zostać wstawiona do danej listy rozwijanej Worda, wpisuję w oddzielną kolumnę w Excelu. Zapisuję tak przygotowany skoroszyt.

Uzyskanie dostępu do danych ze skoroszytu

Zanim zaczniemy cokolwiek pisać w edytorze VBA musimy uzupełnić brakujące zależności. W tym celu z okna Tools edytora VBA wybieramy "References..." i tam zaznaczamy Microsoft Excel Library. Bez tej biblioteki nie będziemy w stanie kontrolować aplikacji Excela.

Teraz nasz model obiektowy wzbogacił się o klasy reprezentujące Excela i jego elementy. W kodzie zaczniemy od zdefiniowania tych elementów. Będzie to sama aplikacja Excela oraz skoroszyt i arkusz. Warto też zapisać sobie do zmiennej ścieżkę do skoroszytu z adresami.

Dim xlApp As New Excel.Application
Dim wb As Excel.Workbook
Dim sh1 As Excel.Worksheet
Dim wbPath As String

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
wbPath = "C:\Users\MojaFirma\Desktop\adresy.xlsx"
Set wb = xlApp.Workbooks.Open(wbPath)
Set sh1 = wb.Sheets(1)

Aplikacja Excela jest ustawiona jako niewidoczna. Oznacza to, że jeśli makro VBA będzie otwierać skoroszyt Excela, to nie zobaczymy tego (co najwyżej w procesach). Dzięki temu całość wykona się wielokrotnie szybciej. Zbudowanie interfejsu graficznego zajmuje masę czasu w porównaniu z samym otworzeniem dokumentu i odczytaniem z niego danych. Używany arkusz zdefiniowałem odwołując się do numeracji arkuszy w skoroszycie, ale równie dobrze można wskazywać arkusze za pomocą ich nazw.

Przygotowanie do iteracji na wierszach

Tworzymy kilka zmiennych pomocniczych, którymi posłużymy się przy odczycie danych ze skoroszytu:

LastRow - będzie przechowywać ilość niepustych wierszy w arkuszu - dzięki temu będziemy mieli pewność, że odczytujemy tylko te wiersze, w których są zapisane dane;

i - zmienna sterująca pętlą, której zadaniem będzie przeskakiwanie po wierszach arkusza;

Adresaci - tablica zawierające odczytane dane z kolumny adresatów;

Ulice - analogicznie tablica z odczytanymi ulicami;

Miejscowosci - odczytane miejscowosci;

Aby ustalić ilość niepustych wierszy użyjemy następującego kodu:

Dim LastRow As Long
Dim i As Integer
Dim Adresaci() As String
Dim Ulice() As String
Dim Miejscowosci() As String

LastRow = Range("A" & Rows.count).End(xlUp).Row
If (LastRow = 1) Then
   xlApp.Quit
   Set wb = Nothing
   Set xlApp = Nothing
   Exit Sub
End If

Przy okazji sprawdzamy ile jest tych wierszy. Jeśli tylko jeden, to oznacza, że poza wierszem nagłówka nie ma żadnych danych i dalsze wykonywanie instrukcji nie ma sensu. Zamykamy Excela, czyścimy zmienne, aby nie zajmowały niepotrzebnie pamięci i przerywamy dalsze działanie makra.

Określenie rozmiaru tablic dynamicznych

Tablice, w których będziemy zapisywać odczytane dane zostały zadeklarowane jako tablice dynamiczne. Oznacza to, że nie podaliśmy ich rozmiaru (ilości przechowywanych elementów). W VBA istnieje możliwość zmiany wielkości tablicy w trakcie wykonywania kodu. Ponieważ obecnie wiemy, ile wierszy odczytamy, możemy ustalić rozmiar naszych tablic. Jest to konieczne przed rozpoczęciem iteracji, ponieważ nie ma możliwości odnoszenia się do tablic, które nie mają zdefiniowanego rozmiaru.

ReDim Adresaci(1 To LastRow - 1) As String
ReDim Ulice(1 To LastRow - 1) As String
ReDim Miejscowosci(1 To LastRow - 1) As String

Rozmiar tablic jest mniejsza o 1 od ilości zapisanych wierszy, ponieważ pomijamy pierwszy wiersz, będący nagłówkiem. Oczywiście jeśli ktoś nie chce używać nagłówka, możemy nie odejmować tego jednego elementu.

Iteracja i odczyt danych

Naszą pętle ustawiamy tak, aby pierwszy odczytywany wiersz miał indeks = 2. Pomijamy w ten sposób pierwszy wiersz, będący nagłówkiem.

For i = 2 To LastRow
   Adresaci(i - 1) = sh1.Cells(i, 1).Value
   Ulice(i - 1) = sh1.Cells(i, 2).Value
   Miejscowosci(i - 1) = sh1.Cells(i, 3).Value
Next i

Obiekt Cells reprezentuje komórki arkusza. Tak naprawdę jest to dwuwymiarowa tablica komórek arkusza (tablica, której elementami są jednowymiarowe tablice takie, jak np. Adresaci). Aby wskazać konkretną komórkę skoroszytu podajemy kolejno numer wiersza i numer kolumny. W miejsce numeru wiersza wstawiamy zmienną sterującą pętli "i", która dla każdego przebiegu pętli ma wartość zwiększaną o 1, począwszy od 2, a skończywszy na wartości równej ilości niepustych wierszy w arkuszu.

Jeśli chcemy sprawdzić działanie naszej pętli, zanim przejdziemy do wstawiania danych do dokumentu Worda, możemy dopisać do pętli instrukcję wyświetlenia zawartości naszych tablic do immediate window (włączanego w edytorze VBA skrótem ALT+G).

Debug.Print (Adresaci(i - 1))
Debug.Print (Ulice(i - 1))
Debug.Print (Miejscowosci(i - 1))

Gdy już mamy wszystkie dane, możemy zamknąć Excela tym samym kodem, którego używaliśmy wcześniej. Przechodzimy teraz do sterowania listami rozwijanymi w Wordzie.

Przypisanie list rozwijanych do zmiennych VBA

Aby zapełnić danymi poszczególne listy, najpierw trzeba je sobie zdefiniować w kodzie VBA, aby zyskać do nich dostęp i kontrolę. Wszystkie formanty zawartości są w VBA reprezentowane przez obiekty klasy ContentControl. Zatem:

Dim PoleAdresata As ContentControl
Dim PoleUlicy As ContentControl
Dim PoleMiejscowosci As ContentControl

Jeśli nasz dokument zawiera tylko te 3 listy, to możemy się do nich dostać po indeksach (są one kolejno numerowane):

PoleAdresata = ActiveDocument.ContentControls.Item(1)
PoleUlicy = ActiveDocument.ContentControls.Item(2)
PoleMiejscowosci = ActiveDocument.ContentControls.Item(3)

Jeśli jednak korzystamy w dokumencie z innych elementów tego typu, to lepiej nie ryzykować pomieszania numeracji i zidentyfikować nasze listy po tytule, który wcześniej nadaliśmy. W tym celu wykonamy pętlę po wszystkich elementach typu ContentControl i dla każdego z osobna sprawdzimy jego tytuł. Jeśli natrafimy na element o pasującym tytule, przypiszemy go do naszej zmiennej. Jest to bardziej rozbudowana metoda, ale zyskujemy pewność, że nasze zmienne odnoszą się do właściwych elementów w dokumencie. Aby skonstruować pętlę for, musimy ustalić ilość elementów ContentControl w dokumencie. Najwygodniej jest zapisać tę informację do zmiennej.

Dim IlePol As Integer
IlePol = ActiveDocument.ContentControls.count

Teraz sprawdzamy wszystkie formanty:

Dim Tytul As String
For i = 1 To IlePol
   Tytul = ActiveDocument.ContentControls.Item(i).Title
   Select Case Tytul
      Case "Adresat"
         Set PoleAdresata = ActiveDocument.ContentControls.Item(i)
      Case "Ulica"
         Set PoleUlicy = ActiveDocument.ContentControls.Item(i)
      Case "Miejscowość"
         Set PoleMiejscowosci = ActiveDocument.ContentControls.Item(i)
   End Select
Next i

W każdym kroku pętli zmiennej tytuł przypisujemy tytuł kolejnego sprawdzanego elementu typu ContentControl. Następnie korzystamy z konstrukcji Select Case. Jest to odpowiednik instrukcji warunkowej If Then Else, która jest łatwiejsza w stosowaniu, jeśli mamy do czynienia z wieloma przypadkami do rozpatrzenia. Odpowiednikiem warunku, który sprawdzamy w konstrukcji If, jest to, co stoi po Select Case. W naszym przypadku jest to Tytul. Każdy Case poniżej wskazuje na możliwą wartość zmiennej Tytul, jaką może ona przyjąć oraz instrukcję jaka ma być wykonana, jeśli sprawdzana wartość zmiennej tytuł będzie się zgadzać z tą podaną w danym Case. Jeśli więc napotkamy na element ContentControl o tytule "Adresat" to wykonana zostanie instrukcja przypisania tego elementu do zmiennej Adresat. W ten sposób możemy wyłapać wszystkie nasze elementy po tytule, bez względu na to jaki mają swój "numerek" w zbiorze formantów zawartości (ContentControls).

"Zaludnianie" list rozwijanych

Wypełnianie danymi różnych elementów, np. listy rozwijanej jest zwane w VBA zaludnianiem. Warto znać to określenie, ponieważ są w VBA polecenia, które dosłownie tak brzmią. Mając już zmienne przechowujące odpowiednie listy możemy stworzyć pętlę, która wypełni nam te listy danymi. Pozostaje nam jeszcze tylko upewnić się, że listy nie przechowują jakichś starych danych. W tym celu należy je wyczyścić.

PoleAdresata.DropdownListEntries.Clear
PoleUlicy.DropdownListEntries.Clear
PoleMiejscowosci.DropdownListEntries.Clear

Wyczyszczone listy są gotowe do zapełnienia.

For i = 1 To UBound(Adresaci)
   PoleAdresata.DropdownListEntries.Add Adresaci(i)
   PoleUlicy.DropdownListEntries.Add Ulice(i)
   PoleMiejscowosci.DropdownListEntries.Add Miejscowosci(i)
Next i

Funkcja Ubound (Upper Bound) zwraca nam ilość elementów tablicy. Jako parametr przyjmuje tablicę, której ilość elementów ma policzyć. Dzięki temu możemy ustawić odpowiednią (zgodną z ilością odczytanych wierszy w Excelu) ilość kroków pętli.

Optymalizowanie kodu

W tym przykładzie celowo użyłem zmiennych, które są tak naprawdę zbędne, a wiele operacji można było wykonać w jednej pętli zamiast w kilku. Zrobiłem tak, aby łatwiej można było zrozumieć co się dzieje w poszczególnych miejscach i nie robić zbyt wiele na raz. Zapełnianie list można zrobić od razu w pętli odczytu danych z Excela bez konieczności tworzenia tablic, w których te dane są przechowywane. Poniżej zamieszczam cały kod makra, w skróconej wersji.

Sub ImportExcel()
'-----ZMIENNE DO EXCELA------------
Dim xlApp As New Excel.Application
Dim wb As Excel.Workbook
Dim sh1 As Excel.Worksheet
Dim wbPath As String
Dim LastRow As Long
Dim i As Integer
'-----ZMIENNE DO WORDA------------
Dim PoleAdresata As ContentControl
Dim PoleUlicy As ContentControl
Dim PoleMiejscowosci As ContentControl
Dim IlePol As Integer
Dim Tytul As String

'-----PRZYGOTOWANIE LIST DO ZAPELNIENIA----------------------

IlePol = ActiveDocument.ContentControls.count

For i = 1 To IlePol
   Tytul = ActiveDocument.ContentControls.Item(i).Title
   Select Case Tytul
      Case "Adresat"
         Set PoleAdresata = ActiveDocument.ContentControls.Item(i)
      Case "Ulica"
         Set PoleUlicy = ActiveDocument.ContentControls.Item(i)
      Case "Miejscowość"
         Set PoleMiejscowosci = ActiveDocument.ContentControls.Item(i)
   End Select
Next i

PoleAdresata.DropdownListEntries.Clear
PoleUlicy.DropdownListEntries.Clear
PoleMiejscowosci.DropdownListEntries.Clear

'-----OTWORZENIE SKOROSZYTU I SPRAWDZENIE ILOSCI WIERSZY---------

Set xlApp = CreateObject("Excel.Application")
wbPath = "C:\Users\Jarosław\Desktop\adresy.xlsx"
Set wb = xlApp.Workbooks.Open(wbPath)
xlApp.Visible = False
Set sh1 = wb.Sheets(1)

LastRow = Range("A" & Rows.count).End(xlUp).Row

If (LastRow = 1) Then
   xlApp.Quit
   Set wb = Nothing
   Set xlApp = Nothing
   Exit Sub
End If

'-----ODCZYT DANYCH I ZAPIS DO LIST W WORDZIE-------------

ReDim Adresaci(1 To LastRow - 1) As String
ReDim Ulice(1 To LastRow - 1) As String
ReDim Miejscowosci(1 To LastRow - 1) As String

For i = 2 To LastRow
   PoleAdresata.DropdownListEntries.Add sh1.Cells(i, 1).Value
   PoleUlicy.DropdownListEntries.Add sh1.Cells(i, 2).Value
   PoleMiejscowosci.DropdownListEntries.Add sh1.Cells(i, 3).Value
Next i

xlApp.Quit
Set wb = Nothing
Set xlApp = Nothing
   
End Sub

Jak widać na zrzucie ekranu poniżej, w treści dokumentu nie wyświetlają się zaimportowane dane. Są one widoczne dopiero po rozwinięciu listy. Dzieje się tak dlatego, że domyślnie ustawiony jest indeks 0, a więc żaden element listy nie jest wybrany.

Można to zmienić i ustawić np. pierwszy element w następujący sposób:

PoleAdresata.DropdownListEntries.Item(1).Select

Podsumowanie

W niniejszym wpisie pokazałem na przykładzie jak można pobierać dane z Excela i wstawiać je do Worda. Każdy może poeksperymentować i zmodyfikować podane przeze mnie rozwiązanie. Nie ma konieczności stosowania rozwijanych list. Są one jednak wygodne, ponieważ w łatwy sposób możemy zdefiniować je w kodzie VBA i wstawić dane dokładnie w to miejsce w tekście, które nas interesuje. Na wydruku listy rozwijane z wybranymi wartościami wyglądają jak zwykły tekst.

Bazę adresów w Excelu możemy na bieżąco aktualizować. Jeśli nasze makro wstawimy do makra AutoOpen lub Document_Open to nasze listy będą się aktualizować przy każdym otworzeniu dokumentu. Adresy możemy posortować alfabetycznie. Najprościej w samym Excelu, po dodaniu nowych wpisów, choć można to zrobić także z poziomu VBA.