როგორ გავხსნათ და შევავსოთ შაბლონი Excel VBA-ით

Excel-ის შაბლონები, როგორც წესი, სამუშაო წიგნებია, მოხსენების ჩარჩოთი, ხშირად მხარდაჭერილი ფუნქციებით. შაბლონები (xltx) შეიძლება გამოყენებულ იქნას არაერთხელ, მონაცემებით დაბინძურების გარეშე. მონაცემების მქონე პოპულაციის შემდეგ, შაბლონის სამუშაო წიგნი ინახება xlsx-ად, ინარჩუნებს თავად xltx-ის უცნაურ მდგომარეობას.

ამ სავარჯიშოში ჩვენ გამოვიყენებთ VBA კოდს შაბლონის გასახსნელად და დასავსებად. შაბლონი შეიძლება ი აქ დაწკაპუნებით და Excel მაკრო გამოიყენება აქ დაწკაპუნებით.

ეს სტატია ვარაუდობს, რომ მკითხველს აქვს დეველოპერის ლენტი ნაჩვენები და იცნობს VBA რედაქტორს. თუ არა, გთხოვთ Google „Excel Developer Tab“ ან „Excel Code Window“.

შაბლონი

პირველ რიგში, ჩვენ ავაშენებთ მოტყუებულ შაბლონს, რომელიც შევსებულია მონაცემებით, კრებსითი ცხრილით და დიაგრამით, შემდეგნაირად:

გახსენით ახალი Excel ფაილი. დაარქვით „Sheet1“-ს „დიაგრამა“ და „Sheet2“ როგორც „მონაცემები“

დააკოპირეთ შემდეგი ტექსტი, სათაურების ჩათვლით, "მონაცემების" ჩანართის D1-ში:

აირჩიეთ ყველა მონაცემი, სვეტების სათაურების ჩათვლით, და ჩასვით კრებსითი ცხრილი "მონაცემების" ფურცლის A1-ზე, როგორც ეს ნაჩვენებია ქვემოთ.

შექმენით დიაგრამა „დიაგრამა“ ჩანართზე, მონაცემთა წყაროდ კრებსითი ცხრილის გამოყენებით.

წაშალეთ მონაცემები D2:F15-ში. არ არის აუცილებელი კრებსითი ცხრილის მონაცემთა დიაპაზონის გადატვირთვა; დატოვეთ იგი დასახლებული მაშინაც კი, თუ არ არის მონაცემები.  

შეინახეთ სამუშაო წიგნი, როგორც „VacancyTemplate.xltx.” იმ ქვედირექტორიაში, რომელშიც უნდა იყოს განთავსებული მაკრო სამუშაო წიგნი. უპასუხეთ "არა" Excel-ის ნებისმიერ გაფრთხილებას შენახვის დროს.

ჩვენ ასევე დაგვჭირდება ანგარიშების ქვეცნობარი. Მაგალითად:

Excel ანგარიშები (xlsm ინახება აქ)

|_Templates (xlxt ინახება აქ)

       |_ანგარიშები (თითოეული xlsx შენახულია აქ)

ერთხელ შენახული როგორც xltx, დახურეთ თარგი

მაკრო

გახსენით ახალი სამუშაო წიგნი ჩვენი კოდის შესანახად. დაარქვით „Sheet1“-ს „მთავარი“ და „Sheet2“ როგორც „მონაცემთა ბაზა“.

განათავსეთ ღილაკი "მთავარზე" აპლიკაციის მართვისთვის.

ჩვეულებრივ, მონაცემები ამოღებულია მონაცემთა ბაზებიდან. იმის გამო, რომ ყველას არ აქვს მონაცემთა ბაზა მოსახერხებელი, "მონაცემთა ბაზა" ფურცელი გამოიმუშავებს მონაცემთა ბაზის ცხრილს.

ამ სტატიის დასაწყისში მოცემული მონაცემები დააკოპირეთ 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-ის მეშვეობით.

შეამოწმეთ კოდი

მიანიჭეთ ღილაკი "მთავარზე". ქვე Openworkbook. შეინახეთ სამუშაო წიგნი, როგორც „Tablates.xlsm დასახლება“.

დახურეთ სამუშაო წიგნი და ხელახლა გახსენით იგი.

დააჭირეთ ღილაკს და ნახეთ შედეგი. გაზარდეთ მონაცემთა სტრიქონების რაოდენობა „მონაცემთა ბაზაში“ და გაუშვით ხელახლა და ნახეთ, განახლებულია თუ არა დიაგრამა დამატებითი ინფორმაციით.

ზემოთ მოცემულ კოდში ჩვენ ვაჩვენეთ შაბლონი ადრეულ პერიოდში, ერთად XL.Visible = True. ლაივ გარემოში, ეს შეიძლება გაკეთდეს ბოლომდე, ისე, რომ ეკრანის განახლებები არ ჩანს.

გაუმკლავდეთ მონაცემთა კატასტროფას!

ცოტა რამ არის უფრო იმედგაცრუებული, ვიდრე კარგად დამუშავებული Excel ფაილის გაუმართაობა, წყაროს ფაილის დაზიანება და სარეზერვო ასლის არარსებობა. ასეთ შემთხვევებში, როდესაც Excel ვერ ახერხებს დაზიანებული ფაილის აღდგენას, მასზე შესრულებული ყველა სამუშაო იკარგება, თუ ხელთ არ გაქვთ ხელსაწყო. შეასწორეთ Excel files.

ასევე გონივრულია ღირებული სამუშაოს ხშირად სარეზერვო საშუალება.

ავტორი შესავალი:

ფელიქს ჰუკერი არის მონაცემთა აღდგენის ექსპერტი DataNumen, Inc., რომელიც მსოფლიო ლიდერია მონაცემთა აღდგენის ტექნოლოგიებში, მათ შორის rar-ის შეკეთება და sql აღდგენის პროგრამული პროდუქტები. დამატებითი ინფორმაციისთვის ეწვიეთ www.datanumen. ერთად