Pchełki VBA, odcinek 21: Makra precz!

https://xpil.eu/BJJnq 📋

Dziś pokażę w jaki sposób za pomocą VBA zapisać bieżący dokument (zawierający makra, czyli kod VBA) w postaci "bezmakrowej".

A po co?

Zastosowań jest kilka. Mi się zdarzyło niedawno, że musiałem zaimplementować "bursting" plików XLSX, czyli masowo wygenerować pliki o identycznej strukturze, ale z różnymi danymi. "Zasysanie" danych wymagało użycia kodu VBA i odbywało się w dokumencie "głównym", natomiast po każdorazowym "zassaniu" danych należało bieżący dokument zapisać "na boku" pod inną nazwą, a następnie zassać kolejną porcję danych, zapisać kolejny plik, i tak w koło Macieju prawie osiemset razy.

O zasysaniu danych może kiedy indziej, dziś skupię się tylko na kwestii zapisania kopii bieżącego pliku, w wersji bez kodu VBA.

Jak być może niektórzy Czytelnicy kojarzą, pliki z kodem VBA mają roszerzenie ".xlsm", a te bez makr ".xlsx". Jest tak od wersji Office 2007, przedtem było trochę inaczej, zakładam, że większość z pięciorga moich Czytelników używa wersji 2007 lub nowszej.

Jeszcze raz: chcę zapisać bieżący plik .xlsm jako .xlsx.

Najpierw, tradycyjnie, kod:

Option Explicit

Public Sub SaveMeAsMacroFree()
    ThisWorkbook.SaveCopyAs "temp.xlsm"
    Application.DisplayAlerts = False
    Dim wbk As Excel.Workbook
    Set wbk = Workbooks.Open("temp.xlsm")
    wbk.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", ".xlsx"), xlOpenXMLWorkbook
    Application.DisplayAlerts = True
    wbk.Close False
    Kill "temp.xlsm"
End Sub

Teraz trochę objaśnień.

Public Sub SaveMeAsMacroFree()

Tworzymy procedurę, nazywamy ją SaveMeAsMacroFree (bez parametrów). W wersji podrasowanej moglibyśmy utworzyć ją z parametrem oznaczającym nową nazwę pliku, ale ponieważ to jest pchełka, upraszczamy do minimum. Nowa nazwa pliku będzie identyczna z bieżącą, z wyjątkiem rozszerzenia (czyli będzie .xlsx zamiast .xlsm)

    ThisWorkbook.SaveCopyAs "temp.xlsm"

Najpierw zapisujemy kopię bieżącego dokumentu pod tymczasową nazwą "temp.xlsm".

    Application.DisplayAlerts = False

Wyłączamy ostrzeżenia Excela o niekompatybilności, makrach i innych takich.

    Dim wbk As Excel.Workbook

Deklaracja zmiennej typu Workbook (czyli dokument Excela)

    Set wbk = Workbooks.Open("temp.xlsm")

Otwieramy, jako osobny dokument, zapisaną przed chwilą kopię.

    wbk.SaveAs Replace(ThisWorkbook.FullName, ".xlsm", ".xlsx"), xlOpenXMLWorkbook

Zapisujemy tę otwartą przeed chilą kopię pod nazwą identyczną z naszym bieżącym dokumentem, ale z roszerzeniem "xlsx". Jako typ zapisywanego pliku podajemy xlOpenXMLWorkbook ("zwykły" plik Excelowy bez makr)

    Application.DisplayAlerts = True

Przywracamy ostrzeżenia dotyczące kompatybilności.

   wbk.Close False

Zamykamy otwartą przed chwilą kopię tymczasową, bez zapisywania zmian (żadnych zmian nie zrobiliśmy, no i mamy już zapisane co trzeba).

    Kill "temp.xlsm"

Kasujemy tę kopię, bo nie jest nam już potrzebna

End Sub

Koniec i bomba, kto nie skumał ten trąba temu objaśniam jeszcze bardziej: najbardziej oczywiste pytanie, które powinno się rzucić tej części Czytelników, którzy po pierwszych trzech liniach kodu nie zasnęli, jest "a dlaczego robić to tak po kolei? Nie da się od razu w jednym kroku zapisać kopii jako xlsx?"

No właśnie, nie da się. Metoda SaveCopyAs nie daje możliwości zmiany formatu na inny. A z kolei gdybyśmy wykonali zamiast tego zwykłe "SaveAs", wówczas próbowaliśmy zapisać bieżący plik jako plik bez kodu VBA, co mija się z celem, ponieważ to bieżący plik, za pomocą właśnie kodu VBA, steruje całym procesem, a więc podcięlibyśmy sobie nie tylko gałąź, na której siedzimy, ale od razu całe drzewo. Tak więc trzeba to rozbić na dwa kroki: 1. zapisać tymczasową kopię bieżącego pliku w formacie xlsm, a następnie otworzyć tę kopię i zapisać ją jako xlsx. No i na koniec skasować kopię.

Pomysły na ulepszenie powyższego kodu:

1. Wyłączamy ostrzeżenia - super. Ale potem je włączamy, chociaż użytkownik mógł je mieć wyłączone na stałe w opcjach. Trzeba by przed wyłączeniem zapamiętać stan i potem go przywrócić.

2. Plik "temp.xlsm" może już istnieć w bieżącym folderze - ponieważ wyłączyliśmy ostrzeżenia, nadpiszemy go. Co prawda nikt o trzycyfrowej inteligencji nie powinien zapisywać niczego ważnego w pliku "temp.xlsm", ale różnie bywa. Zamiast tego należałoby wygenerować losową nazwę dla kopii tymczasowej, i to najlepiej taką, która nie istnieje w foldrze bieżącym (licho nie śpi).

Smacznego.

-- Kuchcik.

 

[yop_poll id="33"]

https://xpil.eu/BJJnq 📋