excelpogodzinach-logo
Jak stworzyć listę arkuszy Excela w Wordzie? - Excel Po Godzinach

Jak stworzyć listę arkuszy Excela w Wordzie?

Wyobraź sobie, że posiadasz skoroszyt zawierający kilkanaście lub kilkadziesiąt arkuszy, w których zgromadziłeś ważne dla Ciebie informacje. Teraz potrzebujesz zrobić listę wszystkich arkuszy i przekopiować je do edytora tekstów Word, ale chcesz to zrobić sprytnie, szybko i bezbłędnie. Tylko jak się za to zabrać? Właśnie z takim pytaniem zgłosił się do mnie Zbigniew, jeden z Czytelników bloga. Rozwiązanie publikuję poniżej.

Przekopiowanie, a co gorsza przepisanie nazw poszczególnych arkuszy nie jest czymś bardzo trudnym i pracochłonnym pod warunkiem, że tych arkuszy nie jest zbyt wiele (dosłownie kilka). Co innego w przypadku, kiedy tych arkuszy jest naprawę sporo, a u Zbigniewa jest ich około 200!

Być może ciśnie Ci się na usta odpowiedź: automatyzacja! Dokładnie tak! W przypadku tego zadania trzeba sięgnąć po makro VBA (Visual Basic for Applications), które z tym wyzwaniem poradzi sobie wyśmienicie.

Kurs online: TABELE PRZESTAWNE dla spragnionych sukcesu!

Czy wiesz, że przygotowuję dla Ciebie kurs online, w którym rozkładam na czynniki pierwsze tematykę tabel przestawnych? Kurs będzie dostępny lada chwila, a jeśli chcesz skorzystać ze specjalnej oferty przewidzianej na pierwszy dzień sprzedaży, to nie zastanawiaj się dłużej, tylko zapisz się na listę osób oczekujących.

Pamiętaj, że tylko obecność na tej liście gwarantuje Ci dostęp do informacji o rozpoczęciu sprzedaży i specjalnym kodzie rabatowym aktywnym w pierwszym dniu sprzedaży.

Makro na kłopoty

Zanim makro, czyli nasza procedura, zostanie napisane warto zastanowić się w jaki sposób będzie działało, jakie operacje muszą zostać wykonane.

KROK 1: Makro tworzy nowy dokument w Wordzie.
KROK 2: Oblicza ile w bieżącym skoroszycie znajduje się arkuszy.
KROK 3: Odczytuje nazwę każdego arkusza, a następnie umieszcza ją w osobnym wierszu dokumentu w Wordzie.

Oczywiście można dodać jeszcze jeden krok, a mianowicie zapisanie dokumentu Word na dysku. Z wiadomości Zbigniewa wynika, że nie jest to ostatni etap pracy w Wordzie z pobranymi nazwami arkuszy, dlatego to Tobie jako użytkownikowi pozostawiam decyzję czy będziesz plik zapisywał czy nie.

Skoro już wiesz jakie zadania stoją przed makrem, można przystąpić do jego napisania.

Zanim przejdziesz do tworzenia procedury sprawdź czy na wstążce Excela wyświetla się karta Deweloper. Jeśli nie, to włącz ją teraz.

Aby włączyć (aktywować) kartę Deweloper wykonaj następujące czynności:
KROK 1: Kliknij na kartę Plik, a następnie wybierz Opcje.
KROK 2: W oknie dialogowym Opcje programu Excel wybierz Dostosowywanie Wstążki.
KROK 3: Po prawej stronie zaznacz kartę Deweloper i kliknij przycisk OK.
 
Lista arkuszy w Wordzie - Excel Po Godzinach

Świetnie, karta jest już widoczna. :)

Teraz wybierz kartę Deweloper i w grupie Kod kliknij Visual Basic (możesz też użyć skrótu klawiaturowego Alt + F11). Okno edytora Visual Basic for Applications pojawi się na ekranie monitora.

W oknie Project, które zawiera informację o wszystkich otwartych skoroszytach, wybierz ten, w którym chcesz umieścić makro.

Następnie prawym przyciskiem myszy wywołaj menu, w którym należy wskazać opcję Insert, a potem Insert Module.
 
Lista arkuszy w Wordzie - Excel Po Godzinach
Moduł Module1, w którym docelowo znajdzie się kod naszego makra, zostanie utowrzony. Jednocześnie po prawej stronie aktywowane zostanie okno Kod, w którym wprowadzisz zawartość poniższej procedury:

Option Explicit

Sub ArkuszeZExcelaDoWorda()
'Definicja zmiennych
Dim obiektWord As Object
Dim obiektDokument As Object
Dim obiektZaznaczenie As Object
Dim i As Integer

'Przypisanie wartości do zmiennych
Set obiektWord = CreateObject("Word.Application")
Set obiektDokument = obiektWord.Documents.Add
Set obiektZaznaczenie = obiektWord.Selection
obiektWord.Visible = True

