Tartalomjegyzék makró - Excel tippek

Köszönet Mattnek, aki elküldte az e heti Excel kérdést:

Van egy nagy és növekvő Excel munkafüzetem (sok lap). Az oldalszámokat nyomtatás közben felvettem a láblécbe, azonban egyre nehezebb navigálni, amikor értekezleten vagyunk. Van-e mód arra, hogy az Excel munkalap-nevek alapján kinyomtasson egy tartalomjegyzéket, hogy én és a munkatársak gyorsan áttérhessünk a (z) #xx oldalra?

Ez egy nagyszerű ötlet. Az első egyszerű javaslat az, hogy a lap nevét vegye fel a nyomtatás láblécébe. Amikor az Oldalbeállítás / Fejléc lábléc párbeszédpanelen rákattint az "Egyéni lábléc" elemre, 7 ikon jelenik meg. A jobb szélső ikon három fülű indexkártyának tűnik. Ha rákattint a Jobb szakasz: mezőre, és megnyomja az ikont, a lap neve minden lapra kinyomtatódik. Ez önmagában segíthet a jelentésben való navigálásban.

MrExcelnek tetszik az ötlet, hogy makróval készítsen tartalomjegyzéket. A fő probléma az, hogy az Excel nem számolja ki, hogy hány nyomtatott oldal található egy munkalapon, amíg nem készít egy nyomtatási előnézetet. Tehát a makró tudatja a felhasználóval, hogy hamarosan látni fogja a nyomtatási előnézetet, és arra kéri őket, hogy a bezárás gombra kattintva utasítsa el.

A makró végignézi a munkafüzet egyes lapjait. Jelenlegi állapotában információkat gyűjt az egyes munkalapok nevéből. Két másik sort is felvettem, amelyeket kommentáltak. Ha inkább a bal fejlécből vagy az A1 cellában található címből szeretné megkapni a leírást, akkor ezek közül bármelyik elvégzésére is sorok vannak. Csak kommentálja azt, amelyet használni szeretne.

A makró hány oldalt számol ki, ha egyet hozzáad a vízszintes oldaltörések számához (HPageBreaks.count). Hozzáad egyet a függőleges oldaltörések számához (VPageBreaks.Count). Ezt a két számot megszorozza, hogy kiszámolja az adott munkalapon található oldalak számát. Ha valamelyik hűséges olvasónak jobb módja van erre, kérem, tudassa velem. Az oldaltörések számlálásának jelenlegi módja ördögien lassú. Úgy tűnt, hogy nem találtam olyan tulajdonságot, amely megmondaná, hány nyomtatott oldal van, de azt gondolnád, hogy az Excel tartalmazna egyet.

Az utolsó trükk az oldaltartományba való belépés volt. Ha egy lap a "3 - 4" oldalon volt, akkor az Excel ezt dátumként kezeli, és március 4-ét írja be. Ha a cella formátumát "@" karakterű szövegre állítja, akkor az oldalak megfelelően beíródnak.

Itt van a makró:

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

Az alábbiakban egy ekvivalens makró található, több új makrótechnikával frissítve.

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

Az új makró új makrótechnikáinak rövid összefoglalása:

  • Ritkán van szükség lap kiválasztására
  • A 2. makró ahelyett, hogy a munkafüzet egyes lapjain keresztül keresné a Tartalomjegyzék nevű lapot, egyszerűen feltételezi, hogy ott van, és ellenőrzi az Err változó állapotát. Ha az Err más, mint 0, akkor tudjuk, hogy a lap nem létezik, és hozzá kell adni.
  • A WST egy objektumváltozó, amelyet a Tartalomjegyzék munkalapnak definiálnak. Így bármilyen hivatkozás a Munkalapokra ("Tartalomjegyzék"). helyettesíthető WST-vel.
  • A Cells (sor, oszlop) konstrukció hatékonyabb, mint a Range ("A" és TOCRow) kluge. Mivel a Cells () numerikus paramétereket vár, a Range ("A" & TOCRow) cellákká válik (TOCRow, 1)
  • A szögletes zárójeleket rövidítésként használják a Range ("A1") hivatkozására.

érdekes cikkek...