सामग्री की तालिका मैक्रो - एक्सेल टिप्स

इस सप्ताह के एक्सेल प्रश्न भेजने वाले मैट का धन्यवाद:

मेरे पास एक बड़ी और बढ़ती एक्सेल वर्कबुक (बहुत सारी चादरें) हैं। मैंने मुद्रण करते समय पाद लेख में पृष्ठ संख्याएँ शामिल की हैं, हालाँकि जब हम किसी मीटिंग में होते हैं तो नेविगेट करना कठिन और कठिन हो जाता है। क्या एक्सेल वर्कशीट नामों के आधार पर सामग्री की एक तालिका को प्रिंट करने का एक तरीका है ताकि मैं और कर्मचारी जल्दी से पृष्ठ #xx में बदल सकें?

यह एक महान विचार है। पहला सरल सुझाव आपके प्रिंटआउट के पाद लेख में शीट का नाम शामिल करना है। जब आप पृष्ठ सेटअप / हैडर पाद संवाद में "कस्टम पाद" पर क्लिक करते हैं, तो 7 आइकन होते हैं। सबसे दाहिना आइकन तीन टैब के साथ एक इंडेक्स कार्ड की तरह दिखता है। राइट सेक्शन में क्लिक करना: बॉक्स और उस आइकन को हिट करने से शीट का नाम प्रत्येक शीट पर प्रिंट हो जाएगा। यह अकेले रिपोर्ट के माध्यम से नेविगेट करने में मदद कर सकता है।

MrExcel को सामग्री की तालिका बनाने के लिए मैक्रो होने का विचार पसंद है। मुख्य समस्या यह है कि जब तक आप प्रिंट पूर्वावलोकन नहीं करते हैं तब तक एक्सेल किसी कार्यपत्रक पर कितने मुद्रित पृष्ठ की गणना नहीं करता है। इसलिए, मैक्रो उपयोगकर्ता को यह जानने देता है कि वे एक प्रिंट पूर्वावलोकन देखने वाले हैं और उन्हें क्लोज बटन के एक क्लिक के साथ इसे खारिज करने के लिए कहता है।

कार्यपुस्तिका में प्रत्येक पत्रक के माध्यम से मैक्रो लूप करता है। यह वर्तमान स्थिति में है, यह प्रत्येक कार्यपत्रक के नाम से जानकारी एकत्र करता है। मैंने दो अन्य पंक्तियों को भी शामिल किया है जिन पर टिप्पणी की गई है। यदि आप बजाए शीर्ष लेख से या सेल A1 में एक शीर्षक से वर्णन प्राप्त करेंगे, तो उन दोनों के रूप में भी करने के लिए नमूना लाइनें हैं। बस आप जो उपयोग करना चाहते हैं, उसे अनइंस्टॉल करें।

मैक्रो गणना करता है कि क्षैतिज पृष्ठ विराम (HPageBreaks.count) की संख्या में एक जोड़कर कितने पृष्ठ हैं। यह वर्टिकल पेज ब्रेक (VPageBreaks.Count) की संख्या में एक जोड़ता है। यह इन दो संख्याओं को उस कार्यपत्रक पर पृष्ठों की संख्या की गणना करने के लिए एक साथ गुणा करता है। यदि किसी निष्ठावान पाठक के पास ऐसा करने का बेहतर तरीका है, तो कृपया मुझे बताएं। पृष्ठ विराम को गिनने की वर्तमान विधि शैतानी धीमी है। मुझे ऐसा कोई प्रॉपर्टी नहीं मिला, जो यह बताता हो कि कितने मुद्रित पृष्ठ हैं, लेकिन आपको लगता है कि एक्सेल में एक शामिल होगा।

अंतिम ट्रिक पेज रेंज में प्रवेश कर रही थी। यदि एक शीट "3 - 4" पृष्ठों पर थी, तो एक्सेल इसे एक तारीख के रूप में मानेगा और 4 मार्च को प्रवेश करेगा। सेल प्रारूप को "@" वर्ण के साथ पाठ में सेट करके, पेज ठीक से दर्ज होते हैं।

यहाँ मैक्रो है:

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

नीचे एक समरूप मैक्रो है, जिसे कई नई मैक्रो तकनीकों के साथ अद्यतन किया गया है।

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

नए मैक्रो में नई मैक्रो तकनीकों का संक्षिप्त सारांश:

शीट का चयन करना शायद ही कभी आवश्यक होता है
कार्यपुस्तिका में प्रत्येक शीट के माध्यम से लूपिंग करने के बजाय, टेबल ऑफ कॉन्टेंट्स नामक एक शीट की तलाश में, दूसरा मैक्रो बस मानता है कि यह वहां है और एर चर की स्थिति की जांच करता है। यदि Err 0 के अलावा कुछ भी है, तो हमें पता है कि शीट मौजूद नहीं है और इसे जोड़ना होगा।
WST एक ऑब्जेक्ट वैरिएबल है और इसे टेबल ऑफ़ कॉन्टेंट्स वर्कशीट के रूप में परिभाषित किया गया है। इस प्रकार, वर्कशीट ("सामग्री तालिका") का कोई भी संदर्भ। WST से बदला जा सकता है।
कक्ष (पंक्ति, स्तंभ) का निर्माण रेंज ("A" और TOCROW) के कुल्लु से अधिक कुशल है। क्योंकि कोशिकाएं () संख्यात्मक मापदंडों की उम्मीद करती हैं, रेंज ("ए" और TOCRow) कोशिकाएं बन जाती हैं (TOCRow, 1)
वर्ग कोष्ठक का उपयोग रेंज ("ए 1") को संदर्भित करने के एक संक्षिप्त तरीके के रूप में किया जाता है।