Paano Buksan at Populate ang Template gamit ang Excel VBA

Ang Mga Template ng Excel ay karaniwang mga workbook, na may balangkas sa pag-uulat, na madalas na sinusuportahan ng mga pagpapaandar. Ang isang template (xltx) ay maaaring magamit nang paulit-ulit nang hindi nadumhan ito ng data. Matapos ang populasyon na may data, ang isang workbook ng template ay nai-save bilang isang xlsx, pinapanatili ang estado ng birhen ng xltx mismo.

Sa pagsasanay na ito gagamitin namin ang VBA code upang mabuksan at mapuno ang isang template. Ang template Maaaring matagpuan dito at Excel Macro na ginamit ay matatagpuan dito.

Ipinapalagay ng artikulong ito na ang mambabasa ay ipinapakita ang Developer ribbon at pamilyar sa VBA Editor. Kung hindi, mangyaring Google "Excel Developer Tab" o "Excel Code Window".

Ang Template

Una, gagawa kami ng isang template ng dummy, na pinuno ng data, isang pivot table at isang tsart, tulad ng sumusunod:

Magbukas ng isang bagong file na Excel. Palitan ang pangalan ng "Sheet1" bilang "Chart" at "Sheet2" bilang "Data"

Kopyahin ang sumusunod na teksto, kasama ang mga heading, sa D1 ng tab na "Data":

Piliin ang lahat ng data, kabilang ang mga heading ng haligi, at maglagay ng talahanayan ng pivot sa A1 ng sheet na "Data", tulad ng ipinakita sa ibaba.

Lumikha ng isang tsart sa tab na "Tsart", gamit ang talahanayan ng pivot bilang isang mapagkukunan ng data.

Alisin ang data sa D2: F15. Hindi kinakailangan upang i-reset ang hanay ng data ng talahanayan ng pivot; iwanan itong populasyon kahit na walang data.  

I-save ang workbook bilang "VacancyTemplate.xltx. " sa isang sub-direktoryo ng isa kung saan ang makro workbook ay naninirahan. Tumugon sa "Hindi" sa anumang mga alerto mula sa Excel habang nai-save.

Kakailanganin din namin ang isang sub-direktoryo ng Mga Ulat. Halimbawa:

Mga Ulat sa Excel (xlsm na nakaimbak dito)

| _Template (xlxt nakaimbak dito)

       | _Report (bawat xlsx ay nai-save dito)

Sa sandaling nai-save bilang isang xltx, isara ang template

Ang Macro

Magbukas ng isang bagong workbook upang hawakan ang aming code. Palitan ang pangalan ng "Sheet1" bilang "Pangunahin" at "Sheet2" bilang "Database".

Maglagay ng isang pindutan sa "Pangunahin" upang himukin ang application.

Karaniwan, ang data ay nakuha mula sa mga database. Dahil hindi lahat ay may isang madaling gamiting database, ang sheet na "Database" ay tutulad sa isang talahanayan ng database.

Kopyahin ang datos na matatagpuan sa simula ng artikulong ito papunta sa tab na "Database" sa A1...

Ang Kodigo

Ang istraktura ng code sa ibaba ay malinaw na tumutukoy sa mga proseso:

• Kunin ang data mula sa "database";
• Buksan ang template;
• Populate ang template gamit ang data at i-reset ang hanay ng data ng pivot table;
• I-save ang template bilang isang ulat

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

Mga Object ng Data ng ActiveX

Para gayahin ang pagbasa ng database, kailangan nating i-reference ang Active X library. Gawin ito sa pamamagitan ng Tools>References mula sa code window.

Subukan ang Code

Italaga ang pindutan sa "Pangunahin" sa Sub Openbookbook. I-save ang workbook bilang "Populate Templates.xlsm".

ISADAD ang workbook, at muling buksan ito.

Pindutin ang pindutan na tingnan ang resulta. Taasan ang bilang ng mga hilera ng data sa "Database", at patakbuhin muli, nakikita kung na-update ang Tsart na may karagdagang impormasyon.

Sa code sa itaas ipinakita namin ang template nang maaga, kasama ang XL.Visible = Tama. Sa live na kapaligiran, magagawa ito sa pinakadulo, upang ang mga pag-update sa screen ay hindi nakikita.

Makitungo sa Data Disaster!

Iilang bagay ang mas nakakadismaya kaysa sa isang Excel file na lubos na na-develop, na sumisira sa source file, at walang magagamit na backup copy. Sa ganitong mga pagkakataon, kung saan nabigo ang Excel na mabawi ang nasirang file, mawawala ang lahat ng trabahong ginawa dito maliban kung mayroon kang tool na madaling gamitin. ayusin ang Excel file.

Maingat din na madalas na mag-back up ng mahalagang trabaho.

Panimula ng May-akda:

Si Felix Hooker ay isang dalubhasa sa pagbawi ng data sa DataNumen, Inc., na pinuno ng mundo sa mga teknolohiya sa pagbawi ng data, kasama ang pagkukumpuni ng rar at mga produkto ng software sa pag-recover ng sql. Para sa karagdagang impormasyon pagbisita www.datanumen. Sa