Dziś przygotujemy makro VBA, które będzie masowo zmieniać formaty plików. Przedstawię też praktyczne zastosowanie takiego makra. Pracując z danymi pochodzącymi z wielu różnych źródeł prędzej czy później trafiamy na sytuację, gdzie pliki z danymi wymagają przez nas przekształcenia zanim będziemy mogli je dalej wykorzystać. Z nabieranym doświadczeniem i poznawanymi narzędziami zdobywamy coraz więcej możliwości łatwego poradzenia sobie z takim problemem. Jeżeli mamy do zmodyfikowania kilka plików miesięcznie to budowanie skomplikowanych rozwiązań może nie być najlepszym rozwiązaniem. W przypadku gdy mamy do czynienia z setkami plików ręczna praca na pewno nie jest tym co chcemy robić.

W wielu przypadkach poradzimy sobie podklejaniem odpowiednich zestawów formuł lub wykorzystując Power Query, które świetnie sobie radzi z przekształceniami danych i pobieraniem ich z wielu plików w bardzo krótkim czasie. Czasami jednak i te rozwiązania nie są wystarczające. Dlatego często powtarzam, że lepiej znać kilka narzędzi średnio niż jedno perfekcyjnie. Wykorzystując proste rozwiązania w VBA , Power Query i Power Pivot możemy osiągnąć niesamowite efekty. Będą to rozwiązania bardzo efektywne i wygodne, a to przecież jest naszym celem – rozwijanie swoich umiejętności przy optymalizacji pracy. Z kolei optymalizacja pracy przekłada się na większe osiągnięcia i większą satysfakcję z pracy.

Życiowy przykład – kiedy makro ma sens

Sytuacja z którą dziś musimy sobie poradzić wygląda tak że dostajemy pliki xml zapisywane w kilku różnych folderach. Do każdego folderu trafia kilkadziesiąt plików co miesiąc. W trakcie przetwarzania danych czasami wychodzą kombinacje danych, które musimy ręcznie poprawić w pliku źródłowym. Pliki xml możemy otwierać w Excelu i otrzymujemy ładną tabelę z danymi ale samo otwieranie plików ma dwa ograniczenia. Po pierwsze musimy określić w jaki sposób chcemy otworzyć plik, a robimy to przez wybór odpowiedniej opcji w wyskakującym okienku, co jest dodatkową czynnością. Pierwsza opcja otwiera plik z elegancką, zdefiniowaną tabelą danych. Druga opcja wyświetla dane zwyczajnie wpisane w komórki, a sam plik jest do odczytu co wiąże się z dodatkowymi czynnościami przy zapisywaniu. Ostatnia opcja wyświetla mapę struktury danych a nie same dane więc nie pozwala na edycję niektórych wpisów.

Same pliki xml dla początkującego użytkownika Power Query mogą być uciążliwe, więc najwygodniej byłoby je zapisać jako pliki Excela. Ponadto jeżeli mamy tam duże ilości wierszy otwarcie takiego pliku będzie trwało bardzo długo. O wiele dłużej niż tego samego pliku zapisanego w formacie xlsx. Dlatego wygodnym rozwiązaniem byłoby zamienić każdy plik na format Excela. Do tego działania wykorzystamy proste makro VBA, które zmieni formaty plików.

Jeśli chcesz zobaczyć jakie formaty plików potrafi otworzyć Excel to zajrzyj na tę stronę LINK

Przygotowanie listy folderów plików do zmiany formatu

Zaczynamy od przygotowania w Excelu listy folderów, w których makro ma szukać pliki. Sam plik Excela oczywiście zapisujemy w formacie xlsm czyli w formacie obsługującym makra.

W folderach mamy pliki xml.

Edytor VBA – zaczynamy pisać makro, które zmieni formaty plików

