Excel VBA-тай загварыг хэрхэн нээж, хэрхэн яаж дүүргэх вэ

Excel Templates нь ихэвчлэн ажлын дэвтэр бөгөөд тайлагнах хүрээтэй, ихэвчлэн функцээр дэмжигддэг. Загваруудыг (xltx) өгөгдлөөр бохирдуулахгүйгээр дахин дахин ашиглаж болно. Өгөгдөлтэй болсны дараа ажлын товхимлын загварыг xlsx хэлбэрээр хадгалж, xltx-ийн онгон байдлыг хадгална.

Энэ дасгалд бид VBA кодыг ашиглан загварыг нээж, дүүргэх болно. Загвар олж болно энд болон ашигласан Excel-ийн макро олж болно энд.

Энэ нийтлэл нь уншигчдад Developer туузыг харуулсан бөгөөд VBA редакторыг сайн мэддэг гэж үзнэ. Хэрэв үгүй ​​бол Google-ийн “Excel Developer Tab” эсвэл “Excel Code Window” -ийг ашиглана уу.

Загвар

Нэгдүгээрт, бид өгөгдөл, пивот хүснэгт, диаграммаар дүүргэсэн дамми загварыг дараах байдлаар бүтээх болно.

Шинэ Excel файл нээнэ үү. "Хуудас1" -ийг "Диаграм", "Хуудас2" -ийг "Мэдээлэл" гэж өөрчлөх

Дараах текст, гарчгийг оруулаад “Мэдээлэл” табын D1 руу хуулж ав.

Баганын гарчгийг оруулаад бүх өгөгдлийг сонгоод "Өгөгдөл" хуудасны А1 дээр пивот хүснэгт оруулна уу.

Пивот хүснэгтийг мэдээллийн эх үүсвэр болгон ашиглан "Диаграм" таб дээр диаграм үүсгээрэй.

D2: F15 дэх өгөгдлийг устгана уу. Пивот хүснэгтийн өгөгдлийн мужийг дахин тохируулах шаардлагагүй; өгөгдөл байхгүй байсан ч гэсэн хүн амаа үлдээх.  

Ажлын номоо “VacancyTemplate.xltx. ” макро ажлын дэвтэр байрлах дэд директор дотор. Хадгалах явцад Excel-ээс ирсэн аливаа анхааруулгад "Үгүй" гэж хариулна уу.

Бидэнд Reports дэд лавлах хэрэгтэй болно. Жишээлбэл:

Excel-ийн тайлан (xlsm энд хадгалагдсан)

| _загварууд (xlxt энд хадгалагдсан)

       | _Тайлан (xlsx бүрийг энд хадгалсан)

Нэг удаа xltx, загварыг хаах

Макро

Манай кодыг хадгалахын тулд шинэ ажлын дэвтэр нээнэ үү. "Хуудас1" -ийг "Үндсэн", "Хуудас2" -ийг "Мэдээллийн сан" болгон өөрчил.

Програмыг жолоодохын тулд "Main" дээр товчлуурыг байрлуул.

Ердийн үед өгөгдлийг мэдээллийн сангаас авдаг. Хүн бүр мэдээллийн санд тохирохгүй тул "Мэдээллийн сан" хуудас нь өгөгдлийн сангийн хүснэгтийг дууриах болно.

Энэ нийтлэлийн эхэнд байгаа өгөгдлийг 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.Visible = Үнэн. Амьд орчинд үүнийг төгсгөлд нь хийж болох бөгөөд ингэснээр дэлгэцийн шинэчлэлтүүд харагдахгүй болно.

Мэдээллийн гамшигтай тэмцээрэй!

Маш их хөгжүүлсэн Excel файл гацаж, эх файл нь эвдэрч, нөөц хуулбар байхгүй болохоос илүү бухимдал төрүүлэм зүйл цөөхөн байдаг. Ийм тохиолдолд Excel гэмтсэн файлыг сэргээж чадахгүй бол танд туслах хэрэгсэл байхгүй л бол түүгээр хийсэн бүх ажил алдагдана. Excel-ийг засах файлууд.

Үнэ цэнэтэй бүтээлээ байнга нөөцлөх нь ухаалаг хэрэг юм.

Зохиогчийн танилцуулга:

Феликс Хүүкер бол мэдээлэл сэргээх мэргэжилтэн юм DataNumen, Үүнд мэдээлэл сэргээх технологиор дэлхийд тэргүүлэгч, Inc. rar засвар болон sql сэргээх програм хангамжийн бүтээгдэхүүнүүд. Дэлгэрэнгүй мэдээллийг авна уу WWW.datanumen.com