'Przenoszenie nazw arkuszy do Worda
For i = 1 To Sheets.Count Step 1
obiektZaznaczenie.TypeText Sheets(i).Name & vbNewLine
Next i
End Sub

Jak widzisz nie jest ona specjalnie długa. Raptem kilka linijek kodu, które „czynią cuda”. 😉

Od ogółu do szczegółu

Makro przepisane? W takim razie przeanalizujmy pokrótce jego zawartość, abyś miał świadomość co się tak naprawdę dzieje w trakcie jego wykonywania.
 
Lista arkuszy w Wordzie - Excel Po Godzinach

Instrukcja Option Explicit wymusza zadeklarowanie wszystkich zmiennych za pomocą instrukcji Dim. Jeśli jakakolwiek zmienna nie zostanie zadeklarowana, ale będzie użyta w procedurze, to pojawi się błąd w czasie kompilacji makra. Pamiętaj, aby instrukcję Option Explicit wstawiać zawsze na początku kodu przed pierwszą procedurą lub funkcją.

Całość, która powoduje zliczenie arkuszy, pobranie ich nazw i wprowadzenie do Worda znajduje się w obrębie procedury ArkuszeZExcelaDoWorda, która zaczyna się od instrukcji Sub nazwa_makra() a kończy instrukcją End Sub.

Na początku makra deklarowane są cztery zmienne za pomocą instrukcji Dim, trzy z nich (obiektWord, obiektDokument, obiektZaznaczenie) jako obiekt, a ostatnia i jako zmienna liczbowa.

W kolejnej sekcji następuje przypisanie wartości do zmiennych i tak: obiektWord otrzymuje dostęp do edytora tekstów Word, obiektDokument stanowi nowy dokument w Wordzie, natomiast obiektZaznaczenie reprezentuje miejsce wstawiania treści jeśli nic nie jest zaznaczone w dokumencie. Znajduje się tam jeszcze właściwość object.Visible, która jeśli ma wartość True powoduje wyświetlenie na ekranie dokumentu w Wordzie. W przypadku wartości False, dokument nie będzie widoczny.

W ostatniej sekcji stworzona jest pętla oparta na instrukcjach For i Next, która pozwala przetwarzać serię danych. W naszym przypadku dla każdego arkusza w skoroszycie jego nazwa jest wstawiana do dokumentu Worda, a po niej następuje przejście do nowego wiersza.

Zwróć uwagę, że przy instrukcji For określony został zakres wierszy od 1 (skoroszyt zawsze musi posiadać przynajmniej jeden arkusz) do liczby wszystkich dostępnych arkuszy obliczonej za pomocą polecenia Sheets.Count. Parametr Step 1 określa o ile ma zwiększyć się wartość zmiennej i. W tym przypadku wzrasta ona o 1 po wykonaniu każdej pętli. Kiedy dla ostatniej wartości z zakresu zostanie wykonana operacja pobrania nazwy arkusza i wstawienia jej do Worda, pętla zostaje zakończona, a program kończy swoje działanie (instrukcja End Sub).

Tak skonstruowane makro generuje w Wordzie gotową listę wszystkich arkuszy zawartych w skoroszycie.

Jak uruchomić makro VBA?

Po wpisaniu makra za pośrednictwem edytora VB powinieneś zapisać skoroszyt, żeby nie musieć wpisywać go za każdym razem. Pamiętaj tylko, że skoroszyty zawierające makra muszą być zapisywane jako skoroszyty programu Excel z obsługą makr.

Samo wywołanie makra możesz wykonać na różne sposoby. Najłatwiej jednak będzie użyć skrótu klawiaturowego Alt + F8 w celu wywołania okna dialogowego Marka zawierającego listę dostępnych makr. To samo okno możesz włączyć klikając na kartę Deweloper, a następnie w grupie Kod wybierając polecenie Makra.
 
Lista arkuszy w Wordzie - Excel Po Godzinach
Lista arkuszy w Wordzie - Excel Po Godzinach
Teraz wystarczy wybrać nazwę makra i kliknąć przycisk Uruchom, aby procedura została wykonana i gotowe!
 
Lista arkuszy w Wordzie - Excel Po Godzinach

Mam nadzieję, że spodobała Ci się ta porada. Jeśli tak, to zostaw komentarz pod artykułem i przekaż informację o nim swoim znajomym lub kolegom z pracy.

Podobał Ci się ten artykuł?

Jeśli tak, to dołącz do grona Czytelników bloga Excel Po Godzinach, którzy każdego tygodnia otrzymują sprawdzone porady, wskazówki oraz rozwiązania nietypowych problemów.