Przechodzimy do edytora Visual Basic klikając przycisk Visual Basic na karcie Deweloper lub wciskając skrót Alt+F11 i zaczynamy pisanie makra w nowym module. Zaczynamy od wpisania polecenia Option Explicit, które wymaga deklarowania każdej zmiennej wykorzystywanej w kodzie. Można to pominąć ale lepiej wprowadzić sobie taki nawyk, zabezpiecza nas to przed literówkami i optymalizuje pracę makra. Nasze zmienne to „Folder”, „NazwaPliku”, „PoczatekNazwy” jako zmienne tekstowe i zmienna „i” jako zmienna liczbowa.

Nasze makro rozpoczynamy od polecenia Sub i określenia jego nazwy „ZmianaFormatuXML”, kod makra jak zawsze kończymy poleceniem End Sub. Następnie wyłączamy w Excelu wyświetlanie komunikatów. Robimy to aby podczas otwierania, zamykania plików itp. nie wyskakiwały nam komunikaty, które wstrzymywałyby pracę naszego makra.

Application.DisplayAlerts = False

Stosujemy pętlę For…next

Jeśli musimy pracować na kilku folderach to stosujemy pętlę For. W naszym przykładzie zakładamy, że możemy mieć maksymalnie 5 folderów. Następnie do zmiennej Folder przypisujemy wartość z komórki Cells(i + 2, 2), czyli dla pierwszej iteracji komórkę z trzeciego wiersza i drugiej kolumny (B3), a przy kolejnej iteracji B4 itd. Jeżeli przewidzieliśmy maksymalnie 5 folderów ale wykorzystujemy tylko 3 to dokładamy warunek sprawdzający czy zmienna Folder ma niepustą wartość, czyli czy w danej komórce był jakiś tekst – If Folder <> „” Then ….. End if.

W kolejnym kroku musimy pobrać nazwę pliku, służy do tego funkcja Dir. Nazwę wyciągamy z pełnej ścieżki pliku wraz z rozszerzeniem pliku (formatem pliku). Aby działać na wszystkich plikach w folderze wykorzystujemy zapis Dir(Folder & „*.xml”), który mówi „pobierz nazwę pliku dla pliku w określonym folderze, który ma dowolną nazwę ale ma rozszerzenie „.xml””. Nazwę pliku przypisujemy do zmiennej NazwaPliku. Następnie dokładamy pętlę Do While NazwaPliku <> „”, czyli działaj dopóki nazwa pliku jest niepusta. Pętla ta musi kończyć się poleceniem Loop.

Potrzebujemy też kroku, który zapamięta jaka jest nazwa pliku bez rozszerzenia. W tym celu do zmiennej  PoczatekNazwy przypisujemy wartość Left(NazwaPliku, Len(NazwaPliku) – 4). Funkcja Left pobiera fragment tekstu z lewej strony o określonej długości, długość jaką potrzebujemy to długość nazwy pliku bez rozszerzenia, czyli pobieramy długość z nazwy pliku wraz z rozszerzeniem (funkcja Len) i od tego wyniku odejmujemy cztery czyli tyle ile znaków ma kropka z rozszerzeniem xlm.

Zmiana formatu plików – serce makra

Przechodzimy już do samego otwierania plików xml. Robimy to za pomocą polecenia Workbooks.OpenXML, gdzie określamy pełną ścieżkę pliku Folder & NazwaPliku i dodajemy parametr LoadOption:=xlXmlLoadImportToList. Parametr ten określa, że plik xml chcemy otworzyć w Excelu jako tabelę xml. Po tym kroku moglibyśmy wprowadzić polecenia modyfikujące dane do naszych potrzeb, jeśli byłaby taka potrzeba.

Kluczowy jest krok zapisania otwartego pliku z nowym rozszerzeniem. Wykorzystujemy polecenie zapisz jako czyli ActiveWorkbook.SaveAs. Jako konieczny parametr musimy podać pełną ścieżkę pliku wraz z nazwą i rozszerzeniem. Na szczęście folder i nazwę pliku mamy przechowywane w wartościach zmiennych Folder i PoczatekNazwy. Brakuje nam tylko dołożenia nowego rozszerzenia „.xlsx”. Po zapisaniu pliku musimy go zamknąć – polecenie ActiveWindow.Close, w przeciwnym razie po zakończeniu makra możemy mieć otwarte setki plików Excela.

