Jak wstawić nowe wiersze powyżej ostatniego wiersza w arkuszu kalkulacyjnym programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Witamy w rubryce TechNet, w której Skrypciarze z firmy Microsoft odpowiadają na częste pytania dotyczące używania skryptów w administracji systemu. Jeśli macie jakieś pytania z tej dziedziny, zachęcamy do wysłania e-maila na adres: scripter@microsoft.com. Nie możemy zagwarantować odpowiedzi na każde otrzymane pytanie, ale staramy się jak możemy.

Jak wstawić nowe wiersze powyżej ostatniego wiersza w arkuszu kalkulacyjnym programu Excel?

Cześć Skrypciarze! Mam arkusz kalkulacyjny w którym ostatni wiersz podsumowuje wszystkie wiersze znajdujące się powyżej. Jak dodać nowe wiersze (i nowe dane) do tego arkusza, upewniając się, że ostatni wiersz zawsze będzie podsumowywał wiersze znajdujące się ponad nim?

-- AW

Cześć, AW. Jestem trochę zakręcony dzisiaj dla odmiany, w tym momencie cała ekipa czeka na transport, który zabierze całą masę rzeczy do Barcelony na forum informatyczne TechEd.

Nie, nie, ja nigdzie nie jadę. Wybiera się tam Jean Ross oraz Greg Stem i mają nadzieję spotkać tam wielu z naszych czytelników (szukajcie ich w sekcji Zapytaj eksperta). Ja wiem, że spotkanie ze Skrypciarzem to może nie być najbardziej ekscytujące, co może kogoś spotkać w Barcelonie (z naciskiem na może). Dlatego, w ramach umilania tego spotkania, poza uśmiechniętą twarzą Jean i skwaszoną miną Grega czekają tam na Was specjalne egzemplarze książeczek Dr. Scripto’s Fun Book (razem z bonusową płytą CD). Co więcej, będziecie mieli ostatnią szansę na wygranie laleczki Dr Scripto. Ciągle mało? To wcale nie koniec atrakcji, ale nie mogę przecież zepsuć niespodzianki.

A co, jeżeli nie zapisaliście się jeszcze na forum TechEd? No cóż, zła wiadomość: nie ma już biletów. (Ludzie zaczęli się masowo zapisywać kiedy się okazała, że Jean Ross pojedzie tam osobiście.) Ale nie martwcie się, ja będę w miarę możliwości na bieżąco relacjonował co tam się dzieje.

I wcale nie będę opowiadał o nudnych prezentacjach w programie PowerPoint.

Nie będę też opowiadał o skrypcie, który wstawia wiersze i dane powyżej ostatniego wiersza w arkuszu kalkulacyjnym. Dlaczego? Ponieważ zrobię do dzisiaj, raz, a porządnie. Oto skrypt:

Const xlDown = -4121



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



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

Set objWorksheet = objWorkbook.Worksheets(1)



Set objRange = objExcel.Range("A1")

objRange.End(xlDown).Activate



intRow = objExcel.ActiveCell.Row

intColumn = objExcel.ActiveCell.Column

Set objRange = objWorksheet.Cells(intRow, intColumn).EntireRow



For i = 1 to 10

    objRange.Insert(xlShiftDown)

Next



For i = 1 to 10

    objExcel.Cells(intRow, 1).Value = i

    intRow = intRow + 1

Next



strFormula = "=SUM(A1:A" & intRow - 1 & ")"

objExcel.Cells(intRow, 1).Formula = strFormula

Porozmawiajmy o tym, jak ten skrypt działa. Na początek definiujemy stałą o nazwie xlDown, przypisujemy jej wartość -4121; dzięki tej stałej skrypt będzie przesuwał istniejące wiersze w dół przy każdym wstawieniu nowego wiersza. (Wykorzystamy ją także do znalezienia ostatniej komórki w kolumnie A.) Po zdefiniowaniu stałej tworzymy wystąpienie obiektu Excel.Application i nadajemy właściwości Visible wartość True; dzięki temu otrzymamy działające wystąpienie programu Excel widoczne na ekranie. W tym momencie stosujemy poniższe dwa wiersze kodu w celu otwarcia pliku C:\Scripts\Test.xls i połączenia się w pierwszym arkuszem w tym pliku:

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

Set objWorksheet = objWorkbook.Worksheets(1)

Jak wygląda ten arkusz? No cóż, początkowo wygląda jak ten poniżej, który zawiera dane w komórkach od A1 do A5 oraz formułę (taką, która podsumowuje wszystkie powyższe komórki) w komórce A6:

