Tänk på en situation där du dagligen stöter på ett antal excel -filer och du vill ha en snabb mekanism för att hitta antalet arbetsblad som finns i varje arbetsbok. Om du har ett liknande problem får du inte missa den här artikeln för den kommer att hjälpa dig mycket.
I den här artikeln lär vi oss hur man räknar kalkylblad i flera filer med VBA -kod.
Fråga:Jag behöver makro som kan läsa igenom en lista med filnamn och returnera antalet kalkylblad som finns i var och en av filerna (detta är en granskningsmekanism för att säkerställa att rätt antal kalkylblad finns i en serie filer som skapas via en annan process). Makrot skulle behöva etablera sökvägen till mappen där filerna finns (alla i samma mapp), sedan hitta den första filen, identifiera kalkylbladets antal och upprepa för nästa fil etc.
Jag trodde att jag kunde göra detta med en formel genom att helt enkelt referera till filnamnen men jag tror att Excel inte har en rak framåt formel för kalkylblad. Tack!
Om du vill läsa den ursprungliga frågan, klicka här
Följande är ögonblicksbilden av filer som sparats i en mapp med filtillägget .xlsx
Obs! Det finns inga lösenordsskyddade filer.
För att få koden måste vi följa stegen nedan för att starta VB -editor:
- Klicka på fliken Utvecklare
- Välj Visual Basic i kodgruppen
- Kopiera koden nedan 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 'Mappväg där arbetsböckerna finns. WkbPath = "C: \ Users \ Test" 'Namn på kalkylblad med arbetsbokslistan. Ange Wks = Worksheets ("Sheet1") 'Startcell i arbetsbokslistan. Ställ in Rng = Wks.Range ("A2") 'Hämta cellintervallet i arbetsbokens namnlista. Ställ in RngEnd = Wks.Cells (Rows.Count, Rng.Column) .End (xlUp) Om RngEnd.Row> = Rng.Row Ställ sedan in Rng = Wks.Range (Rng, RngEnd) 'Skapa nödvändiga ADO -objekt från detta makro . Ställ in Conn = CreateObject ("ADODB.Connection") Set Cat = CreateObject ("ADOX.Catalog") 'Lägg till en sista backslash -vägen om det behövs. WkbPath = IIf (Right (WkbPath, 1) "\", WkbPath & "\", WkbPath) 'Gå igenom varje cell i arbetsbokslistan. För varje cell i Rng 'Hämta kalkylbladet för arbetsboken. ConnStr = "Provider = Microsoft.ACE.OLEDB.12.0; Datakälla =" _ & WkbPath & Cell _ & "; Utökade egenskaper =" "Excel 12.0 Xml; HDR = YES; IMEX = 1;" "" Conn.Open ConnStr Ställ in Cat.ActiveConnection = Conn 'Kopiera antalet till cellen en kolumn till höger om arbetsbokens namn i listan. Cell.Offset (n, 1) = Cat.Tables.Count Conn.Close Next Cell 'Rengör. Set Cat = Nothing Set Conn = Nothing End Sub
- När vi kör makrot får vi antalet kalkylblad. Se ögonblicksbilden nedan:
Obs! Makroet ovan fungerar för .xlsx & .xls -tillägget och inte för .xlsm makroaktiverat tillägg.
- Alla ovanstående filer är .xlsx -tillägg
- Låt oss lägga till dummy excel -ark, dvs. blad 10
- Om vi har en fil med samma namn som har .xlsx & .xls -tillägg, måste vi nämna namnet på filen med sina respektive tillägg också i vår testfil (kolumn A) så att makro kan identifiera filen vi är med hänvisning till & ge oss rätt resultat
- Om vi inte har nämnt eller missat att nämna tillägget för filen med samma namn, kommer makrot att ge oss räkningen av .xlsx -tillägget. Se ögonblicksbilden nedan:
- För att få antalet ark för Sheet 10 med .xlsx & .xls -tillägg måste vi nämna filnamnet med respektive tillägg
Ögonblicksbilden av den slutliga utmatningen visas nedan:
Slutsats: Med hjälp av makrokoden ovan kan vi räkna antalet kalkylblad i flera filer och om det behövs för att få det anpassade resultatet kan vi göra lite ändringar i VBA -koden.
Om du gillade våra bloggar, dela den med dina vänner på Facebook. Och du kan också följa oss på Twitter och Facebook.
Vi vill gärna höra från dig, låt oss veta hur vi kan förbättra, komplettera eller förnya vårt arbete och göra det bättre för dig. Skriv till oss på e -postwebbplatsen