Jak sprawdzić wartość określonej komórki w kilku arkuszach kalkulacyjnych programu Excel?

Skrypciarze odpowiadają na Wasze pytania

Jak sprawdzić wartość określonej komórki w kilku arkuszach kalkulacyjnych programu Excel?

Cześć, Skrypciarze! Jak utworzyć skrypt, który wyświetli listę wszystkich plików w katalogu, które mają określoną wartość w określonej komórce arkusza programu Excel? Czy można to zadanie połączyć z zastosowaniem funkcji „znajdź i zamień”?

-- RDC

Cześć, RDC. Udało nam się ostatnio opublikować część wyników różnych konkurencji Zimowej Olimpiady Skrypciarskiej 2008 (j.ang.) i często spotykamy się z tym, że ludzie patrzą na te wyniki z przerażeniem: 0 punktów w konkurencji 2?!? Jak to możliwe?

Jak się okazuje, można było to osiągnąć na wiele sposobów.

• Skrypt mógł po prostu nie zadziałać. Na przykład właściwą odpowiedzią w kategorii 2 jest czerwony, a skrypt uzyskał wynik niebieski. To daje 0 punktów.
• Może skrypt został uszkodzony podczas przesyłania. Otrzymaliśmy kilka skryptów, w których kilka ostatnich wierszy zostało uciętych. A ponieważ nie mieliśmy pojęcia, że jakieś wiersze zostały pominięte, te skrypty także dostały 0 punktów.
• A może wystąpił jakiś problem z ustawieniami konfiguracji. Przynajmniej jeden zawodnik dostał 0, ponieważ ustawienia języka i opcji regionalnych jego komputera różniły się od ustawień naszych komputerów. Jak bardzo? Wystarczająco, aby skrypt działający bezbłędnie na komputerze zawodnika działał zupełnie inaczej na komputerze Skrypciarzy. To trochę tak, jak prowadzić rozmowę ze Skrypciarzem Peterem Constatninim. On zawsze mówi to samo, niezależnie od tego, o co się go pyta.
• A może to po prostu nasza wina. Cóż, jeżeli już, to winna jest tylko Jean Ross. Nie pamiętamy, aby Skrypciarz Greg Stemp popełnił kiedykolwiek jakiś błąd.

W każdym razie powyższe sytuacje mogły spowodować przyznanie skryptowi 0 punktów. Ponadto można to zgonić na przeznaczenie, system poczty elektronicznej lub któregoś Skrypciarza. (Nie martw się Jean; nie podamy żadnych nazwisk.) Ale wiecie co? Otrzymanie 0 punktów to jeszcze nie koniec świata, przynamniej nie na Olimpiadzie Skrypciarskiej. W końcu jest coś takiego jak poprawki. Macie wątpliwości odnośnie do wyniku? Napiszcie do nas, a my powiemy Wam, co się działo podczas testowania skryptu. Jeżeli okaże się, że to nasza wina, niezwłocznie naprawimy swój błąd. Jeżeli z kolei okaże się, że to zawodnik popełnił błąd, będzie szansa jego naprawienia i ponownego przesłania skryptu. Tak, będzie można to zrobić, mimo że termin odsyłania już minął. To się nazywa poprawka.

Miło byłoby, gdyby tak poprawiało się wszystkie egzaminy na studiach.

To całkiem uczciwe podejście. Przecież w Olimpiadzie Skrypciarskiej chodzi głównie o zabawę oraz o zdobycie przy tym pewnej wiedzy. Jeżeli jesteście w stanie naprawić błędny skrypt, oznacza to, że czegoś się nauczyliście. I dopilnujemy, żebyście dostali za to nagrodę.

Innymi słowy – dajcie znać, jeżeli macie pytanie, dotyczące Waszego wyniku. Odpowiedź na nie może nam zająć kilka dni, ale na pewno odpowiemy. I damy Wam możliwość naprawienia problemu.

