Rozważ sytuację, w której codziennie napotykasz liczbę plików Excela i potrzebujesz szybkiego mechanizmu, który pomoże Ci znaleźć liczbę arkuszy w każdym skoroszycie. Jeśli masz podobny problem, nie możesz przegapić tego artykułu, ponieważ bardzo ci pomoże.
W tym artykule dowiemy się, jak liczyć arkusze robocze w wielu plikach z kodem VBA.
Pytanie:Potrzebuję makra, które potrafi czytać listę nazw plików i zwracaćliczbęarkuszy znajdujących sięw każdym z plików (jest to mechanizm audytu, aby upewnić się,że w serii plików utworzonych przez inny proces). Makro musiałoby ustalić ścieżkę folderu, w którym znajdują się pliki (wszystkie w tym samym folderze), a następnie zlokalizować pierwszy plik, zidentyfikować liczbę arkuszy i powtórzyć dla następnego pliku itp.
Pomyślałem, że mogę to zrobić za pomocą formuły, po prostu odwołując się do nazw plików, ale uważam, że Excel nie ma prostej formuły na liczbę arkuszy. Dziękuję!
Jeśli chcesz przeczytać oryginalne pytanie, kliknij tutaj
Poniżej znajduje się migawka plików zapisanych w folderze z rozszerzeniem .xlsx
Uwaga: nie ma plików chronionych hasłem.
Aby uzyskać kod, musimy wykonać poniższe kroki, aby uruchomić edytor VB:
- Kliknij kartę Deweloper
- Z grupy Kod wybierz Visual Basic
- Skopiuj poniższy kod w standardowym module
Sub ListSheetCounts() Dim Cell As Range Dim Conn As Object Dim Cat As Object Dim ConnStr As String Dim n As Long Dim Rng As Range Dim RngEnd As Range Dim WkbPath As Variant Dim Wks As Worksheet ' Ścieżka folderu, w którym znajdują się skoroszyty. WkbPath = "C:\Users\Test" ' Nazwa arkusza z listą skoroszytów. Set Wks = Worksheets("Sheet1") ' Początkowa komórka listy skoroszytów. Set Rng = Wks.Range("A2") ' Pobierz zakres komórek z listy nazw skoroszytu. Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp) If RngEnd.Row >= Rng.Row Then Set Rng = Wks.Range(Rng, RngEnd) ' Utwórz potrzebne obiekty ADO z tego makra . Set Conn = CreateObject("ADODB.Connection") Set Cat = CreateObject("ADOX.Catalog") ' W razie potrzeby dodaj końcowy ukośnik odwrotny do ścieżki. WkbPath = IIf(Right(WkbPath, 1) "\", WkbPath & "\", WkbPath) ' Przejdź przez każdą komórkę na liście skoroszytu. For Each Cell In Rng ' Uzyskaj liczbę arkuszy dla skoroszytu. ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Źródło danych=" _ & WkbPath & Cell _ & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""" Conn.Open ConnStr Set Cat.ActiveConnection = Conn ' Skopiuj licznik do komórki o jedną kolumnę po prawej stronie nazwy skoroszytu na liście. Cell.Offset(n, 1) = Cat.Tables.Count Conn.Close Następna komórka ' Oczyść. Set Cat = Nic Set Conn = Nic Koniec Sub
- Gdy uruchomimy makro, otrzymamy liczbę arkuszy roboczych. Zapoznaj się z poniższym zrzutem ekranu:
Uwaga: powyższe makro będzie działać dla rozszerzeń .xlsx i .xls, a nie dla rozszerzeń .xlsm obsługujących makra.
- Wszystkie powyższe pliki mają rozszerzenie .xlsx
- Dodajmy atrapę arkusza Excela, czyli Arkusz 10
- W przypadku, gdy mamy plik o tej samej nazwie, który ma rozszerzenia .xlsx i .xls, musimy podać nazwę pliku wraz z odpowiednimi rozszerzeniami również w naszym pliku testowym (kolumna A), aby makro mogło zidentyfikować plik, którym jesteśmy odnosząc się do i daj nam poprawny wynik
- Jeśli nie wspomnieliśmy lub przeoczyliśmy rozszerzenie dla pliku o tej samej nazwie, makro poda nam liczbę rozszerzeń .xlsx. Zapoznaj się z poniższym zrzutem ekranu:
- Aby uzyskać liczbę arkuszy dla arkusza 10 z rozszerzeniami .xlsx i .xls, musimy podać nazwę pliku z odpowiednimi rozszerzeniami
Migawkę końcowego wyniku przedstawiono poniżej:
Wniosek: Korzystając z powyższego kodu makra, możemy policzyć liczbę arkuszy roboczych w wielu plikach, a jeśli jest to wymagane, aby uzyskać niestandardowy wynik, możemy dokonać niewielkiej modyfikacji w kodzie VBA.
Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku.
Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail