Excel VBA ile Şablon Nasıl Açılır ve Doldurulur

Excel Şablonları genellikle, genellikle işlevler tarafından desteklenen bir raporlama çerçevesine sahip çalışma kitaplarıdır. A şablonları (xltx), verilerle kirletilmeden tekrar tekrar kullanılabilir. Verilerle doldurma işleminden sonra, bir şablon çalışma kitabı bir xlsx olarak kaydedilir ve xltx'in bakir durumu korunur.

Bu alıştırmada, bir şablonu açmak ve doldurmak için VBA kodunu kullanacağız. Şablon bulunabilir okuyun ve kullanılan Excel Makrosu bulunabilir. okuyun.

Bu makalede, okuyucunun Geliştirici şeridinin görüntülendiği ve VBA Düzenleyicisine aşina olduğu varsayılmaktadır. Değilse, lütfen Google "Excel Geliştirici Sekmesi" veya "Excel Kod Penceresi".

Şablon

İlk olarak, aşağıdaki gibi veriler, bir pivot tablo ve bir grafikle doldurulmuş sahte bir şablon oluşturacağız:

Yeni bir Excel dosyası açın. "Sayfa1"i "Grafik" ve "Sayfa2"yi "Veri" olarak yeniden adlandırın

Başlıklar dahil olmak üzere aşağıdaki metni "Veri" sekmesinin D1'ine kopyalayın:

Sütun başlıkları dahil tüm verileri seçin ve aşağıda gösterildiği gibi "Veri" sayfasının A1 noktasına bir pivot tablo ekleyin.

Pivot tabloyu veri kaynağı olarak kullanarak "Grafik" sekmesinde bir grafik oluşturun.

D2:F15'teki verileri kaldırın. Pivot tablo veri aralığını sıfırlamak gerekli değildir; veri olmasa bile dolu bırakın.  

Çalışma kitabını “VacancyTemplate.txt” olarak kaydedin.xltx” makro çalışma kitabının bulunacağı alt dizinde. Kaydetme sırasında Excel'den gelen uyarılara "Hayır" yanıtı verin.

Ayrıca bir Raporlar alt dizinine de ihtiyacımız olacak. Örneğin:

Excel Raporları (xlsm burada saklanır)

|_Şablonlar (xlxt burada saklanır)

       |_Raporlar (buraya kaydedilen her xlsx)

olarak kaydedildikten sonra xltx, şablonu kapatın

Makro

Kodumuzu tutmak için yeni bir çalışma kitabı açın. "Sayfa1"i "Ana" ve "Sayfa2"yi "Veritabanı" olarak yeniden adlandırın.

Uygulamayı çalıştırmak için "Ana" üzerine bir düğme yerleştirin.

Normalde, veriler veritabanlarından alınır. Herkesin kullanışlı bir veritabanı olmadığından, "Veritabanı" sayfası bir veritabanı tablosunu taklit edecektir.

Bu makalenin başında bulunan verileri A1 hücresindeki “Veritabanı” sekmesine kopyalayın…

Kod

Aşağıdaki kod yapısı süreçleri açıkça tanımlar:

• Verileri “veritabanından” alın;
• Şablonu açın;
• Şablonu verilerle doldurun ve pivot tablo veri aralığını sıfırlayın;
• Şablonu rapor olarak kaydedin

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 Veri Nesneleri

Veritabanı okuma işlemini simüle etmek için ActiveX kütüphanesine referans vermemiz gerekiyor. Bunu kod penceresinden Araçlar > Referanslar yoluyla yapabilirsiniz.

Kodu Test Et

“Ana” üzerindeki düğmeyi şuna atayın: Alt Openworkbook. Çalışma kitabını “Populating Templates.xlsm” olarak kaydedin.

Çalışma kitabını KAPATIN ve yeniden açın.

Sonucu görüntülemek için düğmeye basın. "Veritabanı"ndaki veri satırlarının sayısını artırın ve Grafiğin ek bilgilerle güncellenip güncellenmediğini kontrol ederek tekrar çalıştırın.

Yukarıdaki kodda, şablonu erkenden gösterdik. XL.Görünür = Doğru. Canlı ortamda bu en sonda yapılabilir, böylece ekran güncellemeleri görünmez.

Veri Felaketi ile Başa Çıkın!

Gelişmiş bir Excel dosyasının çökmesi, kaynak dosyayı bozması ve yedek kopyasının bulunmaması kadar sinir bozucu az şey vardır. Excel'in hasarlı dosyayı kurtaramadığı bu gibi durumlarda, elinizde bir kurtarma aracı yoksa, üzerinde yapılan tüm çalışmalar kaybolur. Excel'i düzelt dosyaları.

Değerli işleri sık sık yedeklemek de ihtiyatlı bir davranıştır.

Yazar Tanıtımı:

Felix Hooker, veri kurtarma uzmanıdır. DataNumendahil olmak üzere veri kurtarma teknolojilerinde dünya lideri olan , Inc. rar onarımı ve sql kurtarma yazılımı ürünleri. Daha fazla bilgi için ziyaret edin www.datanumen.com