Jak otworzyć wszystkie arkusze programu Excel w danym folderze i uruchomić makro w każdym z tych arkuszy?

Skrypciarze odpowiadają na Wasze pytania

Jak otworzyć wszystkie arkusze programu Excel w danym folderze i uruchomić makro w każdym z tych arkuszy?

Cześć, Skrypciarze! Jak można uruchomić pętle, która przeszłaby przez cały folder, otworzyć wszystkie pliki programu Excel i w każdym z nich uruchomić odpowiednie makro?

-- BR

Cześć, BR. Dobrze, że to pytanie zadajesz akurat dziś, bo przez cały zeszły tydzień przez mój mózg non-stop przelatywały piłeczki tenisowe – to w jedną, to w drugą stronę i nie mogłem skupić za bardzo głowy w jednym punkcie – tak mi latała od prawej do lewej i na odwrót… No ale już po finale US Open i w końcu mój umysł się trochę zamknął (teraz mam status mUSg Closed). Szyja trochę boli, nie powiem, tym bardziej, że w ostatni weekend oglądałem również mecz baseballowy, w którym to grał Skrypciarski Syn i zaraz go streszczę...

Nie bójcie się, żartowałem tylko, już nie raz obrywało mi się od Skrypciarskiej Pani Redaktor za długie opisy meczów... Może kobieta nie lubi sportu… Jeżeli jednak ciekawi Was przebieg i wynik meczu, to napiszcie i z wielką chęcią, przy użyciu metody kopiuj-wklej, wkleję Wam do wiadomości e-mail wszystko to, co wycięła mi dziś nasza Pani Redaktor.

No, ale do roboty! Pytasz się, BR, jak napisać skrypt, który po przejściu przez cały folder otworzyłby w nim wszystkie pliki programu Excel, a następnie uruchomił makro w każdym z nich. Już odpowiadam:

strComputer = "tvsfrank"



Set objWMIService = GetObject("winmgmts:\\" & strComputer & "\root\cimv2")



Set colFileList = objWMIService.ExecQuery _

    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _

        & "ResultClass = CIM_DataFile")



Set objExcel = CreateObject("Excel.Application", strComputer)

objExcel.DisplayAlerts = False



For Each objFile In colFileList

    If objFile.Extension = "xls" Then

        Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)

        Set objWorksheet = objWorkbook.Worksheets(1)



        objExcel.Run("BoldfaceHeadings")



  objWorkbook.SaveAs objFile.Name

        objWorkbook.Close

    End If

Next



objExcel.Quit

Trafna obserwacja! Skrypt nasz różni się trochę od typowego skryptu WMI. Jak wszyscy już wiemy, za każdym razem, gdy piszemy skrypt WMI (w powyższym skrypcie używamy WMI do pobrania kolekcji wszystkich plików z folderu C:\Test), zaczynamy go od takiego oto wiersza kodu:

strComputer = "."

Dlaczego właśnie tak? Ano dlatego, że w skryptach WMI przypisujemy najczęściej nazwę komputera na którym chcemy pracować zmiennej strComputer. Prawda, kropka (.) nie wygląda za bardzo jak nazwa komputera, ale, przynajmniej w WMI, kropka jest skrótem oznaczającym komputer lokalny. Nasze skrypty zwykle zaczynają się od strComputer = ".", ponieważ dzięki temu działają na komputerze lokalnym bez żadnego dodatkowego kodowania, bez względu na nazwę tego właśnie komputera. Można też uruchomić skrypt na komputerze zdalnym, trzeba tylko przypisać zmiennej strComputer nazwę żądanego komputera zdalnego:

strComputer = "atl-ws-01"

Dlaczego więc nie zaczęliśmy dzisiejszego skryptu od strComputer = "."? Chcieliśmy utworzyć skrypt, który byłby możliwie jak najmniej skomplikowany, a jednocześnie wykonywał działania na plikach Excel za pomocą makr na komputerze lokalnym lub zdalnym. Jak już wspomnieliśmy wyżej, WMI używa kropki na oznaczenie komputera lokalnego. Niestety, kropka nie ma wcale takiego znaczenia dla programu Excel. Jeżeli tworzymy wystąpienie programu Excel, musimy podać dokładnie, na jakim komputerze będzie działał nasz skrypt. Dlatego właśnie musimy podać nazwę komputera, nawet jeżeli pracujemy na komputerze lokalnym.

Jeżeli nie rozumiecie za bardzo, nad czy się tu rozwodzę, to nie przejmujcie się, tylko zaufajcie mi i przypiszcie nazwę komputera do zmiennej strComputer (nawet jeżeli chcecie pracować na arkuszach z komputera), a wszystko na pewno się uda.

Po połączeniu się z usługą WMI, w celu zwrócenia kolekcji wszystkich plików z folderu C:\Test, uruchamiamy następującą kwerendę:

