Si të hapni dhe plotësoni shabllonin me Excel VBA

Modelet e Excel janë zakonisht libra pune, me një kornizë raportimi, shpesh të mbështetur nga funksionet. Një shabllon (xltx) mund të përdoret vazhdimisht pa e ndotur atë me të dhëna. Pas popullimit me të dhëna, një libër pune shabllon ruhet si xlsx, duke ruajtur gjendjen e virgjër të vetë xltx.

Në këtë ushtrim do të përdorim kodin VBA për të hapur dhe mbushur një shabllon. Shablloni mund të gjenden këtu dhe Excel Macro e përdorur mund të gjendet këtu.

Ky artikull supozon se lexuesi ka të shfaqur shiritin e Zhvilluesit dhe është i njohur me Redaktuesin VBA. Nëse jo, ju lutemi Google "Excel Developer Tab" ose "Excel Code Window".

Modeli

Së pari, ne do të ndërtojmë një shabllon të rremë, të mbushur me të dhëna, një tabelë kryesore dhe një tabelë, si më poshtë:

Hapni një skedar të ri Excel. Riemërto "Sheet1" si "Grafik" dhe "Sheet2" si "Të dhënat"

Kopjoni tekstin e mëposhtëm, duke përfshirë titujt, në D1 të skedës "Data":

Zgjidhni të gjitha të dhënat, duke përfshirë titujt e kolonave, dhe futni një tabelë kryesore në A1 të fletës "Data", siç tregohet më poshtë.

Krijoni një grafik në skedën "Grafiku", duke përdorur tabelën kryesore si burim të dhënash.

Hiqni të dhënat në D2:F15. Nuk është e nevojshme të rivendosni diapazonin e të dhënave të tabelës kryesore; lëreni të mbushur edhe nëse nuk ka të dhëna.  

Ruani librin e punës si “VacancyTemplate.xltx.” në një nëndrejtori të atij në të cilin do të qëndrojë libri makro i punës. Përgjigjuni "Jo" për çdo sinjalizim nga Excel gjatë ruajtjes.

Do të na duhet gjithashtu një nëndrejtori Raporte. Për shembull:

Raporte Excel (xlsm ruhet këtu)

|_Templates (xlxt ruhet këtu)

       |_Raporton (secila xlsx e ruajtur këtu)

Pasi ruhet si një xltx, mbyllni shabllonin

Makro

Hapni një libër të ri pune për të mbajtur kodin tonë. Riemërto "Sheet1" si "Kryesore" dhe "Sheet2" si "Baza e të Dhënave".

Vendosni një buton në "Main" për të drejtuar aplikacionin.

Normalisht, të dhënat merren nga bazat e të dhënave. Meqenëse jo të gjithë kanë një bazë të dhënash të dobishme, fleta "Baza e të dhënave" do të imitojë një tabelë të bazës së të dhënave.

Kopjoni të dhënat e gjetura në start të këtij artikulli në skedën "Baza e të dhënave" në A1…

Kodi

Struktura e kodit më poshtë përcakton qartë proceset:

• Merrni të dhënat nga "baza e të dhënave";
• Hapni shabllonin;
• Plotësoni shabllonin me të dhëna dhe rivendosni gamën e të dhënave të tabelës kryesore;
• Ruani shabllonin si raport

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

Objektet e të dhënave ActiveX

Për të simuluar një bazë të dhënash të lexuar, duhet t'i referohemi Active X library. Bëni këtë përmes Tools>References nga dritarja e kodit.

Testoni Kodin

Cakto butonin në "Main" për Nën Libër pune të hapur. Ruaje librin e punës si “Populating Templates.xlsm”.

MBYLL librin e punës dhe rihape atë.

Shtypni butonin për të parë rezultatin. Rritni numrin e rreshtave të të dhënave në "Baza e të dhënave" dhe ekzekutoni përsëri, duke parë nëse grafiku është përditësuar me informacion shtesë.

Në kodin e mësipërm ne kemi treguar shabllonin herët, me XL.E dukshme = E vërtetë. Në mjedisin live, kjo mund të bëhet në fund, në mënyrë që përditësimet e ekranit të mos jenë të dukshme.

Merreni me fatkeqësinë e të dhënave!

Pak gjëra janë më zhgënjyese sesa një skedar Excel shumë i zhvilluar që rrëzohet, korrupton skedarin burimor dhe pa asnjë kopje rezervë të disponueshme. Në raste të tilla, kur Excel nuk arrin të rikuperojë skedarin e dëmtuar, e gjithë puna e bërë në të është lost përveç nëse keni një mjet të dobishëm për të rregulloni Excel files.

Është gjithashtu e kujdesshme që shpesh të mbështetet puna e vlefshme.

Hyrje e autorit:

Felix Hooker është një ekspert i rikuperimit të të dhënave në DataNumen, Inc., e cila është lider botëror në teknologjitë e rikuperimit të të dhënave, duke përfshirë rar riparim dhe produkte softuerike për rikuperimin sql. Për më shumë informacion vizitoni www.datanumen.com