Sisällysluettelo Makro - Excel-vinkit

Kiitos Mattille, joka lähetti tämän viikon Excel-kysymyksen:

Minulla on iso ja kasvava Excel-työkirja (paljon arkkia). Olen lisännyt sivunumerot alatunnisteeseen tulostuksen aikana, mutta on yhä vaikeampi navigoida kokouksessa. Onko mahdollista tulostaa sisällysluettelo Excel-laskentataulukoiden nimien perusteella, jotta minä ja henkilökunta voimme nopeasti siirtyä sivulle #xx?

Tämä on hieno idea. Ensimmäinen yksinkertainen ehdotus on sisällyttää arkin nimi tulosteen alatunnisteisiin. Kun napsautat "Mukautettu alatunniste" Sivun asetukset / Ylä- ja alatunniste -valintaikkunassa, on 7 kuvaketta. Oikeanpuoleisin kuvake näyttää hakemistokortilta, jossa on kolme välilehteä. Napsauttamalla Oikea osa: -ruutua ja napsauttamalla kyseistä kuvaketta, arkin nimi tulostuu jokaiselle arkille. Pelkästään tämä voi auttaa selaamaan raporttia.

MrExcel pitää ajatuksesta makron luomisesta sisällysluettelon luomiseksi. Suurin ongelma on, että Excel ei laske, kuinka monta tulostettua sivua on laskentataulukossa, ennen kuin teet tulostuksen esikatselun. Joten makro ilmoittaa käyttäjälle, että he näkevät tulostuksen esikatselun, ja pyytää heitä hylkäämään sen napsauttamalla sulkemispainiketta.

Makro silmukkaa työkirjan jokaisen arkin läpi. Nykyisessä tilassaan se kerää tietoja jokaisen laskentataulukon nimestä. Olen myös sisällyttänyt kaksi muuta riviä, jotka on kommentoitu. Jos haluat mieluummin kuvauksen vasemmasta otsikosta tai solun A1 otsikosta, on myös kumpikin näistä tehtävä esimerkkiriveillä. Kommentoi vain sitä, jota haluat käyttää.

Makro laskee kuinka monta sivua lisäämällä yhden vaakasuuntaisten sivunvaihtojen määrään (HPageBreaks.count). Se lisää yhden pystysuuntaisten sivunvaihtojen määrään (VPageBreaks.Count). Se kertoo nämä kaksi numeroa yhdessä laskeaksesi taulukon sivujen määrän. Jos uskollisilla lukijoilla on parempi tapa tehdä tämä, ilmoita siitä minulle. Nykyinen menetelmä sivujen katkeamien laskemiseen on pirullisen hidasta. En näyttänyt löytävän ominaisuutta, joka kertoo minulle, kuinka monta tulostettua sivua on, mutta luulet Excelin sisältävän yhden.

Viimeinen temppu oli siirtyminen sivualueelle. Jos arkki oli sivuilla 3 - 4, Excel käsittelisi tätä päivämääränä ja kirjoittaisi 4. maaliskuuta. Asettamalla solumuoto tekstiksi "@" -merkillä sivut syötetään oikein.

Tässä on 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 Sub

Alla on vastaava makro, joka on päivitetty useilla uusilla makrotekniikoilla.

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

Lyhyt yhteenveto uudemman makron uusista makrotekniikoista:

  • Arkin valinta on harvoin tarpeen
  • Sen sijaan, että silmukoitaisiin jokaisen työkirjan taulukon läpi etsimällä taulukkoa, jonka nimi on Sisällysluettelo, toinen makro olettaa yksinkertaisesti olevan siellä ja tarkistaa Err-muuttujan tilan. Jos Err on jokin muu kuin 0, tiedämme, että taulukkoa ei ole ja se on lisättävä.
  • WST on objektimuuttuja ja se määritellään sisällysluettelotaulukoksi. Siten kaikki viittaukset laskentataulukoihin ("Sisällysluettelo"). voidaan korvata WST: llä.
  • Solurakenne (rivi, sarake) on tehokkaampi kuin Range ("A" & TOCRow) -kluge. Koska Cells () odottaa numeerisia parametreja, alueesta ("A" ja TOCRow) tulee soluja (TOCRow, 1)
  • Hakasulkeita käytetään lyhenteinä viittaamalla alueeseen ("A1").

Mielenkiintoisia artikkeleita...