Tabella dei contenuti Macro - Suggerimenti per Excel

Grazie a Matt che ha inviato la domanda su Excel di questa settimana:

Ho una cartella di lavoro Excel ampia e in crescita (molti fogli). Ho incluso i numeri di pagina nel piè di pagina durante la stampa, tuttavia è sempre più difficile navigare quando siamo in una riunione. Esiste un modo per stampare un sommario basato sui nomi dei fogli di lavoro di Excel in modo che io e lo staff possiamo passare rapidamente alla pagina #xx?

Questa è una grande idea. Il primo semplice suggerimento è includere il nome del foglio nei piè di pagina della stampa. Quando si fa clic su "Piè di pagina personalizzato" nella finestra di dialogo Imposta pagina / Piè di pagina intestazione, sono presenti 7 icone. L'icona più a destra ha l'aspetto di una scheda con tre schede. Facendo clic nella casella di destra: e premendo quell'icona, il nome del foglio verrà stampato su ogni foglio. Questo da solo può aiutare con la navigazione nel report.

A MrExcel piace l'idea di avere una macro per creare il sommario. Il problema principale è che Excel non calcola quante pagine stampate ci sono su un foglio di lavoro finché non si esegue un'anteprima di stampa. Quindi, la macro fa sapere all'utente che sta per vedere un'anteprima di stampa e chiede loro di chiuderla con un clic del pulsante di chiusura.

La macro scorre in ogni foglio della cartella di lavoro. Nel suo stato attuale, raccoglie le informazioni dal nome di ogni foglio di lavoro. Ho anche incluso altre due righe che sono state commentate. Se preferisci ottenere la descrizione dall'intestazione di sinistra o da un titolo nella cella A1, ci sono linee di esempio per fare anche uno di questi. Basta rimuovere il commento da quello che si desidera utilizzare.

La macro calcola il numero di pagine aggiungendo uno al numero di interruzioni di pagina orizzontali (HPageBreaks.count). Aggiunge uno al numero di interruzioni di pagina verticali (VPageBreaks.Count). Moltiplica questi due numeri insieme per calcolare il numero di pagine su quel foglio di lavoro. Se qualche lettore fedele ha un modo migliore per farlo, fammelo sapere. L'attuale metodo di conteggio delle interruzioni di pagina è diabolicamente lento. Non sono riuscito a trovare una proprietà che mi dica quante pagine stampate ci sono, ma potresti pensare che Excel ne includa una.

L'ultimo trucco è stato entrare nell'intervallo di pagine. Se un foglio fosse nelle pagine "3 - 4", Excel lo considererebbe come una data e inserirà il 4 marzo. Impostando il formato della cella sul testo con il carattere "@", le pagine vengono inserite correttamente.

Ecco 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

Di seguito è riportata una macro equivalente, aggiornata con diverse nuove tecniche di 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 riassunto delle nuove tecniche macro nella macro più recente:

• Raramente è necessario selezionare un foglio
• Piuttosto che scorrere ogni foglio nella cartella di lavoro alla ricerca di un foglio chiamato Sommario, la seconda macro presume semplicemente che sia presente e controlla lo stato della variabile Err. Se Err è diverso da 0, sappiamo che il foglio non esiste e deve essere aggiunto.
• WST è una variabile oggetto ed è definita come il foglio di lavoro del sommario. Pertanto, qualsiasi riferimento ai fogli di lavoro ("Sommario"). può essere sostituito con WST.
• Il costrutto Cells (riga, colonna) è più efficiente del kluge di Range ("A" e TOCRow). Poiché Cells () si aspetta parametri numerici, Range ("A" e TOCRow) diventano celle (TOCRow, 1)
• Le parentesi quadre vengono utilizzate come abbreviazione per fare riferimento a Intervallo ("A1").