Kā atvērt un aizpildīt veidni ar Excel VBA

Excel veidnes parasti ir darbgrāmatas ar atskaites sistēmu, ko bieži atbalsta funkcijas. Veidnes (xltx) var izmantot atkal un atkal, nepiesārņojot to ar datiem. Pēc populācijas ar datiem veidnes darbgrāmata tiek saglabāta kā xlsx, saglabājot paša xltx neapstrādāto stāvokli.

Šajā uzdevumā mēs izmantosim VBA kodu, lai atvērtu un aizpildītu veidni. Veidne var atrast šeit var atrast izmantoto Excel makro šeit.

Šajā rakstā tiek pieņemts, ka lasītājam ir parādīta izstrādātāja lente un viņš ir iepazinies ar VBA redaktoru. Ja nē, lūdzu, Google “Excel izstrādātāja cilne” vai “Excel koda logs”.

Veidne

Pirmkārt, mēs izveidosim fiktīvu veidni, kurā būs dati, rakurstabula un diagramma, šādi:

Atveriet jaunu Excel failu. Pārdēvēt “Sheet1” par “Chart” un “Sheet2” kā “Data”

Nokopējiet šo tekstu, ieskaitot virsrakstus, cilnes “Dati” D1 DXNUMX:

Atlasiet visus datus, ieskaitot kolonnu virsrakstus, un lapas “Dati” A1 vietā ievietojiet rakurstabulu, kā parādīts zemāk.

Cilnē “Diagramma” izveidojiet diagrammu, kā datu avotu izmantojot rakurstabulu.

Noņemiet datus D2: F15. Nav nepieciešams atiestatīt rakurstabulas datu diapazonu; atstājiet to apdzīvotu pat tad, ja nav datu.  

Saglabājiet darbgrāmatu kā “VacancyTemplate.xltx. ” apakšdirektorijā, kurā atrodas makro darbgrāmata. Saglabāšanas laikā atbildiet uz “Nē” uz visiem brīdinājumiem no Excel.

Mums būs nepieciešams arī apakšdirektorijs Reports. Piemēram:

Excel ziņojumi (šeit glabājas xlsm)

| _Veidnes (šeit glabājas xlxt)

       | _Pārskati (katrs xlsx saglabāts šeit)

Kad esat saglabājis kā xltx, aizveriet veidni

Makro

Atveriet jaunu darbgrāmatu, lai turētu mūsu kodu. Pārdēvējiet “Sheet1” par “Main” un “Sheet2” kā “Database”.

Novietojiet pogu uz “Main”, lai vadītu lietojumprogrammu.

Parasti datus iegūst no datu bāzēm. Tā kā ne visiem ir ērta datu bāze, lapa “Datu bāze” atdarina datu bāzes tabulu.

Kopējiet šī raksta sākumā atrastos datus cilnē “Datubāze” A1…

Kodekss

Zemāk esošā kodu struktūra skaidri nosaka procesus:

• Iegūstiet datus no “datu bāzes”;
• Atveriet veidni;
• Aizpildiet veidni ar datiem un atiestatiet rakurstabulas datu diapazonu;
• Saglabājiet veidni kā pārskatu

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 datu objekti

Lai simulētu datubāzes lasīšanu, mums ir jāatsaucas uz Active X bibliotēku. To var izdarīt, koda logā izvēloties Rīki>Atsauces.

Pārbaudiet kodu

Piešķiriet pogu “Main” uz Apakšdarbu grāmata. Saglabājiet darbgrāmatu kā “Veidņu aizpildīšana.xlsm”.

AIZVĒRT darbgrāmatu un atkārtoti atveriet to.

Nospiediet pogu, lai skatītu rezultātu. Palieliniet datu rindu skaitu “Database” un palaidiet vēlreiz, lai redzētu, vai diagramma ir atjaunināta ar papildu informāciju.

Iepriekš kodā mēs esam parādījuši veidni agri, ar XL.Redzams = patiess. Dzīvajā vidē to var izdarīt pašās beigās, lai ekrāna atjauninājumi nebūtu redzami.

Nodarbojieties ar datu katastrofu!

Maz kas var būt kaitinošāks par ļoti izstrādāta Excel faila avarēšanu, avota faila bojāšanu un bez pieejamas dublējuma kopijas. Šādos gadījumos, kad Excel neizdodas atgūt bojāto failu, viss ar to paveiktais darbs tiek zaudēts, ja vien jums nav pieejams rīks, lai to izdarītu. noteikt Excel faili.

Ir arī saprātīgi bieži dublēt vērtīgu darbu.

Autora ievads:

Fēlikss Hukers ir datu atkopšanas eksperts DataNumen, Inc., kas ir pasaules līderis datu atkopšanas tehnoloģiju, tostarp rar failu remonts un SQL atkopšanas programmatūras produkti. Lai iegūtu vairāk informācijas, apmeklējiet vietni www.datanumen. Ar