Overweeg een situatie waarin u dagelijks een aantal Excel-bestanden tegenkomt en u wilt een snel mechanisme om u te helpen het aantal werkbladen in elke werkmap te vinden. Als je een soortgelijk probleem hebt, mag je dit artikel niet missen, want het zal je veel helpen.
In dit artikel leren we hoe u werkbladen in meerdere bestanden met VBA-code kunt tellen.
Vraag:Ik heb een macro nodig die een lijst met bestandsnamen kan lezen en het aantal werkbladen kan retourneren dat aanwezig is in elk van de bestanden (dit is een controlemechanisme om ervoor te zorgen dat het juiste aantal werkbladen aanwezig is in een reeks bestanden die zijn gemaakt via een ander proces). De macro zou het pad moeten bepalen van de map waar de bestanden zich bevinden (allemaal in dezelfde map), vervolgens het eerste bestand lokaliseren, het aantal werkbladen identificeren en herhalen voor het volgende bestand, enz.
Ik dacht dat ik dit met een formule kon doen door simpelweg naar de bestandsnamen te verwijzen, maar ik geloof dat Excel geen eenvoudige formule heeft voor het tellen van werkbladen. Bedankt!
Als je de originele vraag wilt lezen, klik dan hier
Hieronder volgt een momentopname van bestanden die zijn opgeslagen in een map met de extensie .xlsx
Opmerking: er zijn geen met een wachtwoord beveiligde bestanden.
Om de code te krijgen, moeten we de onderstaande stappen volgen om de VB-editor te starten:
- Klik op het tabblad Ontwikkelaar
- Selecteer in de codegroep Visual Basic
- Kopieer onderstaande code in de standaard 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 Werkblad ' Mappad waar de werkmappen zich bevinden. WkbPath = "C:\Users\Test" ' Naam van het werkblad met de werkmaplijst. Set Wks = Worksheets("Blad1") ' Startcel van werkmaplijst. Set Rng = Wks.Range("A2") ' Haal het celbereik op in de lijst met werkmapnamen. Set RngEnd = Wks.Cells(Rows.Count, Rng.Column).End(xlUp) If RngEnd.Row >= Rng.Row Set Rng = Wks.Range(Rng, RngEnd) ' Maak de benodigde ADO-objecten voor deze macro . Set Conn = CreateObject("ADODB.Connection") Set Cat = CreateObject("ADOX.Catalog") ' Voeg indien nodig een laatste backslash toe aan het pad. WkbPath = IIf(Right(WkbPath, 1) "\", WkbPath & "\", WkbPath) ' Doorloop elke cel in de werkmaplijst. Voor elke cel in Rng ' Haal het aantal werkbladen voor de werkmap op. ConnStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" _ & WkbPath & Cell _ & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;IMEX=1;""" Conn.Open ConnStr Set Cat.ActiveConnection = Conn ' Kopieer de telling naar de cel één kolom rechts van de naam van de werkmap in de lijst. Cell.Offset(n, 1) = Cat.Tables.Count Conn.Close Next Cell 'Opruimen. Set Cat = Niets Set Conn = Niets Einde Sub
- Terwijl we de macro uitvoeren, krijgen we het aantal werkbladen. Zie onderstaande momentopname:
Opmerking: de bovenstaande macro werkt voor de extensie .xlsx & .xls en niet voor de extensie .xlsm Macro.
- Alle bovenstaande bestanden hebben de extensie .xlsx
- Laten we een dummy Excel-blad toevoegen, d.w.z. Blad 10
- Als we een bestand hebben met dezelfde naam met de extensies .xlsx & .xls, dan moeten we de naam van het bestand met hun respectievelijke extensies ook in ons testbestand (kolom A) vermelden, zodat de macro het bestand kan identificeren dat we zijn verwijzend naar & geef ons het juiste resultaat
- Als we de extensie voor het bestand met dezelfde naam niet hebben genoemd of hebben gemist, geeft de macro ons het aantal .xlsx-extensies. Zie onderstaande momentopname:
- Om het aantal bladen voor Blad 10 met .xlsx & .xls extensies te krijgen, moeten we de bestandsnaam vermelden met hun respectievelijke extensies
De momentopname van de uiteindelijke uitvoer wordt hieronder weergegeven:
Conclusie: Met behulp van bovenstaande macrocode kunnen we het aantal werkbladen in meerdere bestanden tellen en indien nodig om het aangepaste resultaat te krijgen, kunnen we een klein beetje wijzigingen aanbrengen in VBA-code.
Als je onze blogs leuk vond, deel deze dan met je vrienden op Facebook. En je kunt ons ook volgen op Twitter en Facebook.
We horen graag van je, laat ons weten hoe we ons werk kunnen verbeteren, aanvullen of vernieuwen en het voor jou beter kunnen maken. Schrijf ons op de e-mailsite