Macro de table des matières - Astuces Excel

Merci à Matt qui a envoyé la question Excel de cette semaine:

J'ai un classeur Excel volumineux et en croissance (beaucoup de feuilles). J'ai inclus les numéros de page dans le pied de page lors de l'impression, mais il devient de plus en plus difficile de naviguer lorsque nous sommes en réunion. Existe-t-il un moyen d'imprimer une table des matières basée sur les noms de feuille de calcul Excel afin que le personnel et moi-même puissions passer rapidement à la page #xx?

C'est une bonne idée. La première suggestion simple consiste à inclure le nom de la feuille dans les pieds de page de votre impression. Lorsque vous cliquez sur "Pied de page personnalisé" dans la boîte de dialogue Mise en page / Pied de page d'en-tête, il y a 7 icônes. L'icône la plus à droite ressemble à une fiche avec trois onglets. En cliquant dans la zone de droite: et en appuyant sur cette icône, le nom de la feuille s'imprimera sur chaque feuille. Cela seul peut aider à naviguer dans le rapport.

MrExcel aime l'idée d'avoir une macro pour créer la table des matières. Le problème principal est qu'Excel ne calcule pas le nombre de pages imprimées dans une feuille de calcul tant que vous n'avez pas effectué un aperçu avant impression. Ainsi, la macro informe l'utilisateur qu'il est sur le point de voir un aperçu avant impression et lui demande de le rejeter en cliquant sur le bouton de fermeture.

La macro parcourt chaque feuille du classeur. Dans son état actuel, il collecte des informations à partir du nom de chaque feuille de calcul. J'ai également inclus deux autres lignes qui sont commentées. Si vous préférez obtenir la description à partir de l'en-tête gauche ou d'un titre dans la cellule A1, il existe des exemples de lignes pour faire l'un ou l'autre. Décommentez simplement celui que vous souhaitez utiliser.

La macro calcule le nombre de pages en ajoutant un au nombre de sauts de page horizontaux (HPageBreaks.count). Il ajoute un au nombre de sauts de page verticaux (VPageBreaks.Count). Il multiplie ces deux nombres ensemble pour calculer le nombre de pages sur cette feuille de calcul. Si des lecteurs fidèles ont une meilleure façon de le faire, faites-le moi savoir. La méthode actuelle de comptage des sauts de page est diaboliquement lente. Je n'arrive pas à trouver une propriété qui m'indique le nombre de pages imprimées, mais on pourrait penser qu'Excel en inclurait une.

La dernière astuce était d'entrer dans la plage de pages. Si une feuille se trouvait sur les pages «3 à 4», Excel la traiterait comme une date et entrerait le 4 mars. En définissant le format de cellule sur du texte avec le caractère «@», les pages entrent correctement.

Voici la macro:

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

Voici une macro équivalente, mise à jour avec plusieurs nouvelles techniques de macro.

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

Un bref résumé des nouvelles techniques de macro dans la nouvelle macro:

  • Il est rarement nécessaire de sélectionner une feuille
  • Plutôt que de parcourir chaque feuille du classeur à la recherche d'une feuille appelée Table des matières, la deuxième macro suppose simplement qu'elle est là et vérifie l'état de la variable Err. Si Err est autre chose que 0, nous savons que la feuille n'existe pas et doit être ajoutée.
  • WST est une variable objet et est défini comme étant la feuille de calcul Table des matières. Ainsi, toute référence à des feuilles de calcul («Table des matières»). peut être remplacé par WST.
  • La construction Cells (ligne, colonne) est plus efficace que la kluge de Range ("A" & TOCRow). Étant donné que Cells () attend des paramètres numériques, Range ("A" & TOCRow) devient des cellules (TOCRow, 1)
  • Les crochets sont utilisés comme une manière abrégée de se référer à Range ("A1").

Articles intéressants...