Үлгіні Excel VBA көмегімен қалай ашуға және толтыруға болады

Excel шаблондары әдетте жұмыс кітаптары болып табылады, есеп беру шеңбері бар, көбінесе функциялар қолдайды. Шаблондарды (xltx) оны мәліметтермен ластамай қайта-қайта қолдануға болады. Толтырылғаннан кейін, жұмыс кітабының үлгісі xltx тың күйін сақтай отырып, xlsx ретінде сақталады.

Бұл жаттығуда біз шаблонды ашу және толтыру үшін VBA кодын қолданамыз. Үлгі Табуға болады Мұнда және Excel-дің макросты табуға болады Мұнда.

Бұл мақала оқырманға Әзірлеуші ​​лентасы көрсетілген және VBA редакторымен таныс деп болжайды. Олай болмаса, Google-дің «Excel Developer Tab» немесе «Excel Code Window» сұраймыз.

Үлгі

Біріншіден, біз деректермен, бұрылыс кестесімен және диаграммамен толтырылған жалған шаблон жасаймыз:

Жаңа Excel файлын ашыңыз. “Sheet1” - “Диаграмма”, “Sheet2” - “Data” деп өзгертіңіз

«Мәліметтер» қойындысының D1 мәтініне тақырыптарды қоса келесі мәтінді көшіріңіз:

Баған тақырыптарын қоса барлық деректерді таңдап, төменде көрсетілгендей «Деректер» парағының А1-ге бұрылыс кестесін салыңыз.

Деректер көзі ретінде жиынтық кестені пайдаланып, «Диаграмма» қойындысында диаграмма жасаңыз.

D2: F15 ішіндегі деректерді алып тастаңыз. Жиынтық кестенің деректер ауқымын қалпына келтіру қажет емес; деректер болмаса да, оны толығымен қалдырыңыз.  

Жұмыс кітабын «Вакансиялық үлгі.xltx. ” макро жұмыс кітабы орналасқан каталогта. Сақтау кезінде Excel-ден келген ескертулерге «Жоқ» деп жауап беріңіз.

Бізге есептер ішкі каталогы қажет болады. Мысалға:

Excel есептері (xlsm осында сақталған)

| _үлгілер (xlxt осында сақталған)

       | _Есептер (әрбір xlsx осында сақталады)

Ретінде сақталды xltx, үлгіні жабыңыз

Макро

Біздің кодты сақтау үшін жаңа жұмыс кітабын ашыңыз. “Sheet1” - “Main”, “Sheet2” - “Database” деп өзгертіңіз.

Қосымшаны басқару үшін «Негізгі» батырмасын орналастырыңыз.

Әдетте мәліметтер базасынан мәліметтер алынады. Барлығында бірдей мәліметтер қоры бола бермейтіндіктен, «Деректер базасы» парағы мәліметтер қорының кестесін еліктейді.

-Дан табылған деректерді көшіріңізtarосы мақаланың А1-де «Деректер базасы» қойындысына…

Кодекс

Төмендегі код құрылымы процестерді нақты анықтайды:

• Деректерді «мәліметтер базасынан» алыңыз;
• Үлгіні ашыңыз;
• Үлгіні деректермен толтырыңыз және жиынтық кесте деректер ауқымын қалпына келтіріңіз;
• Үлгіні есеп ретінде сақтаңыз

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 lib сілтемесіне жүгіну керекrarж. Мұны Код терезесіндегі Құралдар> Сілтемелер арқылы жасаңыз.

Кодты тексеріп көріңіз

«Негізгі» батырмасын келесіге тағайындаңыз Ашық жұмыс кітабы. Жұмыс кітабын «Үлгілерді толтыру.xlsm» ретінде сақтаңыз.

Жұмыс кітабын жауып, оны қайта ашыңыз.

Нәтижені көру үшін батырманы басыңыз. «Деректер базасындағы» мәліметтер қатарының санын көбейтіп, диаграмма қосымша ақпаратпен жаңартылғанын тексеріп, қайта іске қосыңыз.

Жоғарыдағы кодта біз үлгіні ертерек көрсеттік XL.Visible = Рас. Тірі ортада мұны ең соңында жасауға болады, осылайша экран жаңартулары көрінбейді.

Деректер апатымен күресу!

Әдетте дамыған Excel файлынан, бастапқы файлды бүлдіріп, резервтік көшірмесінен гөрі аз нәрсе көңілсіз. Мұндай жағдайларда, егер Excel бүлінген файлды қалпына келтіре алмаса, онда барлық жұмыс l боладыost егер сізге ыңғайлы құрал болмаса Excel бағдарламасын түзетіңіз файлдар.

Сондай-ақ құнды жұмыстың сақтық көшірмесін жиі жасау қажет.

Автордың кіріспесі:

Феликс Хукер - деректерді қалпына келтіру бойынша сарапшы DataNumen, Соның ішінде деректерді қалпына келтіру технологиялары бойынша әлемдік көшбасшы болып табылатын Inc. rar жөндеу және SQL қалпына келтіру бағдарламалық жасақтама өнімдері. Қосымша ақпарат алу үшін кіріңіз WWW.datanumen.com