Tenk på en situasjon der du støter på mange Excel -filer daglig, og du vil ha en rask mekanisme som hjelper deg med å finne antall regneark som finnes i hver arbeidsbok. Hvis du har et lignende problem, må du ikke gå glipp av denne artikkelen, for den vil hjelpe deg mye.
I denne artikkelen lærer vi hvordan du teller regneark i flere filer med VBA -kode.
Spørsmål:Jeg må makro som kan lese gjennom en liste over filnavn og returnere antall regneark som er tilstede i hver av filene (dette er en revisjonsmekanisme for å sikre at riktig antall regneark er tilstede i en serie filer som er opprettet via en annen prosess). Makroen må etablere banen til mappen der filene er plassert (alle i samme mappe), deretter finne den første filen, identifisere regnearket og gjenta for neste fil etc.
Jeg trodde jeg kunne gjøre dette med en formel ved ganske enkelt å referere til filnavnene, men jeg tror Excel ikke har en rett fram formel for regneark. Takk!
Hvis du vil lese det opprinnelige spørsmålet, klikk her
Følgende er øyeblikksbildet av filer som er lagret i en mappe med .xlsx -forlengelse
Merk: Det er ingen passordbeskyttede filer.
For å få koden må vi følge trinnene nedenfor for å starte VB -editor:
- Klikk på kategorien Utvikler
- Velg Visual Basic fra kodegruppen
- Kopier koden nedenfor i standardmodulen
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 'Mappebane der arbeidsbøkene ligger. WkbPath = "C: \ Users \ Test" 'Navn på regneark med arbeidsboklisten. Angi Wks = Worksheets ("Sheet1") 'Startcelle i arbeidsboklisten. Angi Rng = Wks.Range ("A2") 'Hent celleområdet i arbeidsbokens navneliste. Sett RngEnd = Wks.Cells (Rows.Count, Rng.Column) .End (xlUp) Hvis RngEnd.Row> = Rng.Row Sett deretter Rng = Wks.Range (Rng, RngEnd) 'Lag de nødvendige ADO -objektene fra denne makroen . Sett Conn = CreateObject ("ADODB.Connection") Sett Cat = CreateObject ("ADOX.Catalog") 'Legg til en siste backslash -banen om nødvendig. WkbPath = IIf (Høyre (WkbPath, 1) "\", WkbPath & "\", WkbPath) 'Gå gjennom hver celle i arbeidsboklisten. For hver celle i Rng 'Få regnearketallet for arbeidsboken. ConnStr = "Provider = Microsoft.ACE.OLEDB.12.0; Data Source =" _ & WkbPath & Cell _ & "; Extended Properties =" "Excel 12.0 Xml; HDR = YES; IMEX = 1;" "" Conn.Open ConnStr Angi Cat.ActiveConnection = Conn 'Kopier tellingen til cellen en kolonne til høyre for arbeidsboknavnet i listen. Cell.Offset (n, 1) = Cat.Tables.Count Conn.Close Next Cell 'Rydd opp. Sett Cat = Nothing Sett Conn = Nothing End Sub
- Når vi kjører makroen, får vi antall regneark. Se øyeblikksbildet nedenfor:
Merk: Makroen ovenfor fungerer for .xlsx og .xls -utvidelsen og ikke for .xlsm makroaktiverte utvidelser.
- Alle filene ovenfor er utvidelsen .xlsx
- La oss legge til dummy excel -ark, dvs. ark 10
- I tilfelle vi har en fil med samme navn som har .xlsx og .xls -utvidelser, må vi nevne navnet på filen med deres respektive utvidelser også i testfilen (kolonne A) slik at makroen kan identifisere filen vi er refererer til og gir oss det riktige resultatet
- Hvis vi ikke har nevnt eller savnet å nevne utvidelsen for filen med samme navn, vil makroen gi oss tellingen av .xlsx -utvidelsen. Se øyeblikksbildet nedenfor:
- For å få antall ark for ark 10 med .xlsx og .xls -utvidelser, må vi nevne filnavnet med de respektive utvidelsene
Øyeblikksbildet av den endelige utgangen er avbildet nedenfor:
Konklusjon: Ved å bruke makrokoden ovenfor kan vi telle antall regneark i flere filer, og hvis det er nødvendig for å få det tilpassede resultatet, kan vi gjøre en liten endring i VBA -koden.
Hvis du likte bloggene våre, del den med vennene dine på Facebook. Og du kan også følge oss på Twitter og Facebook.
Vi vil gjerne høre fra deg, gi oss beskjed om hvordan vi kan forbedre, utfylle eller innovere arbeidet vårt og gjøre det bedre for deg. Skriv til oss på e -post