Excel VBA bilan shablonni qanday ochish va to'ldirish

Excel shablonlari odatda hisobot tizimiga ega, ko'pincha funktsiyalar tomonidan qo'llab-quvvatlanadigan ishchi kitoblardir. Shablonlarni (xltx) ma'lumotlar bilan ifloslantirmasdan qayta-qayta ishlatish mumkin. Ma'lumotlar bilan to'ldirilgandan so'ng, shablon ishchi kitobi xltx ning o'zining bokira holatini saqlab, xlsx sifatida saqlanadi.

Ushbu mashqda biz shablonni ochish va to'ldirish uchun VBA kodidan foydalanamiz. Shablon Topish mumkin Bu yerga va foydalanilgan Excel makrosini topish mumkin Bu yerga.

Ushbu maqolada o'quvchi dasturchi tasmasi ko'rsatilgan va VBA muharriri bilan tanish deb taxmin qilinadi. Agar yo'q bo'lsa, Google "Excel Developer Tab" yoki "Excel Code Window".

Shablon

Birinchidan, biz quyidagi ma'lumotlar, pivot jadval va diagramma bilan to'ldirilgan qo'g'irchoq shablonni yaratamiz:

Yangi Excel faylini oching. "Sheet1" nomini "Chart" va "Sheet2" "Ma'lumotlar" deb o'zgartiring

Quyidagi matnni, jumladan, sarlavhalarni “Maʼlumotlar” yorligʻining D1-ga nusxalang:

Barcha ma'lumotlarni, shu jumladan ustun sarlavhalarini tanlang va quyida ko'rsatilganidek, "Ma'lumotlar" varaqining A1-ga pivot jadvalini qo'ying.

Ma'lumotlar manbai sifatida pivot jadvalidan foydalanib, "Chart" yorlig'ida diagramma yarating.

D2: F15 da ma'lumotlarni olib tashlang. Pivot jadval ma'lumotlar diapazonini tiklash shart emas; ma'lumotlar bo'lmasa ham uni to'ldirilgan holda qoldiring.  

Ish kitobini “Vacancy Template” sifatida saqlang.xltx”. makro ish kitobi joylashgan katalogning pastki katalogida. Saqlash paytida Exceldan kelgan barcha ogohlantirishlarga "Yo'q" deb javob bering.

Shuningdek, bizga Hisobotlar kichik katalogi kerak bo'ladi. Masalan:

Excel hisobotlari (xlsm shu yerda saqlanadi)

|_andozalar (xlxt shu yerda saqlanadi)

       |_Hisobotlar (har bir xlsx bu yerda saqlanadi)

Bir marta sifatida saqlangan xltx, shablonni yoping

Makro

Kodimizni saqlash uchun yangi ish kitobini oching. "Sheet1" nomini "Asosiy" va "Sheet2" ni "Ma'lumotlar bazasi" deb o'zgartiring.

Ilovani boshqarish uchun "Asosiy" tugmachasini qo'ying.

Odatda, ma'lumotlar ma'lumotlar bazasidan olinadi. Hamma ham ma'lumotlar bazasiga ega emasligi sababli, "Ma'lumotlar bazasi" varag'i ma'lumotlar bazasi jadvaliga taqlid qiladi.

Ushbu maqolaning boshida topilgan ma'lumotlarni A1... dagi "Ma'lumotlar bazasi" yorlig'iga nusxalash.

Kodeks

Quyidagi kod tuzilishi jarayonlarni aniq belgilaydi:

• "Ma'lumotlar bazasi" dan ma'lumotlarni oling;
• Shablonni oching;
• Shablonni ma'lumotlar bilan to'ldiring va pivot jadval ma'lumotlar oralig'ini tiklang;
• Shablonni hisobot sifatida saqlang

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 ma'lumotlar ob'ektlari

Ma'lumotlar bazasini o'qishni simulyatsiya qilish uchun biz Active X kutubxonasiga murojaat qilishimiz kerak. Buni kod oynasidan Tools>References orqali bajaring.

Kodni sinab ko'ring

"Asosiy" tugmachasini belgilang Kichik ochiq ish kitobi. Ishchi kitobni “Pulating Templates.xlsm” sifatida saqlang.

Ish daftarini yoping va uni qayta oching.

Natijani ko'rish tugmachasini bosing. "Ma'lumotlar bazasi" dagi ma'lumotlar qatorlari sonini ko'paytiring va Diagramma qo'shimcha ma'lumotlar bilan yangilanganligini ko'rish uchun qayta ishga tushiring.

Yuqoridagi kodda biz shablonni erta ko'rsatdik, bilan XL.Visible = Rost. Jonli muhitda bu ekran yangilanishlari ko'rinmasligi uchun eng oxirida amalga oshirilishi mumkin.

Ma'lumotlar halokati bilan kurashing!

Juda ko'p ishlab chiqilgan Excel faylining ishdan chiqishi, manba faylining buzilishi va zaxira nusxasining yo'qligidan ko'ra ko'proq asabga tegadigan narsa kam. Bunday hollarda, Excel shikastlangan faylni tiklay olmasa, qo'lingizda vosita bo'lmasa, u bilan bajarilgan barcha ishlar yo'qoladi. Excelni tuzatish fayllar.

Qimmatbaho ishlarni tez-tez zaxiralab turish ham oqilona.

Muallif kirish:

Feliks Xuker ma'lumotlarni qayta tiklash bo'yicha mutaxassis DataNumenMa'lumotlarni qayta tiklash texnologiyalari bo'yicha jahon yetakchisi bo'lgan , Inc rar ta'mirlash va sql-ni tiklash dasturiy mahsulotlar. Qo'shimcha ma'lumot olish uchun tashrif buyuring www.datanumen.com