Jak usunąć wszystkie arkusze w skoroszycie Excela oprócz pierwszego arkusza?

Skrypciarze odpowiadają na Wasze pytania

Jak usunąć wszystkie arkusze w skoroszycie Excela oprócz pierwszego arkusza?

Cześć, Skrypciarze! Jak usunąć wszystkie arkusze w skoroszycie Excela oprócz pierwszego arkusza?

-- NN

Cześć, NN. Przepraszamy za wszelkie plamy krwi na ekranie. Jak się okazuje, piszący te słowa Skrypciarz miał w ostatni weekend mały wypadek. Skrypciarz grał ze swoim synem w baseball na podwórku. Gdy syn uznał, że zabawne będzie, jeśli rzuci piłkę tak mocno, jak tylko potrafi, Skrypciarz zrobił unik, piłka uderzyła w szopę, odbiła się do gęstej dżungli z tyłu domu i potoczyła się do małego, ale stromego, parowu. Choć syn Skrypciarza uznał, że najlepiej po prostu kupić nową piłkę, Skrypciarz piszący te słowa zdecydował się wejść do parowu. Jak się okazało, Skrypciarz szedł po kupie martwych gałęzi, która rozsypała się i Skrypciarz poleciał z pagórka na główkę, prosto w jeżyny.

Pomyśleliście zapewne, że lądowanie twarzą w jeżynach boli. I mieliście absolutną rację. Na szczęście oprócz jeżynowych kolców, które musiał wyciągać sobie z rąk, nóg i twarzy, Skrypciarzowi nic się nie stało. W zasadzie to gdy już przestał krwawić, cała ta sytuacja wydawała się zabawna. A przynajmniej synowi Skrypciarza.

Ale wszystko dobre, co się dobrze kończy, prawda? W końcu Skrypciarz wciąż jest żywy (a przynajmniej tak żywy, jak kiedykolwiek był), a wszyscy inni dostaną skrypt, który usuwa wszystkie arkusze w skoroszycie Excela oprócz pierwszego:

Set objExcel = CreateObject("Excel.Application")



objExcel.Visible = True

objExcel.DisplayAlerts = False



Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

i = objWorkbook.Worksheets.Count



Do Until i = 1 

  objWorkbook.Worksheets(i).Delete

  i = i - 1

Loop

O ile znowu gdzieś nie wpadnę, wyjaśnię teraz, jak działa ten skrypt. Jak widzicie, zaczynamy od utworzenia wystąpienia obiektu Excel.Application i ustawienia wartości True własności Visible; to daje nam działające wystąpienie Excela, które widzimy na ekranie. To prowadzi nas do tego wiersza kodu:

objExcel.DisplayAlerts = False

Po co jest ten wiersz? Cóż, jeśli chcecie usunąć arkusz Excela zawierający jakieś dane, wyskoczy okno dialogowe pytające, czy naprawdę chcecie usunąć tne arkusz (dzieje się tak bez względu na to, czy używacie skryptu, czy próbujecie usunąć arkusz w samym Excelu) Tak naprawdę nie chcemy widzieć tego okna dialogowego. W końcu sprawiłoby to, że nasz skrypt zatrzymywałby się za każdym razem, gdy spróbujemy usunąć arkusz i, co gorsza, czekałby, aż klikniemy na okno dialogowe. Jednak jeśli ustawimy wartość False własnośći DisplayAlerts, okna dialogowe się nie pojawią. Zamiast tego skrypt będzie działał tak, jakby okno dialogowe się pojawiło i jakbyśmy kliknęli domyślny przycisk. W tym wypadku jest to idealne rozwiązanie, bo domyślny przycisk to Tak (czyli naprawdę chcemy usunąć arkusz).

Po skonfigurowaniu własności DisplayAlerts odwołamy się do metody Open aby otworzyć skoroszyt C:\Scripts\Test.xls:

Set objWorkbook = objExcel.Workbooks.Open("C:\Scripts\Test.xls")

Po otwarciu skoroszytu, używamy własności Count do określenia liczby arkuszy w Test.xls, przechowując tę wartość w zmiennej licznika o nazwie:

i = objWorkbook.Worksheets.Count

Teraz jesteśmy gotowi na usuwanie arkuszy.

Oczywiście usuwanie elementów kolekcji Microsoft Office może sprawiać problemy, ze względu na to, jak Office zmienia numery elementów za każdym razem, gdy coś zostanie usunięte z kolekcji. Pewnym sposobem na obejście tego problemu jest rozpoczęcie na końcu kolekcji. Pamiętając o tym, tworzymy pętlę Do Until, która wygląda tak:

Do Until i = 1 

  objWorkbook.Worksheets(i).Delete

  i = i - 1

Loop

Jasne, wyjaśnimy, jak działa ta pętla. Powiedzmy, że w kolekcji mamy 5 arkuszy. Zaczniemy od usunięcia ostatniego arkusza w kolekcji (o numerze indeksu 5). To właśnie robi następujący wiersz kodu:

objWorkbook.Worksheets(i).Delete

To działa, ponieważ przydzieliliśmy liczbę arkuszy do zmiennej licznika i; w rezultacie, wartość naszej zmiennej licznika (5) jest równa numerowi indeksu ostatniego arkusza w kolekcji (również 5). Po usunięciu arkusza 5, zmniejszamy i o 1. Jeśli jesteście dobrzy z matematyki, wiecie, że i równa się teraz 4. Zapętlamy skrypt, usuwamy arkusz 4, zmniejszamy i o 1, wracamy na górę pętli i powtarzamy całą czynność. To wszystko trwa aż i będzie równe 1. Kiedy i będzie równe 1, oznaczać to będzie, że w kolekcji jest tylko jeden arkusz. Dzięki odrobinie planowania i przezorności, wiemy też, że ten jeden arkusz jest pierwszym arkuszem w kolekcji. A jako że teraz usunęliśmy wszystkie arkusze oprócz tego jednego, oznacza to, że możemy wyjść z pętli i zakończyć skrypt. Proste jak spadanie z kłody. A przynajmniej jak spadanie z kupy gałęzi.

Jeśli czekacie na puentę, oto ona. Gdy Skrypciarz piszący te słowa leżał na dnie parowu, uznał, że skoro już tam jest, może przynajmniej znajdzie piłkę. Gdy to zrobił, okazało się, że była w niej wielka dziura i piłka była do wyrzucenia. Innymi słowy, i tak musiał kupić nową piłkę, poszedł tylko trochę bardziej okrężną (i bolesną) drogą.

Do początku strony