Usunięcie niepotrzebnych plików poleceniem VBA – rozbudowujemy makro

Nasze makro, gdy już zmieni formaty plików, może wykonywać dodatkowe zadania wykorzystując inne polecenia VBA. Jeżeli pierwotnych plików xml nie chcemy już więcej wykorzystywać i nie chcemy aby zajmowały nam miejsce na dysku, możemy je bardzo łatwo usunąć za pomocą polecenia Kill. Musimy tylko podać ścieżkę pliku wraz z nazwą i rozszerzeniem. Aby to uzyskać wystarczy, że połączymy zmienną Folder i zmienną NazwaPliku, która nadal przechowuje nazwę pliku z rozszerzeniem xlm. Polecenie Kill należy używać z dużą ostrożnością, ponieważ po pierwsze nie wyświetli komunikatu czy na pewno potwierdzamy usunięcie pliku. Po drugiej sam proces usuwania pliku przy obecnych dyskach twardych może być niezauważalny.

Po zakończeniu pracy nad plikiem chcemy przejść do kolejnego w danym folderze. Wykorzystujemy tu zapis NazwaPliku = Dir, który skutkuje przypisaniem do zmiennej NazwaPliku nazwy kolejnego pliku w folderze i tak aż przejdziemy przez wszystkie pliki z rozszerzeniem xlm w folderze. Następnie pętla For przejdzie do kolejnego folderu.

Na sam koniec pozostaje nam przywrócenie normalnego działania Excela w kwestii wyświetlania komunikatów, czyli zmiana parametru dla Application.DisplayAlerts na True. Możemy też zastosować polecenie MsgBox aby wyświetliło nam komunikat, gdy makro skończy pracę.

W efekcie otrzymujemy w folderach pliki Excela z tymi samymi nazwami co pierwotne pliki. Używając tak naprawdę kilku linijek kodu VBA, otrzymaliśmy makro, które za nas zmienia formaty plików.

Na pierwszy rzut oka może się wydawać, że takie makro przy dużej ilości plików. Może zablokować nam możliwość pracy na Excelu przez długi czas. Jest na to bardzo prosty i skuteczny sposób. Wystarczy uruchomić Excela w nowej sesji klikając jego ikonę w menu Start Windowsa z wciśniętym przyciskiem Alt. Wtedy uruchomi się nam nowy Excel jako zupełnie osobny program względem wcześniej otwartego Excela. Wystarczy na jednym Excelu uruchomić makro, które będzie działało w tle, a na drugim pracować zupełnie normalnie. W ten sposób proces zmiany formatów plików nie pływa na naszą codzienną pracę.

Makro VBA do zmiany formatów plików – kod

Możesz też pobrać kod w pliku tekstowym Pobierz plik . Plików z modułami makr VBA nie można zamieszczać na serwerze, dlatego musisz samodzielnie wkleić je do swojego Excela.

Option Explicit

Dim Folder As String
Dim NazwaPliku As String
Dim PoczatekNazwy As String
Dim i As Long

Sub ZmianaFormatuXML()

Application.DisplayAlerts = False

For i = 1 To 5
    Folder = Cells(i + 2, 2)
    If Folder <> "" Then
            NazwaPliku = Dir(Folder & "*.xml")
            Do While NazwaPliku <> ""
            PoczatekNazwy = Left(NazwaPliku, Len(NazwaPliku) - 4)
            
                Workbooks.OpenXML Filename:=Folder & NazwaPliku, LoadOption:=xlXmlLoadImportToList
                
                    ActiveWorkbook.SaveAs Filename:=Folder & PoczatekNazwy & ".xlsx" 
                ActiveWindow.Close
            
                Kill (Folder & NazwaPliku)
             NazwaPliku = Dir
            Loop
    End If
Next

Application.DisplayAlerts = True
MsgBox "Gotowe"

End Sub