Inhaltsverzeichnis Makro - Excel-Tipps

Vielen Dank an Matt, der diese Woche die Excel-Frage gesendet hat:

Ich habe eine große und wachsende Excel-Arbeitsmappe (viele Blätter). Ich habe beim Drucken Seitenzahlen in die Fußzeile aufgenommen, es wird jedoch immer schwieriger, in einer Besprechung zu navigieren. Gibt es eine Möglichkeit, ein Inhaltsverzeichnis basierend auf Excel-Arbeitsblattnamen zu drucken, damit ich und die Mitarbeiter schnell zur Seite #xx wechseln können?

Das ist eine großartige Idee. Der erste einfache Vorschlag besteht darin, den Blattnamen in die Fußzeilen Ihres Ausdrucks aufzunehmen. Wenn Sie im Dialogfeld "Seite einrichten / Kopfzeile" auf "Benutzerdefinierte Fußzeile" klicken, werden 7 Symbole angezeigt. Das Symbol ganz rechts sieht aus wie eine Karteikarte mit drei Registerkarten. Wenn Sie in das Feld Rechts: klicken und auf dieses Symbol klicken, wird der Blattname auf jedem Blatt gedruckt. Dies allein kann beim Navigieren durch den Bericht hilfreich sein.

MrExcel mag die Idee, ein Makro zum Erstellen des Inhaltsverzeichnisses zu haben. Das Hauptproblem besteht darin, dass Excel erst berechnet, wie viele gedruckte Seiten sich auf einem Arbeitsblatt befinden, wenn Sie eine Druckvorschau erstellen. Das Makro informiert den Benutzer also darüber, dass eine Druckvorschau angezeigt wird, und fordert ihn auf, diese mit einem Klick auf die Schaltfläche zum Schließen zu schließen.

Das Makro durchläuft jedes Blatt in der Arbeitsmappe. Im aktuellen Status werden Informationen aus dem Namen jedes Arbeitsblatts erfasst. Ich habe auch zwei andere Zeilen eingefügt, die auskommentiert sind. Wenn Sie die Beschreibung lieber aus der linken Kopfzeile oder aus einem Titel in Zelle A1 erhalten möchten, können Sie auch eine dieser Beispielzeilen ausführen. Kommentieren Sie einfach diejenige aus, die Sie verwenden möchten.

Das Makro berechnet die Anzahl der Seiten, indem es eine zur Anzahl der horizontalen Seitenumbrüche hinzufügt (HPageBreaks.count). Es erhöht die Anzahl der vertikalen Seitenumbrüche um eins (VPageBreaks.Count). Diese beiden Zahlen werden miteinander multipliziert, um die Anzahl der Seiten in diesem Arbeitsblatt zu berechnen. Wenn treue Leser einen besseren Weg haben, dies zu tun, lassen Sie es mich bitte wissen. Die derzeitige Methode zum Zählen der Seitenumbrüche ist teuflisch langsam. Ich konnte anscheinend keine Eigenschaft finden, die mir sagt, wie viele gedruckte Seiten es gibt, aber Sie würden denken, Excel würde eine enthalten.

Der letzte Trick war die Eingabe des Seitenbereichs. Wenn sich ein Blatt auf den Seiten "3 - 4" befindet, behandelt Excel dies als Datum und gibt den 4. März ein. Wenn Sie das Zellenformat auf Text mit dem Zeichen "@" einstellen, werden die Seiten ordnungsgemäß eingegeben.

Hier ist das Makro:

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

Unten finden Sie ein äquivalentes Makro, das mit mehreren neuen Makrotechniken aktualisiert wurde.

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

Eine kurze Zusammenfassung der neuen Makrotechniken im neueren Makro:

  • Es ist selten notwendig, ein Blatt auszuwählen
  • Anstatt jedes Blatt in der Arbeitsmappe nach einem Blatt mit dem Namen "Inhaltsverzeichnis" zu durchsuchen, geht das 2. Makro einfach davon aus, dass es vorhanden ist, und überprüft den Status der Err-Variablen. Wenn Err etwas anderes als 0 ist, wissen wir, dass das Blatt nicht vorhanden ist und hinzugefügt werden muss.
  • WST ist eine Objektvariable und wird als Arbeitsblatt zum Inhaltsverzeichnis definiert. Daher jeder Verweis auf Arbeitsblätter ("Inhaltsverzeichnis"). kann durch WST ersetzt werden.
  • Das Cells-Konstrukt (Zeile, Spalte) ist effizienter als die Kluge of Range ("A" & TOCRow). Da Cells () numerische Parameter erwartet, wird Range ("A" & TOCRow) zu Zellen (TOCRow, 1).
  • Die eckigen Klammern werden als Abkürzung für Range ("A1") verwendet.

Interessante Beiträge...