8 myśli na temat “Jak stworzyć listę arkuszy Excela w Wordzie?

  1. Fajny artykuł, fajna porada. Dla mnie jednak jest to jeszcze jednak trochę za trudne – dopiero zaczynam poznawać VBA i pojęcia typu zmienna, pętla to dla mnie jeszcze czarna magia. Czy możesz Michał napisać krótki artykuł o VBA z podstawowymi pojęciami? Np co co jest integer, pętla, zmienna? Bo dla osoby, która nie ma pojęcia o VBA obawiam się, że powyższy tekst będzie za trudny do zrozumienia.
    Generalnie jednak opisany przypadek bardzo ciekawy i przyznaję, że nie wiedziałem, że tak się da zrobić.

    1. Witaj Rafale,

      Dzięki za komentarz. W swoich artykułach staram się możliwie jak najdokładniej i jak najprościej wyjaśniać poszczególne zagadnienia. Na pewno temat makr i samego VBA do prostych nie należą, ale nie należy się zrażać początkami, które w przypadku nauki czegokolwiek chyba zawsze są trudne. :)

      Artykuł wprowadzający do VBA? Jasne! Dodałem do długiej listy propozycji Czytelników bloga. Inną propozycją może być zorganizowanie kolejnego webinaru tym razem z podstaw VBA. Jak będzie więcej chętnych, to przygotuję go.

      Rafale, chciałbym poznać powody, dla których zainteresowałeś się makrami i VBA. Do pracy? Na studia? Jaki jest Twój cel jeśli chodzi o naukę VBA?

      Pozdrawiam,

  2. Michał, dzięki za odpowiedź. Moje zainteresowanie makrami i VBA oparte jest na chęci pogłębienia wiedzy i przekucia jej później na większe zarobki :) Kilka razy usłyszałem ostatnio, że na rynku jest deficyt osób znających VBA i przez to ich zarobki są bardzo atrakcyjne, a że język VBA nie wydaje się bardzo skomplikowany to czemu nie dołączyć do tej „elity”? Pozostaje tylko rozpocząć naukę, samodyscyplina i może za rok będę mógł powiedzieć o sobie, że „jestem dobry z VBA”?

    1. Rafale,

      VBA musi znaleźć zastosowanie zwłaszcza, że na całym świecie z Excela korzysta ponad 500 mln użytkowników (niektórzy twierdzą, że ta liczba jest jeszcze większa, ale do oficjalnych statystyk nie dotarłem). Nie wiem w jakiej branży na co dzień pracujesz (a może jeszcze studiujesz), ale z doświadczenia wiem, że w finansach, bankowości (zwłaszcza back-office), ubezpieczeniach etc. znajomość pisania makr, które rozwiązują rzeczywiste problemy jest nieoceniona.

      Naukę sugeruję rozpocząć od lektury książki Johna Walkenbacha – Excel 2013 PL. Programowanie w VBA. To dobry podręcznik na początek drogi. :) No i nie pozostaje Ci nic innego jak ćwiczyć, ćwiczyć i jeszcze raz ćwiczyć.

      Powodzenia w realizacji tego ambitnego celu!

      Pozdrawiam,

  3. Super Makro. Mam tylko pytanie jak go przerobić żeby tworzył listę arkuszy w nowym arkuszu excela (oczywiście wiem że można przekopiować z worda z powrotem do excela ale po co :) )

    1. Cześć TOF99,

      Dzięki za komentarz i dobre pytanie. :) Oczywiście zamiast tracić cenny czas na kopiowanie tych informacji z Worda do Excela wystarczy nieco zmienić kod makra, aby to samo działo się bezpośrednio w Excelu. Poniżej znajdziesz makro po niezbędnych modyfikacjach.

      Option Explicit
      Sub ArkuszeZExcelaDoArkuszaWExcelu()
      ‚Definicja zmiennych
      Dim i As Integer
      ‚Tworzymy nowy arkusz jako pierwszy w skoroszycie
      ActiveWorkbook.Sheets.Add Before:=Sheets(1)
      ‚Wstawianie nazw arkuszy do następujących po sobie kolejnych komórek w kolumnie A nowego arkusza
      For i = 1 To Sheets.Count Step 1
      Cells(i, 1) = Sheets(i).Name
      Next i
      End Sub

      I to wszystko! Po uruchomieniu makra najpierw wstawiony zostanie nowy arkusz na samym początku całej listy arkuszy, a następnie w kolejnych komórkach kolumny A umieszczone zostaną poszczególne nazwy arkuszy. Oczywiście lista rozpocznie się od arkusza utworzonego za pomocą tego makra. Jeśli chcesz go pominąć na liście, to wystarczy, że dla zmiennej i przypiszesz wartość 2 zamiast 1 (For i = 2 To Sheets.Count Step 1).

      Miłego użytkowania. :)

      Pozdrawiam,

      1. Jeszcze mała uwaga. W prezentowanym kodzie umieszczone są trzy komentarze, które normalnie w edytorze VBA rozpoczynają się od znaku apostrof (‚), który niestety tutaj wyświetla się błędnie. 😐

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *