วิธีการเปิดและเติมเทมเพลตด้วย Excel VBA

โดยทั่วไปเทมเพลต Excel เป็นสมุดงานที่มีกรอบการรายงานซึ่งมักได้รับการสนับสนุนโดยฟังก์ชัน สามารถใช้เทมเพลต (xltx) ซ้ำแล้วซ้ำอีกโดยไม่ทำให้ข้อมูลเสียหาย หลังจากใส่ข้อมูลแล้วสมุดงานเทมเพลตจะถูกบันทึกเป็น xlsx โดยรักษาสถานะบริสุทธิ์ของ xltx ไว้

ในแบบฝึกหัดนี้เราจะใช้รหัส VBA เพื่อเปิดและเติมเทมเพลต เทมเพลต สามารถพบได้ Good Farm Animal Welfare Awards และ Excel Macro สามารถพบได้ Good Farm Animal Welfare Awards.

บทความนี้ถือว่าผู้อ่านมี Ribbon ของนักพัฒนาปรากฏอยู่และคุ้นเคยกับ VBA Editor มิฉะนั้นโปรดใช้“ แท็บนักพัฒนา Excel” ของ Google หรือ“ หน้าต่างรหัส Excel”

เทมเพลต

ขั้นแรกเราจะสร้างเทมเพลตจำลองที่มีข้อมูลตาราง Pivot และแผนภูมิดังนี้:

เปิดไฟล์ Excel ใหม่ เปลี่ยนชื่อ "Sheet1" เป็น "แผนภูมิ" และ "Sheet2" เป็น "Data"

คัดลอกข้อความต่อไปนี้รวมทั้งส่วนหัวลงใน D1 ของแท็บ "ข้อมูล":

เลือกข้อมูลทั้งหมดรวมถึงส่วนหัวของคอลัมน์และแทรกตาราง Pivot ที่ A1 ของแผ่นงาน“ Data” ดังที่แสดงด้านล่าง

สร้างแผนภูมิบนแท็บ "แผนภูมิ" โดยใช้ตาราง Pivot เป็นแหล่งข้อมูล

ลบข้อมูลใน D2: F15 ไม่จำเป็นต้องรีเซ็ตช่วงข้อมูลตาราง Pivot ปล่อยทิ้งไว้แม้ว่าจะไม่มีข้อมูลก็ตาม  

บันทึกเวิร์กบุ๊กเป็น“ VacancyTemplatexltx.” ในไดเร็กทอรีย่อยของไดเร็กทอรีที่ใช้มาโครเวิร์กบุ๊ก ตอบสนอง“ ไม่” สำหรับการแจ้งเตือนใด ๆ จาก Excel ในระหว่างการบันทึก

เราจะต้องมีไดเรกทอรีย่อยของรายงานด้วย ตัวอย่างเช่น:

รายงาน Excel (xlsm เก็บไว้ที่นี่)

| _แม่แบบ (xlxt เก็บไว้ที่นี่)

       | _ รายงาน (แต่ละ xlsx บันทึกไว้ที่นี่)

เมื่อบันทึกเป็นไฟล์ xltxปิดเทมเพลต

มาโคร

เปิดสมุดงานใหม่เพื่อเก็บรหัสของเรา เปลี่ยนชื่อ“ Sheet1” เป็น“ Main” และ“ Sheet2” เป็น“ Database”

วางปุ่มบน“ หลัก” เพื่อขับเคลื่อนแอปพลิเคชัน

โดยปกติข้อมูลจะถูกดึงมาจากฐานข้อมูล เนื่องจากไม่ใช่ทุกคนที่มีฐานข้อมูลสะดวกแผ่นงาน "ฐานข้อมูล" จึงจำลองตารางฐานข้อมูล

คัดลอกข้อมูลที่พบใน start ของบทความนี้ลงในแท็บ“ ฐานข้อมูล” ที่ A1 …

รหัส

โครงสร้างโค้ดด้านล่างกำหนดกระบวนการอย่างชัดเจน:

• รับข้อมูลจาก "ฐานข้อมูล";
• เปิดเทมเพลต
• เติมเทมเพลตด้วยข้อมูลและรีเซ็ตช่วงข้อมูล Pivot Table
• บันทึกเทมเพลตเป็นรายงาน

Option Explicit
    'Create objects to represent the template workbook and worksheets
    Public wb As Object
    Public XL As Object
    Public connDB As New ADODB.Connection
    Public rs As ADODB.Recordset
    Public eRow As Integer
    Public eRec As Integer
    Public dDate As String

