Innhold Makro - Excel-tips

Takk til Matt som sendte ukens Excel-spørsmål:

Jeg har en stor og voksende Excel-arbeidsbok (mange ark). Jeg har tatt med sidetall i bunnteksten under utskrift, men det blir vanskeligere og vanskeligere å navigere når vi er i et møte. Er det en måte å skrive ut en innholdsfortegnelse basert på navn på Excel-regneark, slik at jeg og personalet raskt kan gå til side #xx?

Dette er en god idé. Det første enkle forslaget er å inkludere arknavnet i bunnteksten på utskriften. Når du klikker på "Tilpasset bunntekst" i dialogboksen Utskriftsformat / topptekst, er det 7 ikoner. Det høyre ikonet ser ut som et indekskort med tre faner. Ved å klikke i høyre seksjon: og trykke på ikonet, vil arknavnet bli skrevet ut på hvert ark. Dette alene kan hjelpe med å navigere gjennom rapporten.

MrExcel liker ideen om å ha en makro for å lage innholdsfortegnelsen. Hovedproblemet er at Excel ikke beregner hvor mange trykte sider som er på et regneark før du gjør en forhåndsvisning av utskriften. Så lar makroen brukeren vite at de er i ferd med å se en forhåndsvisning av utskrift, og ber dem om å avvise den med et klikk på lukkeknappen.

Makroen går gjennom hvert ark i arbeidsboken. I den nåværende tilstanden samler den informasjon fra navnet på hvert regneark. Jeg har også tatt med to andre linjer som er kommentert. Hvis du heller vil få beskrivelsen fra venstre overskrift eller fra en tittel i celle A1, er det eksempler på linjer som du kan gjøre også. Bare kommenter den du vil bruke.

Makroen beregner hvor mange sider ved å legge til en til antall horisontale sideskift (HPageBreaks.count). Det legger en til antall vertikale sideskift (VPageBreaks.Count). Det multipliserer disse to tallene sammen for å beregne antall sider på regnearket. Hvis noen lojale lesere har en bedre måte å gjøre dette på, vennligst gi meg beskjed. Den nåværende metoden for å telle sideskiftene er djevelsk treg. Jeg syntes ikke å finne en eiendom som forteller meg hvor mange trykte sider det er, men du tror Excel vil inneholde en.

Det siste trikset var å gå inn i sideområdet. Hvis et ark var på sidene "3 - 4", behandler Excel dette som en dato og går inn 4. mars. Ved å sette celleformatet til tekst med "@" tegnet, kommer sidene riktig inn.

Her er makroen:

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

Nedenfor er en ekvivalent makro, oppdatert med flere nye makroteknikker.

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

En kort oppsummering av de nye makroteknikkene i den nyere makroen:

  • Det er sjelden nødvendig å velge et ark
  • I stedet for å løpe gjennom hvert ark i arbeidsboken på jakt etter et ark som heter Innholdsfortegnelse, antar den andre makroen ganske enkelt at den er der og sjekker statusen til Err-variabelen. Hvis Err er noe annet enn 0, vet vi at arket ikke eksisterer og må legges til.
  • WST er en objektvariabel og er definert som regneark for innholdsfortegnelse. Dermed enhver henvisning til regneark ("Innholdsfortegnelse"). kan erstattes med WST.
  • Cells (rad, kolonne) -konstruksjon er mer effektiv enn kluge av Range ("A" og TOCRow). Fordi celler () forventer numeriske parametere, blir Område ("A" og TOCRow) celler (TOCRow, 1)
  • De firkantede parentesene brukes som en stenografisk måte å referere til Range ("A1").

Interessante artikler...