Podziękowania dla Matta, który wysłał w tym tygodniu pytanie dotyczące programu Excel:
Mam duży i rosnący skoroszyt programu Excel (dużo arkuszy). Podczas drukowania umieściłem numery stron w stopce, jednak nawigacja w trakcie spotkania jest coraz trudniejsza. Czy istnieje sposób na wydrukowanie spisu treści na podstawie nazw arkuszy programu Excel, abyśmy ja i pracownicy mogli szybko przejść do strony #xx?
To świetny pomysł. Pierwsza prosta sugestia to umieszczenie nazwy arkusza w stopce wydruku. Po kliknięciu opcji „Stopka niestandardowa” w oknie dialogowym Ustawienia strony / Stopka nagłówka pojawia się 7 ikon. Ikona znajdująca się po prawej stronie wygląda jak karta indeksu z trzema zakładkami. Kliknięcie pola Prawa sekcja: i naciśnięcie tej ikony spowoduje wydrukowanie nazwy arkusza na każdym arkuszu. Już samo to może pomóc w poruszaniu się po raporcie.
MrExcelowi podoba się pomysł posiadania makra do tworzenia spisu treści. Główny problem polega na tym, że program Excel nie oblicza liczby wydrukowanych stron w arkuszu, dopóki nie wykonasz podglądu wydruku. Tak więc makro informuje użytkownika, że wkrótce zobaczy podgląd wydruku, i prosi o zamknięcie go jednym kliknięciem przycisku zamykania.
Makro przechodzi przez każdy arkusz w skoroszycie. W obecnym stanie zbiera informacje z nazwy każdego arkusza. Dodałem również dwie inne linie, które zostały zakomentowane. Jeśli wolisz uzyskać opis z lewego nagłówka lub z tytułu w komórce A1, dostępne są również przykładowe linie do wykonania jednej z tych czynności. Po prostu odkomentuj ten, którego chcesz użyć.
Makro oblicza liczbę stron, dodając jeden do liczby poziomych podziałów stron (HPageBreaks.count). Dodaje jeden do liczby pionowych podziałów strony (VPageBreaks.Count). Mnoży te dwie liczby, aby obliczyć liczbę stron w tym arkuszu. Jeśli jacyś lojalni czytelnicy mają lepszy sposób na zrobienie tego, daj mi znać. Obecna metoda liczenia podziałów stron jest diabelsko powolna. Nie mogłem znaleźć właściwości, która mówi mi, ile jest wydrukowanych stron, ale można by pomyśleć, że Excel będzie zawierał jedną.
Ostatnią sztuczką było wejście w zakres stron. Jeśli arkusz znajdował się na stronach „3-4”, program Excel potraktuje to jako datę i wprowadzi 4 marca. Ustawiając format komórki na tekst ze znakiem „@”, strony są wprowadzane poprawnie.
Oto 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 SubPoniżej znajduje się równoważne makro, zaktualizowane o kilka nowych technik makr.
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 SubKrótkie podsumowanie nowych technik makr w nowszym makrze:
- Rzadko trzeba wybierać arkusz
- Zamiast przeglądać każdy arkusz w skoroszycie w poszukiwaniu arkusza zwanego Spisem treści, drugie makro po prostu zakłada, że tam jest i sprawdza stan zmiennej Err. Jeśli Err jest inne niż 0, wiemy, że arkusz nie istnieje i należy go dodać.
- WST jest zmienną obiektową i jest zdefiniowana jako arkusz roboczy Spisu treści. W związku z tym wszelkie odniesienia do Arkuszy roboczych („Spis treści”). można zastąpić WST.
- Konstrukcja Cells (row, column) jest bardziej wydajna niż kluge Range („A” & TOCRow). Ponieważ Cells () oczekuje parametrów numerycznych, Range („A” & TOCRow) staje się komórkami (TOCRow, 1)
- Nawiasy kwadratowe są używane jako skrótowy sposób określania zakresu („A1”).
