Cara Membuka dan Mengisi Template dengan Excel VBA

Templat Excel biasanya adalah buku kerja, dengan kerangka pelaporan, sering kali didukung oleh berbagai fungsi. Template (xltx) dapat digunakan berulang kali tanpa mencemari data. Setelah populasi dengan data, templat buku kerja disimpan sebagai xlsx, mempertahankan status perawan dari xltx itu sendiri.

Dalam latihan ini kita akan menggunakan kode VBA untuk membuka dan mengisi template. Templatenya dapat ditemukan di sini dan Excel Macro yang digunakan dapat ditemukan di sini.

Artikel ini mengasumsikan bahwa pembaca memiliki pita Pengembang yang ditampilkan dan terbiasa dengan Editor VBA. Jika tidak, harap Google "Tab Pengembang Excel" atau "Jendela Kode Excel".

Template

Pertama, kita akan membuat template tiruan, diisi dengan data, tabel pivot, dan bagan, sebagai berikut:

Buka file Excel baru. Ubah nama "Sheet1" menjadi "Bagan" dan "Sheet2" sebagai "Data"

Salin teks berikut, termasuk tajuk, ke D1 dari tab "Data":

Pilih semua data, termasuk tajuk kolom, dan sisipkan tabel pivot di A1 dari lembar "Data", seperti yang ditunjukkan di bawah ini.

Buat diagram di tab "Bagan", menggunakan tabel pivot sebagai sumber data.

Hapus data di D2: F15. Rentang data tabel pivot tidak perlu disetel ulang; biarkan itu terisi meskipun tidak ada data.  

Simpan buku kerja sebagai “VacancyTemplate.xltx. ” di sub-direktori tempat buku kerja makro berada. Tanggapi "Tidak" untuk setiap peringatan dari Excel selama penyimpanan.

Kami juga membutuhkan sub-direktori Laporan. Sebagai contoh:

Laporan Excel (xlsm disimpan di sini)

| _template (xlxt disimpan di sini)

       | _Laporan (setiap xlsx disimpan di sini)

Setelah disimpan sebagai file xltx, tutup template

Makro

Buka buku kerja baru untuk menyimpan kode kita. Ubah nama "Sheet1" menjadi "Utama" dan "Sheet2" menjadi "Database".

Tempatkan tombol di "Utama" untuk menjalankan aplikasi.

Biasanya, data diambil dari database. Karena tidak semua orang memiliki database, sheet "Database" akan meniru tabel database.

Salin data yang terdapat di awal artikel ini ke tab “Basis Data” di A1…

Kode

Struktur kode di bawah ini dengan jelas mendefinisikan proses:

• Dapatkan data dari "database";
• Buka template;
• Isi template dengan data dan setel ulang rentang data tabel pivot;
• Simpan template sebagai laporan

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

Objek Data ActiveX

Untuk mensimulasikan pembacaan basis data, kita harus merujuk ke pustaka ActiveX. Lakukan ini melalui Tools > References dari jendela kode.

Uji Kode

Tetapkan tombol pada "Utama" untuk Sub Buku Kerawang. Simpan buku kerja sebagai "Mengisi Template.xlsm".

TUTUP buku kerja, dan buka kembali.

Tekan tombol untuk melihat hasilnya. Tingkatkan jumlah baris data di "Database", dan jalankan lagi, lihat apakah Bagan telah diperbarui dengan informasi tambahan.

Pada kode di atas kami telah menunjukkan template awal, dengan XL.Visible = Benar. Di lingkungan langsung, ini dapat dilakukan di bagian paling akhir, sehingga pembaruan layar tidak terlihat.

Atasi Bencana Data!

Tidak banyak hal yang lebih membuat frustrasi daripada file Excel yang sudah dikembangkan dengan matang tiba-tiba mengalami crash, merusak file sumber, dan tidak ada salinan cadangan yang tersedia. Dalam kasus seperti itu, di mana Excel gagal memulihkan file yang rusak, semua pekerjaan yang telah dilakukan akan hilang kecuali Anda memiliki alat yang siap digunakan. perbaiki Excel file.

Juga bijaksana untuk sering mencadangkan pekerjaan yang berharga.

Pengantar Penulis:

Felix Hooker adalah pakar pemulihan data di DataNumen, Inc., yang merupakan pemimpin dunia dalam teknologi pemulihan data, termasuk perbaikan langka dan produk perangkat lunak pemulihan sql. Untuk informasi lebih lanjut kunjungi www.datanumen.com