Innehållsförteckning Makro - Excel-tips

Tack till Matt som skickade veckans Excel-fråga:

Jag har en stor och växande Excel-arbetsbok (många ark). Jag har inkluderat sidnummer i sidfoten under utskrift, men det blir svårare och svårare att navigera när vi är i ett möte. Finns det ett sätt att skriva ut en innehållsförteckning baserat på Excel-kalkylarknamn så att jag och personalen snabbt kan gå till sidan #xx?

Det här är en bra idé. Det första enkla förslaget är att inkludera arkets namn i sidfoten på din utskrift. När du klickar på "Anpassad sidfot" i dialogrutan Utskriftsformat / sidhuvudfot finns det 7 ikoner. Ikonen längst till höger ser ut som ett indexkort med tre flikar. Om du klickar i högeravsnittet: och trycker på ikonen kommer arkets namn att skrivas ut på varje ark. Detta ensamt kan hjälpa till att navigera genom rapporten.

MrExcel gillar tanken på att ha ett makro för att skapa innehållsförteckningen. Huvudproblemet är att Excel inte beräknar hur många utskrivna sidor som finns på ett kalkylblad förrän du gör en förhandsgranskning. Så, makrot låter användaren veta att de håller på att se en utskriftsförhandsgranskning och ber dem att avvisa den med ett klick på stängningsknappen.

Makrot slingrar sig genom varje ark i arbetsboken. I sitt nuvarande tillstånd samlar den information från namnet på varje kalkylblad. Jag har också tagit med två andra rader som kommenteras. Om du hellre vill få beskrivningen från vänster rubrik eller från en titel i cell A1, finns det exempelrader att göra någon av dessa också. Bara avmarkera den du vill använda.

Makrot beräknar hur många sidor genom att lägga till en till antalet horisontella sidbrytningar (HPageBreaks.count). Det lägger till en till antalet vertikala sidbrytningar (VPageBreaks.Count). Det multiplicerar dessa två siffror tillsammans för att beräkna antalet sidor på det kalkylbladet. Om några lojala läsare har ett bättre sätt att göra detta, vänligen meddela mig. Den nuvarande metoden för att räkna sidbrytningarna är djävulskt långsam. Det gick inte att hitta en egendom som berättar för mig hur många utskrivna sidor det finns, men man skulle tro att Excel skulle innehålla en.

Det sista tricket var att komma in i sidintervallet. Om ett ark fanns på sidorna "3 - 4" behandlade Excel detta som ett datum och ange den 4 mars. Genom att ställa in cellformatet till text med tecknet "@" kommer sidorna in korrekt.

Här är makrot:

Sub CreateTableOfContents() ' Copyright 1999.com ' Determine if there is already a Table of Contents TOCFound = False For Each s In Worksheets If s.Name = "Table of Contents" Then TOCFound = True Exit For End If Next s If Not TOCFound Then Sheets.Add Before:=Worksheets(1) ActiveSheet.Name = "Table of Contents" End If ' Set up the table of contents page TOCRow = 7 PageCount = 0 Sheets("Table of Contents").Select Range("A2").Value = "Table of Contents" Range("A6").CurrentRegion.Clear Range("A6").Value = "Subject" Range("A6").ColumnWidth = 36 Range("B6").Value = "Page(s)" Range("B6").ColumnWidth = 12 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Worksheets.Select Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." Msgbox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information For Each s In Worksheets s.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("Table of Contents").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 Next s End Sub

Nedan följer ett motsvarande makro, uppdaterat med flera nya makrotekniker.

Sub CreateTableOfContents() ' Copyright 2002.com ' Determine if there is already a Table of Contents ' Assume it is there, and if it is not, it will raise an error ' if the Err system variable is> 0, you know the sheet is not there Dim WST As Worksheet On Error Resume Next Set WST = Worksheets("Table of Contents") If Not Err = 0 Then ' The Table of contents doesn't exist. Add it Set WST = Worksheets.Add(Before:=Worksheets(1)) WST.Name = "TOC" End If On Error GoTo 0 ' Set up the table of contents page WST.(A2) = "Table of Contents" With WST.(A6) .CurrentRegion.Clear .Value = "Subject" End With WST.(B6) = "Page(s)" WST.Range("A1:B1").ColumnWidth = Array(36, 12) TOCRow = 7 PageCount = 0 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." MsgBox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information ' Loop through each sheet, collecting TOC information For Each S In Worksheets If S.Visible = -1 Then S.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("TOC").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 End If Next S End Sub

En kort sammanfattning av de nya makroteknikerna i det nyare makrot:

  • Det är sällan nödvändigt att välja ett ark
  • I stället för att slinga igenom varje ark i arbetsboken och leta efter ett ark som heter Innehållsförteckning antar det andra makrot helt enkelt att det finns där och kontrollerar status för Err-variabeln. Om Err är något annat än 0 vet vi att arket inte finns och måste läggas till.
  • WST är en objektvariabel och definieras som arbetsbladet Innehållsförteckning. Således, varje hänvisning till arbetsblad ("Innehållsförteckning"). kan ersättas med WST.
  • Cells (rad, kolumn) konstruktion är effektivare än kluge av Range ("A" & TOCRow). Eftersom Cells () förväntar sig numeriska parametrar blir Range ("A" & TOCRow) celler (TOCRow, 1)
  • De hakparenteserna används som ett kortfattat sätt att hänvisa till Range ("A1").

Intressanta artiklar...