Teraz chcemy wstawić kilka pustych rzędów (a potem dodać jakieś dane) powyżej rzędu 6; w ten sposób nasza formuła pozostanie ostatnim rzędem w arkuszu. Jak to zrobimy? Cóż, na początek musimy umieścić kursor w ostatniej komórce w kolumnie A. Z tym jest pewien problem; przecież nie wiemy, która komórka w kolumnie A jest ostatnia. (No dobra, my wiemy, bo widzieliśmy arkusz. Ale skrypt nie wie.) Dlatego stosujemy poniższe dwa wiersze kodu:

Set objRange = objExcel.Range("A1")

objRange.End(xlDown).Activate

W pierwszym tworzymy obiekt Range reprezentujący komórkę A1. Za pomocą drugiego stosujemy właściwość End oraz stałą xlDown i przesuwamy kursor do ostatniej komórki w kolumnie A, a następnie stosujemy metodę Activate w celu aktywacji tej komórki. Efekt? Kursor zostaje umieszczony w komórce A6 zawierającej naszą formułę.

Tak, to naprawdę prosty sposób na określenie ostatniej komórki w kolumnie pod warunkiem, że ta kolumna nie zawiera pustych komórek. Jeżeli znajdują się w niej puste komórki, należy zastosować inny sposób. Ale o tym to sobie porozmawiamy innym razem.

A co mi tam, poniższy fragment kodu (którego dzisiaj nie omówię) powinien załatwić tę sprawę pod warunkiem, że ostatni wiersz arkusza jest pusty:

Const xlUp = -4162



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



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

Set objWorksheet = objWorkbook.Worksheets(1)



Set objRange = objExcel.Range("A65536")

objRange.End(xlUp).Activate

Nadążacie? Dobra. Naszym następnym zadaniem jest wstawienie kilku pustych wierszy; w tym przypadku wstawimy 10 pustych wierszy. Zamiast robić wszystko za jednym zamachem, zdecydowałem się najpierw wstawić puste wiersze, a dopiero potem dodać do nich dane. Dlaczego? Pomyślałem sobie, że będzie mi łatwiej to przedstawić. W konsekwencji, w naszym skrypcie zastosujemy dwie pętle For Next; jeżeli chcecie połączyć te dwa zadania (wstawianie pustego wiersza i dodawanie do niego danych) w jednej pętli For Next, to bardzo dobrze. (A oprócz samego powiedzenia „bardzo dobrze”, na końcu artykułu umieszczę skrypt, który to zrobi.)

Wracając do naszego skryptu, stosujemy teraz poniższe trzy wiersze kodu:

intRow = objExcel.ActiveCell.Row

intColumn = objExcel.ActiveCell.Column

Set objRange = objWorksheet.Cells(intRow, intColumn).EntireRow

Jak widać, nie dzieje się tu nic skomplikowanego. W wierszu 1 przypisujemy numer wiersza aktywnej komórki (będzie to rząd 6, ponieważ aktywna komórka to A6) do zmiennej o nazwie intRow. W wierszu 2 przypisujemy numer kolumny (1) zawierającej aktywną komórkę do zmiennej intColumn. Na koniec, w wierszu 3 stosujemy właściwość EntireRow w celu utworzenia zakresu zawierającego każdą komórkę w wierszu 6.

Dlaczego wybraliśmy wiersz 6? Wyłącznie z jednego powodu: kiedy wywołamy metodę Insert, będziemy pewni, że wstawimy nowy wiersz, a nie nową komórkę.

Skoro już jesteśmy przy wstawianiu wierszy, służy do tego poniższy fragment kodu:

For i = 1 to 10

    objRange.Insert(xlDown)

Next

I znowu, nie ma tu nic skomplikowanego; po prostu uruchamiamy pętlę For Next, która przejdzie od 1 do 10. Przy każdym przejściu pętli zastosujemy metodę Insert w celu wstawienia nowego wiersz, a dzięki stałej xlDown skrypt będzie wiedział, że ma przesuwać istniejące wiersze w dół. Zanim opuścimy pętlę, nasz arkusz kalkulacyjny będzie wyglądał następująco:

Są trzy rzeczy, które należy wiedzieć na temat tego arkusza kalkulacyjnego:

