Excel VBA ilə şablonu necə açmaq və doldurmaq olar

Excel Şablonları adətən funksiyalar tərəfindən dəstəklənən hesabat çərçivəsi olan iş kitablarıdır. Şablonlar (xltx) onu məlumatlarla çirkləndirmədən dəfələrlə istifadə edilə bilər. Məlumatlarla yığımdan sonra şablon iş kitabı xltx-in özünün bakirə vəziyyətini saxlayaraq xlsx kimi saxlanılır.

Bu məşqdə şablonu açmaq və doldurmaq üçün VBA kodundan istifadə edəcəyik. Şablon bilər burada və istifadə Excel makro tapa bilərsiniz burada.

Bu məqalə oxucunun Tərtibatçı lentinin göstərildiyini və VBA Redaktoru ilə tanış olduğunu güman edir. Əgər belə deyilsə, lütfən Google “Excel Developer Tab” və ya “Excel Code Window”.

Şablon

Birincisi, biz aşağıdakı kimi məlumatlar, pivot cədvəli və diaqramla doldurulmuş dummy şablon quracağıq:

Yeni Excel faylı açın. “Cədvəl1”in adını “Chart” və “Sheet2”nin adını “Data” olaraq dəyişdirin

Aşağıdakı mətni, o cümlədən başlıqları “Məlumat” nişanının D1-ə köçürün:

Sütun başlıqları daxil olmaqla bütün məlumatları seçin və aşağıda göstərildiyi kimi “Məlumat” vərəqinin A1-də pivot cədvəli daxil edin.

Məlumat mənbəyi kimi pivot cədvəlindən istifadə edərək, “Chart” tabında diaqram yaradın.

D2:F15-dəki məlumatları silin. Pivot cədvəlinin məlumat diapazonunu sıfırlamaq lazım deyil; heç bir məlumat olmasa belə onu məskunlaşdırın.  

İş kitabını “Vacancy Template.xltx.” makro iş kitabının yerləşəcəyi alt kataloqda. Saxlama zamanı Excel-dən gələn hər hansı xəbərdarlıqlara "Xeyr" cavabını verin.

Bizə həmçinin Hesabatlar alt kataloquna ehtiyacımız olacaq. Misal üçün:

Excel Hesabatları (xlsm burada saxlanılır)

|_Şablonlar (xlxt burada saxlanılır)

       |_Hesabatlar (hər xlsx burada saxlanılır)

Bir dəfə olaraq qeyd edildi xltx, şablonu bağlayın

Makro

Kodumuzu saxlamaq üçün yeni iş kitabı açın. “Cədvəl1”in adını “Əsas” və “Vədvəl2”nin adını “Verilənlər bazası” olaraq dəyişdirin.

Proqramı idarə etmək üçün "Əsas" üzərinə bir düymə qoyun.

Normalda məlumatlar verilənlər bazalarından alınır. Hər kəsin məlumat bazası olmadığı üçün “Verilənlər bazası” cədvəli verilənlər bazası cədvəlini təqlid edəcəkdir.

Bu məqalənin əvvəlində tapılan məlumatları A1-dəki “Verilənlər Bazası” sekmesine kopyalayın...

Kod

Aşağıdakı kod strukturu prosesləri aydın şəkildə müəyyənləşdirir:

• "Məlumat bazasından" məlumatları əldə edin;
• Şablonu açın;
• Şablonu verilənlərlə doldurun və pivot cədvəl məlumat diapazonunu sıfırlayın;
• Şablonu hesabat kimi yadda saxlayın

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 məlumat obyektləri

Verilənlər bazasının oxunmasını simulyasiya etmək üçün Active X kitabxanasına istinad etməliyik. Bunu kod pəncərəsindən Alətlər>Referanslar vasitəsilə edin.

Kodu sınayın

"Əsas" düyməsini təyin edin Alt açıq iş dəftəri. İş kitabını “Populating Templates.xlsm” kimi saxlayın.

İş dəftərini bağlayın və yenidən açın.

Nəticəyə baxmaq düyməsini sıxın. “Verilənlər bazası”nda verilənlər sətirlərinin sayını artırın və Diaqramın əlavə məlumatla yenilənib-yenilənilmədiyini yoxlayın.

Yuxarıdakı kodda biz şablonu erkən göstərdik XL.Visible = Doğrudur. Canlı mühitdə bu, ekran yeniləmələrinin görünməməsi üçün ən sonunda edilə bilər.

Data fəlakəti ilə mübarizə aparın!

Çox inkişaf etmiş bir Excel faylının sıradan çıxması, mənbə faylının zədələnməsi və ehtiyat nüsxəsinin olmamasından daha məyusedici bir şey yoxdur. Belə hallarda, Excel zədələnmiş faylı bərpa edə bilmədikdə, əlinizdə bir alət olmadığı təqdirdə, üzərində görülən bütün işlər itirilir. Excel-i düzəldin files.

Dəyərli işlərin tez-tez ehtiyat nüsxəsini çıxarmaq da ehtiyatlıdır.

Müəllif Giriş:

Feliks Hooker məlumatların bərpası üzrə mütəxəssisdir DataNumendaxil olmaqla məlumatların bərpası texnologiyaları üzrə dünya lideri olan , Inc rar təmiri və sql bərpa proqram məhsulları. Ətraflı məlumat üçün ziyarət edin www.datanumen.com