Jak PDFować arkusze Excela automatycznie z VBA

Właściciel firmy Pan Kabelek poprosił mnie o pomoc w zapisaniu arkuszy z pliku Excela jako pliki pdf, gdyż chce je zapisać jako raporty. Co prawda można za pomocą opcji Zapisz jako wybrać format pliku jako .pdf i zapisać tak zaznaczone arkusze, ale to raczej żmudna praca. Dlatego doradziłem mu, żeby zautomatyzował ten proces z pomocą VBA.

W pierwszej kolejności plik w którym stworzymy makro musi mieć rozszerzenie .xlsm, żeby Excel mógł w nim zapisać kod VBA. Później można otworzyć edytor VBA za pomocą skrótu klawiszowego Alt + F11. Następnie trzeba dodać moduł, w którym zapiszemy nasz konkretny kod VBA. Żeby to zrobić należy w oknie Project Explorer (jeśli nie jest otwarte automatycznie, można je otworzyć za pomocą skrótu klawiszowego Ctrl + R) należy kliknąć prawym przyciskiem myszy na dowolnym arkuszu i z podręcznego menu wybrać opcję Insert -> Module jak pokazuje rysunek poniżej.

Następnie w otwartym module będziemy mogli zacząć pisać już interesujący nasz kod VBA. Żeby właściciel firmy mógł sam zrozumieć kod i później ewentualnie go modyfikować postanowiłem wyjaśnić mu na prostych przykładach poszczególne elementy kodu, a następnie połączyć je razem, żeby uzyskać interesujący go efekt. Zacząłem od pokazaniu prostego kodu do zapisania aktywnego arkusza (ActiveSheet) jako pdf na dysku C: za pomocą funkcji ExportAsFixedFormat:

Sub ZapiszAktywnyArkuszJakoPDF()
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Raport.pdf"
End Sub

Najważniejsza jest tutaj metoda ExportAsFixedFormat dostępna dla arkuszy w pliku Excela dlatego zebrałem jej argumenty wraz z krótkim opisem oraz ich ważnością dla nas w tabelce.

Oczywiste jest, że istotny jest dla nas argument Type, bo chcemy zapisać konkretnie plik .pdf. Równie istotna jest nazwa pliku, a dokładniej chcemy tu podać dokładną ścieżkę do pliku, żeby plik nie zapisywał się w domyślnej lokalizacji. Ważny jest dla nas również argument IgnorePrintAreas ponieważ chcemy zawsze zapisywać cały arkusz, więc dla pewności chcemy pominąć obszary wydruku. Domyślne ustawienie tego nie robi, więc będziemy musieli przypisać temu argumentowi wartość True. Ostatni argument, który nas interesuje to OpenAfterPublish. W okresie testowania makra będziemy chcieli, żeby stworzone przez nas .pdfy automatycznie się otwierały, żebyśmy szybciej mogli sprawdzić, czy zapisanie przebiegło prawidłowo, ale w końcowym kodzie możemy pominąć ten argument, bo domyślnie nie otwiera on zapisanych plików.

Najtrudniejszy do stworzenia będzie argument Filename. Właściciel firmy Pan Kabelek postawił następujące warunki dla niego, a właściwie dla zapisanego pliku. Plik pdf ma:

• Zapisać się w tym samym folderze co aktywny plik Excela,
• Jego nazwa ma zawierać nazwę pliku Excela oraz nazwę zapisywanego arkusza.
• W nazwie powinien się znaleźć znacznik czasowy.

Aktualną ścieżkę do pliku można łatwo wyciągnąć z odpowiedniej właściwości aktywnego pliku Excela (ActiveWorkbook.Path).

Nazwa pliku jest dostępna dla aktywnego pliku pod zmienną Name, ale zawiera również rozszerzenie pliku, które będzie nam przeszkadzać. Dlatego chcemy wyciągnąć tekst przed ostatnią kropką. Żeby to zrobić wykorzystamy funkcję Left (wyciągającą znaki od początku tekstu, czyli od lewej strony) i InStrRev (szukającej pozycji pierwszego wystąpienia znaku/-ów w tekście, ale wyszukiwanie odbywa się od końca). Musimy pamiętać, że zarówno funkcja InStr jak i InStrRev zwraca nam pozycję pierwszego wyszukanego znaku licząc od początku tekstu, czyli od lewej strony. Oznacza to też, że wyciągając tekst od początku tekstu za pomocą funkcji Left zwrócony zostanie również szukany przez nas znak (kropka), a on nie jest nam potrzebny. Dlatego musimy wprowadzić korektę do liczby znaków, które chcemy wyciągnąć, czyli od pozycji kropki odjąć 1. Część formuły wyciągająca nazwę pliku bez rozszerzenie i bez kropki będzie wyglądać następująco:

Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)

Łącząc poszczególne informacje, żeby uzyskać główną ścieżkę pliku, z początkiem nazwy pliku potrzebujemy zmiennej MainPath, której przypiszemy wartość:

MainPath = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)

Przykładowe wyniki elementów ścieżki, które łączymy (ścieżki dostępu do aktualnego pliku oraz początkowej nazwy pliku .pdf, która jest pobierana z nazwy pliku .xlsx bez rozszerzenia) pokazuje rysunek poniżej.

Budując dalej nazwę pliku .pdf zgodnie z naszymi założeniami potrzebujemy teraz wyciągnąć nazwę aktywnego arkusza, czyli za wykorzystamy kod: ActiveSheet.Name.