Sub openWorksheet()
    Call GetData
    Call OpenTemplate
    Call PopulateTemplate
    
    'Save template as datestamped xlsx
    On Error Resume Next
    dDate = Format(Now(), "yyyy.mm.dd")
    wb.SaveAs Filename:=ActiveWorkbook.Path & "\Reports\Vacancies" & dDate & ".xlsx", _
         FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False

     Sheets("Main").Activate        'Move off the database tab
     wb.Activate            'Bring the chart to the fore
     Set wb = Nothing
     Set XL = Nothing
     Set rs = Nothing
     Set connDB = Nothing
End Sub

Sub GetData()
    'Emulate database retrieval
    If connDB.State = 1 Then connDB.Close
    Sheets("Database").Activate
    Sheets("Database").Range("A1").Select
    Selection.End(xlDown).Select
    
    eRec = ActiveCell.Row - 1 'establish how many records will be in the recordset
                     'This step won't be needed in a database environment
    eRow = ActiveCell.Row   'the end row, used later in the template
    
    connDB.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
      "Data Source=" & ActiveWorkbook.FullName & ";" & _
      "Extended Properties=Excel 12.0;"
        
    Set rs = New ADODB.Recordset
    rs.Open "Select top " & eRec & " * from [Database$]", connDB, , , adCmdText
End Sub

 Sub OpenTemplate()
    Set XL = CreateObject("Excel.Application")
    XL.Visible = True       'enables us to see what's happening on debug.
    XL.Workbooks.Add ActiveWorkbook.Path & "\Templates\VacancyTemplate.xltx"
    Set wb = XL.ActiveWorkbook             'the new workbook is referenced by "wb"
 End Sub
 
 Sub PopulateTemplate()
    wb.Sheets("Data").Activate
    wb.Sheets("Data").Range("D2").CopyFromRecordset rs
    wb.Sheets("Data").Range("A1").Select
    
    'resize the range driving the pivot table, using the eRow variable.
    wb.Sheets("Data").PivotTables("PivotTable1").ChangePivotCache wb. _
        PivotCaches.Create(SourceType:=xlDatabase, SourceData:="Data!R1C4:R" & eRow & "C6", _
        Version:=xlPivotTableVersion14)
    wb.Sheets("Chart").Select

 End Sub

วัตถุข้อมูล ActiveX

ในการจำลองการอ่านฐานข้อมูลเราต้องอ้างอิง Active X librarย. ดำเนินการผ่านเครื่องมือ> การอ้างอิงจากหน้าต่างรหัส

ทดสอบรหัส

กำหนดปุ่มบน“ หลัก” ให้ Openworkbook ย่อย บันทึกเวิร์กบุ๊กเป็น“ Populating Templates.xlsm”

ปิดเวิร์กบุ๊กแล้วเปิดใหม่

กดปุ่มเพื่อดูผลลัพธ์ เพิ่มจำนวนแถวข้อมูลใน "ฐานข้อมูล" และเรียกใช้อีกครั้งเพื่อดูว่าแผนภูมิได้รับการอัปเดตด้วยข้อมูลเพิ่มเติมหรือไม่

ในโค้ดด้านบนเราได้แสดงเทมเพลตก่อนหน้านี้ด้วย XL.Visible = จริง. ในสภาพแวดล้อมแบบสดสามารถทำได้ในตอนท้ายเพื่อไม่ให้เห็นการอัปเดตหน้าจอ

จัดการกับภัยพิบัติของข้อมูล!

มีบางสิ่งที่น่าผิดหวังกว่าไฟล์ Excel ที่ได้รับการพัฒนาอย่างมากการขัดข้องทำให้ไฟล์ต้นฉบับเสียหายและไม่มีสำเนาสำรอง ในกรณีเช่นนี้เมื่อ Excel ไม่สามารถกู้คืนไฟล์ที่เสียหายงานทั้งหมดที่ทำในไฟล์นั้นคือ lost เว้นแต่คุณจะมีเครื่องมือที่มีประโยชน์ แก้ไข Excel ไฟล์

นอกจากนี้ควรระมัดระวังในการสำรองงานที่มีคุณค่าบ่อยๆ

บทนำผู้เขียน:

Felix Hooker เป็นผู้เชี่ยวชาญด้านการกู้คืนข้อมูลใน DataNumen, Inc. ซึ่งเป็นผู้นำระดับโลกด้านเทคโนโลยีการกู้คืนข้อมูล ได้แก่ rar ซ่อมแซม และผลิตภัณฑ์ซอฟต์แวร์กู้คืน sql ดูข้อมูลเพิ่มเติมได้ที่ wwwdatanumenด้วย.