Macro de tabla de contenido: consejos de Excel

Gracias a Matt, quien envió la pregunta de Excel de esta semana:

Tengo un libro de Excel grande y en crecimiento (muchas hojas). He incluido números de página en el pie de página mientras imprimía, sin embargo, cada vez es más difícil navegar cuando estamos en una reunión. ¿Hay alguna manera de imprimir una tabla de contenido basada en los nombres de las hojas de cálculo de Excel para que yo y el personal podamos pasar rápidamente a la página #xx?

Esta es una gran idea. La primera sugerencia simple es incluir el nombre de la hoja en los pies de página de su impresión. Cuando hace clic en "Pie de página personalizado" en el cuadro de diálogo Configurar página / Pie de página de encabezado, hay 7 iconos. El icono de la derecha parece una tarjeta de índice con tres pestañas. Al hacer clic en el cuadro de la sección Derecha: y presionar ese icono, el nombre de la hoja se imprimirá en cada hoja. Esto solo puede ayudar a navegar por el informe.

A MrExcel le gusta la idea de tener una macro para crear la tabla de contenido. El principal problema es que Excel no calcula cuántas páginas impresas hay en una hoja de trabajo hasta que no hace una vista previa de impresión. Entonces, la macro le permite al usuario saber que está a punto de ver una Vista previa de impresión y le pide que la descarte con un clic en el botón de cierre.

La macro recorre cada hoja del libro. En su estado actual, recopila información del nombre de cada hoja de trabajo. También he incluido otras dos líneas que están comentadas. Si prefiere obtener la descripción del encabezado izquierdo o de un título en la celda A1, también hay líneas de muestra para hacer cualquiera de esos. Simplemente descomente el que desea usar.

La macro calcula cuántas páginas sumando una al número de saltos de página horizontales (HPageBreaks.count). Agrega uno al número de saltos de página verticales (VPageBreaks.Count). Multiplica estos dos números para calcular el número de páginas de esa hoja de trabajo. Si algún lector leal tiene una mejor manera de hacer esto, hágamelo saber. El método actual de contar los saltos de página es endiabladamente lento. Parece que no pude encontrar una propiedad que me diga cuántas páginas impresas hay, pero pensaría que Excel incluiría una.

El último truco fue ingresar al rango de páginas. Si una hoja estuviera en las páginas "3 - 4", Excel lo trataría como una fecha e ingresaría el 4 de marzo. Al establecer el formato de celda en texto con el carácter "@", las páginas ingresan correctamente.

Aquí está 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

A continuación se muestra una macro equivalente, actualizada con varias nuevas técnicas 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 breve resumen de las nuevas técnicas de macro en la macro más reciente:

  • Rara vez es necesario seleccionar una hoja
  • En lugar de recorrer cada hoja del libro en busca de una hoja llamada Tabla de contenido, la segunda macro simplemente asume que está allí y verifica el estado de la variable Err. Si Err es diferente a 0, sabemos que la hoja no existe y debe agregarse.
  • WST es una variable de objeto y se define como la hoja de trabajo de la Tabla de contenido. Por lo tanto, cualquier referencia a las hojas de trabajo ("Tabla de contenido"). se puede reemplazar con WST.
  • La construcción Cells (row, column) es más eficiente que el kluge de Range ("A" & TOCRow). Debido a que Cells () espera parámetros numéricos, Range ("A" & TOCRow) se convierte en celdas (TOCRow, 1)
  • Los corchetes se utilizan como una forma abreviada de referirse al rango ("A1").

Articulos interesantes...