• Wstawiliśmy do niego 10 pustych wierszy.
• Komórką aktywną jest w dalszym ciągu komórka A6; jest tak dlatego, że komórka aktywne zmienia się tylko wtedy, gdy uczynimy inną komórkę komórką aktywną.
• Formuła, która znajdowała się w komórce A6 została przesunięta w dół o 10 wierszy, dokładnie o tyle chcieliśmy ją przesunąć.

Biorąc pod uwagę wszystkie aspekty, to było całkiem proste. A teraz zajmijmy się zapełnieniem tych pustych wierszy danymi. Jest na to jeden prosty sposób:

For i = 1 to 10

    objExcel.Cells(intRow, 1).Value = i

    intRow = intRow + 1

Next

Uruchomiliśmy tutaj jedynie kolejną pętlę For Next, która także przeszła od 1 do 10. (Dlaczego? No cóż, po wstawieniu 10 pustych wierszy chcemy teraz dodać dane do każdego z nich). Jak wspomniałem przed chwilą, kursor w dalszym ciągu znajduje się w komórce A6, a zmienna intRow (w której zachowany jest numer komórki aktywnej) dalej ma wartość 6. Dlatego też, za pomocą poniższego wiersza kodu możemy przypisać wartość do pierwszego pustego wiersza:

objExcel.Cells(intRow, 1).Value = i

Wartość wiersza komórki 6 (zmienna intRow) w kolumnie 1 zostaje wstawiona do zmiennej licznika i. Zwiększamy wartość zmiennej intRow o 1 (to daje nam wartość 7), kontynuujemy działanie pętli i przypisujemy wartość do wiersza komórki 7 w kolumnie 1. Ten proces trwa, dopóki nie przypiszemy wartości do każdego pustego wiersza wstawionego do arkusza.

I to już koniec naszej pracy.

Zaraz, zaraz: prawie koniec. Pozostała nam jeszcze jedna kwestia: chociaż przesunęliśmy naszą formułę w dół do komórki A16, w dalszym ciągu wygląda ona tak:

=SUM(A1:A5)

Niestety, podczas programistycznego wstawiania komórek do arkusza, formuły odnoszące się do tych arkuszy nie aktualizują się automatycznie.

Jak naprawimy ten problem? No cóż, nie ma jednego polecenia (np. UpdateAllFormulas), które mogło by to za nas załatwić, więc lepiej jest zastąpić formułę, niż ją uaktualniać. Do tego służy poniższy wiersz kodu:

strFormula = "=SUM(A1:A" & intRow - 1 & ")"

objExcel.Cells(intRow, 1).Formula = strFormula

Za pomocą pierwszego wiersza tworzymy zaktualizowaną formułę podsumowującą komórki od A1 do – jak to było dokładnie? W tym przypadku do komórki A15. Dlaczego? Ponieważ od wartości zmiennej intRow (aktualnie 16) odejmujemy 1, co nam daje wiersz 15. Innymi słowy, wartość naszej zmiennej ciągu strFormula będzie wyglądała następująco:

=SUM(A1:A15)

Za pomocą wiersza 2 przypisujemy tę nową formułę do właściwości Formula wiersza komórki 16 (wartość zmiennej intRow) w kolumnie 1.

I w tym momencie to naprawdę koniec naszej pracy.

Najlepsze w tym jest to, że przy następnym dodaniu danych do tego arkusza cała zabawa rozpocznie się od wiersza A16, który teraz zawiera formułę.

A teraz, zgodnie z obietnicą, skrypt wykorzystujący tylko jedną pętlę For Next. Tak na wypadek, gdyby ktoś był zainteresowany:

Const xlDown = -4121



Set objExcel = CreateObject("Excel.Application")

objExcel.Visible = True



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

Set objWorksheet = objWorkbook.Worksheets(1)



Set objRange = objExcel.Range("A1")

objRange.End(xlDown).Activate



intRow = objExcel.ActiveCell.Row

intColumn = objExcel.ActiveCell.Column

Set objRange = objWorksheet.Cells(intRow, intColumn).EntireRow



For i = 1 to 10

    objRange.Insert(xlShiftDown)

    objExcel.Cells(intRow, 1).Value = i

    intRow = intRow + 1

Next



strFormula = "=SUM(A1:A" & intRow - 1 & ")"

objExcel.Cells(intRow, 1).Formula = strFormula

To powinno wystarczyć, AW, a nawet musi, bo ktoś przecież powinien załadować te wszystkie rzeczy do samochodu, a wszyscy jakoś dziwnym trafem nagle poznikali i zostałem sam na placu boju.

Do początku strony