Pozostaje nam tylko jeszcze znacznik czasowy. Zakładamy, że wystarczy nam data zapisana jako liczba/tekst. Możemy łatwo to uzyskać za pomocą funkcje Format i zmiennej wewnętrznej VBA now. Funkcja Format działa bardzo podobnie do funkcji TEKST w Excelu. Musimy tylko pamiętać, że funkcja Format zawsze jest amerykanką, więc kod użyty do formatowania liczby musi uwzględniać angielski znaczniki. Dlatego, żeby wyciągnąć rok, miesiąc i dzień ze zmiennej Now musimy napisać kod: Format(Now, „yyyymmdd”). Przykładowy wynik tego kodu to: 20250805.

Ponieważ w docelowym kodzie zleceniodawca będzie chciał zapisać poszczególne arkusze jako osobne pliki .pdf doradziłem mu, żeby od razu podzielił tworzenie nazwy pliku (dokładnej ścieżki do pliku) na dwie części. Pierwsza część będzie stała. Będzie wskazywała folder i wyciągała nazwę pliku. Druga część będzie dodawała nazwę aktywnego arkusza i znacznik czasowy wraz z symbolami rozdzielającymi i rozszerzeniem pliku. Cały aktualny kod powinien wyglądać następująco:

Sub ZapiszAktywnyArkuszJakoPDF()
   Dim MainPath As String
   MainPath = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, OpenAfterPublish:=True, IgnorePrintAreas:=True _
      , Filename:= MainPath & "_" & ActiveSheet.Name & "_" & Format(Now, "yyyymmdd") & ".pdf"
End Sub

Ten kod zbuduje nam przykładową ścieżkę do pliku (Filename):
C:\Excel\Artykuły\VBA eksport do pdf\Produkty_Kielecka_20250805.pdf

Ponieważ ustawiliśmy, że zapisany plik .pdf od razu się otwiera (OpenAfterPublish:=True) szybko zauważyliśmy, że jego raport jest ucięty.

Dokładniej dalsza część raportu znajduje się już na innej stronie pliku .pdf. Przy drukowaniu taki problem rozwiązuje się automatycznie dopasowując szerokość raportu do 1 strony wydruku. Drugim rozwiązaniem jest zmiana orientacji strony na poziomą (horyzontalną). Właściciel firmy Pan Kabelek stwierdził, że chce zaimplementować oba rozwiązania. W takim przypadku istotna bardzo jest kolejność kodu, żeby dopasowanie szerokości do 1 strony odbyło się już po zmianie jej orientacji. Dodatkowo trzeba się upewnić, że automatyczne dopasowanie zoomu jest wyłączone. Ponieważ zmieniamy kilka opcji wewnątrz ustawień strony arkusza, możemy w kodzie wykorzystać blok with, żeby linijki kodu były krótsze:

With ActiveSheet.PageSetup
      .Orientation = xlLandscape
      .Zoom = False
      .FitToPagesTall = False
   End With

Ten dodatkowy kod musimy umieścić przed wyeksportowaniem arkusza do pliku .pdf.

Aktualny testowy wynik ucieszył właściciela firmy. Pozostało nam dodać pętlę for each, żebyśmy przeszli po wszystkich arkuszach. W związku z tym będziemy potrzebowali zadeklarować zmienną (ws) odpowiedzialną za jeden arkusz (Worksheet) i przejść po wszystkich arkuszach, czyli po kolekcji Worksheets. Musimy pamiętać w naszym kodzie zamienić odwołanie do aktywnego arkusza na odwołanie do wykorzystywanej przez nas zmiennej oraz wyłączyć automatyczne otwieranie plików. Kod po tych zmianach przyjmie następującą postać:

Sub ZapiszWszystkieArkuszeJakoPDF()
   Dim MainPath As String, ws As Worksheet
   
   MainPath = ActiveWorkbook.Path & "\" & Left(ActiveWorkbook.Name, InStrRev(ActiveWorkbook.Name, ".") - 1)
   
   For Each ws In Worksheets
      With ws.PageSetup
         .Orientation = xlLandscape 'xlPortrait
         .Zoom = False
         .FitToPagesWide = 1
      End With
      
      ws.ExportAsFixedFormat Type:=xlTypePDF, IgnorePrintAreas:=True _
      , Filename:=MainPath & "_" & ws.Name & "_" & Format(Now, "yyyymmdd") & ".pdf"
   Next ws
End Sub

Na koniec pracy zleceniodawca zapytał się czy da się zapisać wszystkie arkusze do jednego pliku .pdf? Jak najbardziej można to zrobić za pomocą VBA. Wystarczy zaznaczyć wszystkie arkusze za pomocą kodu: ThisWorkbook.Sheets.Select, a następnie wyeksportować aktywny arkusz tak jak robiliśmy to na początku z pojedynczym arkuszem (musimy tylko odpowiednio stworzyć nazwę dla tego pliku). Jednak przy tym procesie trzeba się zastanowić czy chcemy dopasować szerokość danych do szerokości strony .pdfa i czy zmieniamy orientacje strony, ponieważ te ustawienia są odrębnie ustawiane dla każdego arkusza. Dlatego jest możliwe, że .pdf ze wszystkich arkuszy część stron będzie miał w orientacji poziomej, a część w pionowej. Jeśli chcemy ustawić dla wszystkich arkuszy te same ustawienia strony musimy to zrobić za pomocą już wcześniej używanej pętli.