A co, jeżeli ktoś nie ma żadnych problemów i wszystko jest tak, jak powinno? Cóż, w takim przypadku może warto nauczyć się czegoś nowego i zapoznać ze skryptem, który sprawdza wszystkie pliki programu Microsoft Excel znajdujące się w folderze i informuje nas, które pliki mają określoną wartość w określonej komórce:

strComputer = "atl-fs-001"



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



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



Set FileList = objWMIService.ExecQuery _

    ("ASSOCIATORS OF {Win32_Directory.Name='c:\Temp'} Where " _

        & "ResultClass = CIM_DataFile")



For Each objFile In FileList

    If objFile.Extension = "xls" Then

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

        Set objWorksheet = objWorkbook.Worksheets(1)

        If objExcel.Cells(4,1) = "Test" Then

            Wscript.Echo objFile.FileName

        End If

        objWorkbook.Close

    End If

Next



objExcel.Quit

Zobaczmy, jak ten skrypt działa. Jak widać, zaczynamy od przypisania do zmiennej o nazwie strComputer nazwy komputera, na którym są zapisane pliki programu Excel. Zazwyczaj po tej czynności do zmiennej strComputer przypisujemy kropkę (reprezentującą komputer lokalny):

strComputer = "."

Dlaczego więc nie przypisujemy kropki do zmiennej strComputer w tym skrypcie? Cóż, kropka naprawdę reprezentuje komputer lokalny, ale tylko wtedy, gdy używamy usługi WMI. W tym skrypcie użyjemy kodu WMI, ale użyjemy także kodu programu Excel. Program Excel nie ma pojęcia o tym, że kropka powinna reprezentować komputer lokalny; jeżeli tym razem zastosujemy strComputer = “.”, wystąpi błąd skryptu w momencie, gdy program Excel będzie próbował zlokalizować komputer o nazwie kropka (.). Jaka płynie z tego mądrość? Żeby uniknąć błędu skryptu należy się upewnić, że zmienna strComputer ma przypisaną nazwę komputera nawet wtedy, gdy skrypt jest uruchamiany na komputerze lokalnym.

No i nie rób drugiemu, co tobie niemiłe. To też ważna, życiowa mądrość.

Po przypisaniu wartości do zmiennej strComputer używamy poniższego wiersza kodu w celu utworzenia wystąpienia obiektu Excel.Application:

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

Przyznaję, w tym miejscu zazwyczaj używaliśmy polecenia CreateObject, które wygląda tak:

Set objExcel = CreateObject("Excel.Application")

To polecenie tworzy wystąpienie programu Excel na komputerze lokalnym. Polecenie, którego użyliśmy, razem z opcjonalnym drugim parametrem tworzy wystąpienie programu Excel na komputerze, którego nazwa jest zachowana w zmiennej strComputer. Tak, to mógłby być komputer lokalny lub zdalny.

Dwie uwagi na ten temat. Po pierwsze, tego skryptu nie można uruchomić na komputerze zdalnym, jeżeli nie ma on zainstalowanego programu Excel. Po drugie zauważycie, że nie ustawiamy wartości właściwości Visible jako True, co zwykle służyło uzyskaniu wystąpienia programu Excel widocznego na ekranie. Dlaczego teraz tego nie robimy? Cóż, gdybyśmy chcieli uruchomić skrypt wyłącznie lokalnie, moglibyśmy tak zrobić. (Chociaż każdy plik otwiera się i zamyka tak szybko, że może nie ma to w ogóle sensu.) Jednakże jeżeli uruchomimy ten skrypt zdalnie, wystąpienie programu Excel nie będzie widoczne na ekranie; ma to związek z zabezpieczeniami. Każdy proces uruchamiany na komputerze zdalnym działa zawsze w ukrytym oknie. Dlatego postanowiłem opuścić cały ten fragment na temat widoczności programu Excel na ekranie.

Po uruchomieniu programu Excel łączymy się z usługą WMI na komputerze docelowym, a następnie używamy poniższego wiersza kodu w celu pobrania kolekcji wszystkich plików znajdujących się w folderze C:\Temp:

Set FileList = objWMIService.ExecQuery _

    ("ASSOCIATORS OF {Win32_Directory.Name='c:\Temp'} Where " _

        & "ResultClass = CIM_DataFile")

Po uzyskaniu kolekcji uruchamiamy pętlę For Each, która przejdzie przez każdy plik w folderze C:\Temp. Dla każdego z tych plików najpierw sprawdzamy, czy mamy do czynienia z plikiem Excel; czyli czy mamy do czynienia z plikiem o rozszerzeniu .XLS:

If objFile.Extension = "xls" Then

Jeżeli plik nie ma rozszerzenia .XLS, wracamy na początek pętli i próbujemy z następnym plikiem w kolekcji. Jeżeli plik ma rozszerzenie .XLS, robimy masę fajnych rzeczy.

Na przykład jakich? Cóż, na początek używamy poniższego wiersza kodu oraz metody Open programu Excel w celu otwarcia danego pliku:

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

Pamiętajcie, jeżeli uruchamiamy skrypt na komputerze zdalnym, właściwość Name reprezentuje ścieżkę na tym zdalnym komputerze. Innymi słowy jest to plik C:\Temp\File1.xls na komputerze atl-fs-001.

Po otwarciu pliku używamy następującego wiersza kodu w celu połączenia się z arkuszem w tym pliku:

Set objWorksheet = objWorkbook.Worksheets(1)

W tym momencie sprawdzamy, czy wartość komórki A4 (czyli wiersz 4 kolumna 1) odpowiada docelowej wartości Test:

If objExcel.Cells(4,1) = "Test" Then

Jeżeli tak jest, wywołamy echo nazwy (FileName) pliku. Jeżeli nie, wracamy tylko na początek pętli i spróbujemy ponownie z następnym plikiem w kolekcji.

Kiedy skończymy, powinniśmy otrzymać raport dotyczący wszystkich plików, w których komórka A4 ma wartość Test:

file1

file3

file4

Nieźle, prawda?

Przejdźmy teraz do drugiej części pytania RDC, dotyczącej funkcji „znajdź i zamień”. Prawdę mówiąc nie wiem dokładnie, o co chodzi; domyślam się, że RDC chce zmienić wartość komórki A4. (Jeżeli nie o to, daj nam znać!) Poniższy skrypt sprawdzi, czy wartość komórki A4 we wszystkich arkuszach w pliku to Test; jeżeli tak jest, skrypt zmieni wartość komórki A4 na Modified.

Oto ten kod:

strComputer = "atl-fs-001"



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



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



Set FileList = objWMIService.ExecQuery _

    ("ASSOCIATORS OF {Win32_Directory.Name='c:\Temp'} Where " _

        & "ResultClass = CIM_DataFile")



For Each objFile In FileList

    If objFile.Extension = "xls" Then

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

        Set objWorksheet = objWorkbook.Worksheets(1)

        If objExcel.Cells(4,1) = "Test" Then

            Wscript.Echo objFile.FileName 

            objExcel.Cells(4,1) = "Modified"

        End If

        objWorkbook.Save

        objWorkbook.Close

    End If

Next



objExcel.Quit

Wprowadziłem tu tylko kilka zmian. Po pierwsze, po wywołaniu echa nazwy pliku zawierającego komórkę A4 o wartości Test używamy poniższego wiersza pliku w celu zmiany wartości komórki na Modified:

objExcel.Cells(4,1) = "Modified"

Potem przed zamknięciem pliku używamy metody Save w celu zapisania tej zmiany:

objWorkbook.Save

I to naprawdę wszystko.

Dobrze się składa, że to już koniec, ponieważ czas mnie goni; mam mnóstwo skryptów do sprawdzenia i muszę odpowiedzieć na wiele wiadomości e-mail. Pamiętajcie, jeżeli macie pytanie dotyczące Waszego wyniku; dajcie znać, może da się coś z tym zrobić.

Do początku strony