Mallin avaaminen ja täyttäminen Excel VBA: lla

Excel-mallit ovat tyypillisesti työkirjoja, joissa on raportointikehys, jota toiminnot usein tukevat. Malleja (xltx) voidaan käyttää yhä uudelleen saastuttamatta sitä tiedoilla. Tietojen täyttämisen jälkeen mallityökirja tallennetaan xlsx: nä, mikä säilyttää itse xltx: n neitsytilan.

Tässä harjoituksessa käytämme VBA-koodia mallin avaamiseen ja täyttämiseen. Malli voidaan löytää täältä ja Excel-makro löytyy täältä.

Tässä artikkelissa oletetaan, että lukijalla on kehittäjänauha ja että hän tuntee VBA-editorin. Jos ei, ota Google "Excel Developer -välilehti" tai "Excel Code -ikkuna".

Malli

Ensinnäkin rakennamme näennäismallin, joka on täynnä tietoja, pivot-taulukkoa ja kaaviota seuraavasti:

Avaa uusi Excel-tiedosto. Nimeä "Sheet1" uudelleen "Chart" ja "Sheet2" nimellä "Data"

Kopioi seuraava teksti otsikot mukaan lukien Data-välilehden kohtaan D1:

Valitse kaikki tiedot, sarakeotsikot mukaan lukien, ja lisää pivot-taulukko Data-taulukon kohtaan A1 alla olevan kuvan mukaisesti.

Luo kaavio Kaavio-välilehdeltä käyttämällä pivot-taulukkoa tietolähteenä.

Poista tiedot kohdasta D2: F15. Pivot-taulukon data-aluetta ei tarvitse nollata; jätä se asutuksi, vaikka tietoja ei olisikaan.  

Tallenna työkirja nimellä VacancyTemplate.xltx. ” sen hakemiston alihakemistossa, jossa makron työkirjan on oltava. Vastaa "ei" kaikkiin Excelin ilmoituksiin tallennuksen aikana.

Tarvitsemme myös Raportit-alihakemiston. Esimerkiksi:

Excel-raportit (xlsm tallennettu tähän)

| _Mallit (xlxt tallennettu tähän)

       | _Raportit (kukin xlsx tallennettu tähän)

Kun se on tallennettu nimellä xltx, sulje malli

Makro

Avaa uusi työkirja pitämään koodia. Nimeä "Sheet1" nimeksi "Main" ja "Sheet2" nimeksi "Database".

Aseta painike “Main” -kohtaan ajaaksesi sovellusta.

Normaalisti tiedot haetaan tietokannoista. Koska kaikilla ei ole tietokantaa kätevästi, Tietokanta-taulukko jäljittelee tietokantataulukkoa.

Kopioi tämän artikkelin alussa olevat tiedot A1-kohdan ”Tietokanta”-välilehdelle…

Koodi

Alla oleva koodirakenne määrittelee prosessit selvästi:

• Hanki tiedot "tietokannasta";
• Avaa malli;
• Täytä malli tietoihin ja nollaa kääntötaulukon tietoalue;
• Tallenna malli raporttina

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-dataobjektit

Tietokannan lukemisen simuloimiseksi meidän on viitattava ActiveX-kirjastoon. Tee tämä valitsemalla koodi-ikkunasta Työkalut>Viitteet.

Testaa koodi

Määritä “Main” -painike kohtaan Sub-työkirja. Tallenna työkirja nimellä Populating Templates.xlsm.

SULJE työkirja ja avaa se uudelleen.

Paina painiketta nähdäksesi tuloksen. Lisää tietorivien määrää tietokannassa ja suorita uudelleen nähdäksesi, onko kaavio päivitetty lisätiedoilla.

Yllä olevassa koodissa olemme osoittaneet mallin aikaisin, kanssa XL.Visible = Tosi. Live-ympäristössä tämä voidaan tehdä loppuun asti, jotta näytön päivitykset eivät näy.

Käsittele Data Disaster!

Harva asia on turhauttavampaa kuin paljon kehitetyn Excel-tiedoston kaatuminen, lähdetiedoston vioittuminen ja varmuuskopion puuttuminen. Tällaisissa tapauksissa, joissa Excel ei onnistu palauttamaan vioittunutta tiedostoa, kaikki siihen tehty työ menetetään, ellei sinulla ole työkalua käsillä. korjaa Excel tiedostoja.

On myös järkevää varmuuskopioida usein arvokasta työtä.

Tekijän esittely:

Felix Hooker on tietojen palauttamisen asiantuntija DataNumen, Inc., joka on maailman johtava tietojen palautustekniikoissa, mukaan lukien rar-korjaus ja sql-palautusohjelmistotuotteet. Lisätietoja osoitteessa www.datanumen.com