Mulțumesc lui Matt care a trimis întrebarea Excel din această săptămână:
Am un registru de lucru mare și în creștere Excel (o mulțime de foi). Am inclus numere de pagină în subsol în timp ce tipăresc, totuși este din ce în ce mai greu să navigați atunci când suntem într-o întâlnire. Există o modalitate de a imprima un cuprins bazat pe numele foilor de lucru Excel, astfel încât eu și personalul să putem trece rapid la pagina #xx?
Aceasta este o idee grozavă. Prima sugestie simplă este să includeți numele foii în subsolele tipăritului. Când faceți clic pe „subsol personalizat” în dialogul Configurare pagină / subsol antet, există 7 pictograme. Pictograma din dreapta arată ca o carte index cu trei file. Dacă faceți clic în secțiunea Dreapta: și apăsați pe pictograma respectivă, numele fișei va fi imprimat pe fiecare coală. Numai acest lucru poate ajuta la navigarea în raport.
Lui MrExcel îi place ideea de a avea un macro pentru a crea cuprinsul. Problema principală este că Excel nu calculează câte pagini tipărite sunt pe o foaie de lucru până când nu faceți o previzualizare a tipăririi. Deci, macrocomanda îi permite utilizatorului să știe că urmează să vadă o previzualizare a tipăririi și îi cere să o renunțe printr-un clic pe butonul de închidere.
Macrocomanda parcurge fiecare foaie din registrul de lucru. În starea actuală, colectează informații din numele fiecărei foi de lucru. Am inclus și alte două rânduri care sunt comentate. Dacă preferați să obțineți descrierea din antetul din stânga sau dintr-un titlu din celula A1, există exemple de linii pentru a face oricare dintre acestea. Descomentați-l doar pe cel pe care doriți să îl utilizați.
Macro calculează câte pagini adăugând una la numărul de pauze orizontale de pagină (HPageBreaks.count). Se adaugă unul la numărul de întreruperi de pagină verticale (VPageBreaks.Count). Înmulțește aceste două numere împreună pentru a calcula numărul de pagini din foaia de lucru respectivă. Dacă vreun cititor fidel are o modalitate mai bună de a face acest lucru, vă rog să-mi spuneți. Metoda actuală de numărare a pauzelor de pagină este diabolic lentă. Parcă nu găseam o proprietate care să-mi spună câte pagini tipărite există, dar ați crede că Excel ar include una.
Ultimul truc a fost introducerea intervalului de pagini. Dacă o foaie era pe paginile „3 - 4”, Excel ar trata aceasta ca o dată și ar intra pe 4 martie. Prin setarea formatului de celulă la text cu caracterul „@”, paginile intră corect.
Iată macro-ul:
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 SubMai jos este un macro echivalent, actualizat cu mai multe tehnici noi 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 SubUn scurt rezumat al noilor tehnici macro în macro-ul mai nou:
- Rareori este necesar să selectați o foaie
- În loc să parcurgă fiecare foaie din registrul de lucru, căutând o foaie numită Cuprins, a doua macro presupune pur și simplu că este acolo și verifică starea variabilei Err. Dacă Err este altceva decât 0, știm că foaia nu există și trebuie adăugată.
- WST este o variabilă obiect și este definit ca fișa de lucru a Cuprinsului. Astfel, orice referire la foi de lucru („Cuprins”). poate fi înlocuit cu WST.
- Construcția de celule (rând, coloană) este mai eficientă decât kluge-ul Range („A” & TOCRow). Deoarece Cells () așteaptă parametri numerici, Range („A” & TOCRow) devine celule (TOCRow, 1)
- Parantezele pătrate sunt folosite ca modalitate de scurtare a referinței la Range („A1”).