Set colFileList = objWMIService.ExecQuery _

    ("ASSOCIATORS OF {Win32_Directory.Name='C:\Test'} Where " _

        & "ResultClass = CIM_DataFile")

W tym momencie uruchamiamy makra, lub raczej tak zrobimy, tuż po utworzeniu wystąpienia obiektu Excel.Application:

Set objExcel = CreateObject("Excel.Application", strComputer)

Wywołujemy tu metodę CreateObject i ona też wygląda tu trochę inaczej niż zwykle (używając slangu: pojechaliśmy po bungie, ale z nas krejzole, co nie?). CreateObject ma tutaj dwa parametry: Excel.Application (ProgID projektu, który chcemy utworzyć), oraz zmienną strComputer. (Zwykle nasze wywołania obiektu CreateObject ograniczają się do ProgID.) Kiedy nadamy mu dwa parametry, otrzymujemy nowe wystąpienie obiektu (w tym przypadku aplikacji programu Excel). Co więcej, to wystąpienie zostanie utworzone według drugiego parametru. Jako że zmiennej strComputer nadaliśmy wartość atl-ws-01, nasze wystąpienie zostanie utworzone na komputerze atl-ws-01, bez względu na to, czy jest on komputerem lokalnym, czy zdalnym.

Po utworzeniu wystąpienia Excel, ustawiamy jego właściwość DisplayAlerts na False; to instruuje program Excel, aby nie wyświetlał żadnych komunikatów ani okien dialogowych w trakcie zapisywania pliku. Jeżeli tego nie zrobimy, w trakcie zapisywania pliku możemy otrzymać następujący komunikat:

Jeżeli nastawimy DisplayAlerts na False, program Excel pominie wyświetlanie takich komunikatów, zamiast tego stosując działanie domyślne. W tym wypadku odpowiada to kliknięciu przycisku „Yes" – „tak, chcę zastąpić ten plik". Na pewno zauważyliście również, że tym razem nie ustawialiśmy właściwości Visible na True. Jeżeli chcecie, proszę bardzo, i dodajcie poniższy wiersz kodu po tym, który konfiguruje DisplayAlerts:

objExcel.Visible = False

Miejcie jednak na uwadze, że wystąpienie programu Excel będzie widoczne na ekranie tylko, jeżeli działamy na komputerze lokalnym. Niezależnie od tego, jaką wartość nadamy właściwości Visible, program Excel będzie się uruchamiał w oknie ukrytym zawsze, gdy skrypt zostanie uruchomiony na zdalnym komputerze. Będzie działał tak samo, z jedną różnicą – nie będzie widziany na ekranie ani zdalnego, ani lokalnego komputera.

To wszystko z uwagi na bezpieczeństwo i nic nie da się z tym zrobić.

OK, teraz możemy uruchomić nasze makra. Zaczynamy od utworzenia pętli For Each, która przechodzi przez wszystkie pliki w kolekcji. Wewnątrz pętli sprawdzamy wartość właściwości Extension, dzięki temu upewniamy się, że pracujemy z plikiem programu Excel (rozwinięcie musi kończyć się na xls i bardzo ważne – bez kropki):

If objFile.Extension = "xls" Then

Zakładając, że pracujemy na arkuszu programu Excel, musimy skorzystać z metody Open i tym samym otworzyć pierwszy plik kolekcji następującym wierszem:

Set objWorkbook = objExcel.Workbooks.Open(objFile.Name)

Zauważcie, że nie musimy podawać ścieżki do pliku, skorzystaliśmy tutaj z właściwości Name. Następnie łączymy się z pierwszym skoroszytem w pliku, po czym poniższy fragment kodu uruchamia nam makro BoldfaceHeadings (nie trzeba chyba nadmieniać, ze to makro musi być dostępne w każdym arkuszu):

objExcel.Run("BoldfaceHeadings")

Po uruchomieniu makra przywołujemy metodę SaveAs i zapisujemy zmodyfikowany plik, po czy zamykamy dany skoroszyt korzystając z metody Close:

objWorkbook.SaveAs objFile.Name

objWorkbook.Close

Następnie uruchamiamy pętlę i powtarzamy cały proces dla następnego pliku w kolekcji. Jeżeli przeszliśmy już przez wszystkie pliki, pozostaje nam wyjście z pętli, wywołanie metody Quit, pożegnanie wystąpienia programu Excel (nie zapominamy o pomachaniu) i przeciągnięcie się z zadowolenia, że już skończyliśmy.

Ja właśnie się przeciągnąłem…Wprawdzie nie robię tego tak zgrabnie jak Rafael Nadal, ale w końcu skarpetki mam zawsze na właściwym poziomie i nie muszę się upewniać...

Do początku strony