Ինչպես բացել և լրացնել ձևանմուշը Excel VBA- ով

Excel ձևանմուշները սովորաբար աշխատանքային գրքեր են, որոնք ունեն հաշվետվությունների հիմք, որոնք հաճախ աջակցվում են գործառույթներով: A ձևանմուշները (xltx) կարող են օգտագործվել բազմիցս ՝ առանց տվյալների աղտոտման: Տվյալներով բնակչությունից հետո կաղապարի աշխատանքային գիրքը պահվում է որպես xlsx ՝ պահպանելով բուն xltx- ի կուսական վիճակը:

Այս վարժությունում մենք կօգտագործենք VBA կոդ ՝ ձևանմուշը բացելու և լրացնելու համար: Կաղապարը կարելի է գտնել այստեղ և օգտագործված Excel մակրոը կարելի է գտնել այստեղ.

Այս հոդվածը ենթադրում է, որ ընթերցողը ցուցադրում է erրագրավորողի ժապավենը և ծանոթ է VBA խմբագրին: Եթե ​​ոչ, խնդրում ենք Google- ի «Excel Developer Tab» կամ «Excel Code Window»:

Կաղապարը

Նախ, մենք կկառուցենք կեղծ ձևանմուշ, որը բնակեցված է տվյալների, առանցքային աղյուսակի և գծապատկերի միջոցով, հետևյալ կերպ.

Բացեք նոր Excel ֆայլ: «Sheet1» անվանումը վերանվանել «Chart» և «Sheet2» անվանումը ՝ «Data»

«Տվյալներ» ներդիրի D1- ում պատճենեք հետևյալ տեքստը, ներառյալ վերնագրերը.

Ընտրեք բոլոր տվյալները, ներառյալ սյունակների վերնագրերը, և «Տվյալների» թերթիկի A1- ում տեղադրեք առանցքային աղյուսակ, ինչպես ցույց է տրված ստորև:

Ստեղծեք գծապատկեր «Գծապատկեր» ներդիրում, օգտագործելով առանցքային աղյուսակը ՝ որպես տվյալների աղբյուր:

Հեռացրեք տվյալները D2- ում `F15: Անհրաժեշտ չէ առանցքային աղյուսակի տվյալների տիրույթը վերականգնել. թողնել այն բնակեցված, նույնիսկ եթե տվյալներ չկան:  

Աշխատանքային գիրքը պահեք որպես «Թափուր աշխատատեղերի ձևանմուշ.xltx» մեկի ենթագրացուցակում, որում պետք է լինի մակրո աշխատանքային տետրը: Խնայելու ընթացքում պատասխանեք «Ոչ» -ից Excel- ի ցանկացած ահազանգերին:

Մեզ նույնպես պետք է «portsեկույցներ» ենթագրացուցակ: Օրինակ:

Excel հաշվետվություններ (xlsm- ը պահվում է այստեղ)

| _Templates (xlxt- ը պահված է այստեղ)

       | _Հաշվետվություններ (յուրաքանչյուր xlsx պահված է այստեղ)

Մի անգամ փրկվեց որպես xltx, փակել ձևանմուշը

Մակրո

Բացեք նոր աշխատանքային գրքույկ ՝ մեր ծածկագիրը պահելու համար: «Sheet1» անվանումը վերանվանել «Main» և «Sheet2» անվանումը ՝ «Database»:

Տեղադրեք կոճակը «Հիմնական» վրա `ծրագիրը վարելու համար:

Սովորաբար, տվյալները վերցվում են շտեմարաններից: Քանի որ ոչ բոլորն են հարմար տվյալների բազայում, «Տվյալների շտեմարան» թերթը կպատկերացնի տվյալների շտեմարանի աղյուսակը:

Հոդվածի սկզբում գտնվող տվյալները պատճենեք A1-ի «Տվյալների բազա» ներդիրում…

Կոդ

Ստորև բերված ծածկագրի կառուցվածքը հստակ սահմանում է գործընթացները.

• Ստացեք տվյալները «տվյալների բազայից»;
• Բացեք ձևանմուշը;
• Կաղապարը լրացրեք տվյալների հետ և վերակայեք առանցքային աղյուսակի տվյալների տիրույթը:
• Ձևանմուշը պահեք որպես զեկույց

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 տվյալների օբյեկտներ

Տվյալների բազայի ընթերցումը մոդելավորելու համար մենք պետք է հղում կատարենք Active X գրադարանին։ Դա կարող եք անել կոդի պատուհանից՝ Tools>References (Գործիքներ>Հղումներ) անցնելով։

Փորձեք օրենսգիրքը

Նշեք «Հիմնական» կոճակը Ենթաբաշխման գրքույկ Աշխատանքային գիրքը պահեք որպես «Բնակչության ձևանմուշներ. Xlsm»:

ՓԱԿԵՔ աշխատանքային գիրքը և կրկին բացեք այն:

Սեղմեք արդյունքը դիտելու կոճակը: «Շտեմարան» -ում ավելացրեք տվյալների շարքերի քանակը և նորից գործարկեք ՝ տեսնելով, արդյոք գծապատկերը թարմացվել է հավելյալ տեղեկատվությամբ:

Վերևի ծածկագրում մենք նախապես ցույց ենք տվել ձևանմուշը ՝ XL. Տեսանելի = ueիշտ է, Կենդանի միջավայրում դա կարելի է անել ամենավերջում, այնպես որ էկրանի թարմացումները տեսանելի չեն:

Գործ ունենք տվյալների աղետի հետ:

Քիչ բաներ կան այնքան նյարդայնացնող, որքան շատ մշակված Excel ֆայլի վթարը, որը վնասում է սկզբնական ֆայլը և առանց պահուստային պատճենի հասանելիության։ Նման դեպքերում, երբ Excel-ը չի կարողանում վերականգնել վնասված ֆայլը, դրա վրա կատարված ամբողջ աշխատանքը կորչում է, եթե ձեռքի տակ չունեք գործիք։ ուղղել Excel- ը ֆայլեր.

Արժեք է նաև արժեքավոր աշխատանքի հաճախ կրկնօրինակը:

Հեղինակի ներածություն.

Ֆելիքս Հուքերը տվյալների վերականգման փորձագետ է DataNumen, Inc., որը տվյալների վերականգման տեխնոլոգիաների համաշխարհային առաջատարն է, այդ թվում rar-ի վերանորոգում և sql վերականգնման ծրագրային արտադրանքները: Լրացուցիչ տեղեկությունների համար այցելեք www.datanumen.com