Cách mở và điền mẫu bằng Excel VBA

Mẫu Excel thường là sổ làm việc, có khung báo cáo, thường được hỗ trợ bởi các hàm. Một mẫu (xltx) có thể được sử dụng nhiều lần mà không làm ô nhiễm dữ liệu. Sau khi điền dữ liệu, sổ làm việc mẫu sẽ được lưu dưới dạng xlsx, giữ nguyên trạng thái nguyên bản của chính xltx.

Trong bài tập này, chúng ta sẽ sử dụng mã VBA để mở và điền vào một mẫu. Bản mẫu có thể được tìm thấy đây và Excel Macro được sử dụng có thể được tìm thấy đây.

Bài viết này giả định rằng người đọc đã hiển thị dải băng Nhà phát triển và quen thuộc với Trình soạn thảo VBA. Nếu không, hãy Google “Excel Developer Tab” hoặc “Excel Code Window”.

Bản mẫu

Đầu tiên, chúng ta sẽ xây dựng một mẫu giả, chứa dữ liệu, bảng tổng hợp và biểu đồ như sau:

Mở một tệp Excel mới. Đổi tên “Sheet1” thành “Chart” và “Sheet2” thành “Data”

Sao chép văn bản sau, bao gồm các tiêu đề, vào D1 của tab “Dữ liệu”:

Chọn tất cả dữ liệu, bao gồm tiêu đề cột và chèn bảng tổng hợp tại A1 của trang “Dữ liệu”, như hiển thị bên dưới.

Tạo biểu đồ trên tab “Biểu đồ”, sử dụng bảng tổng hợp làm nguồn dữ liệu.

Xóa dữ liệu ở D2:F15. Không cần thiết phải đặt lại phạm vi dữ liệu của bảng tổng hợp; để nó được phổ biến ngay cả khi không có dữ liệu.  

Lưu sổ làm việc dưới dạng “VacancyTemplate.xltx.” trong thư mục con của thư mục chứa sổ làm việc macro. Trả lời “Không” với bất kỳ cảnh báo nào từ Excel trong quá trình lưu.

Chúng tôi cũng sẽ cần một thư mục con Báo cáo. Ví dụ:

Báo cáo Excel (xlsm được lưu trữ ở đây)

|_Templates (xlxt được lưu trữ ở đây)

       |_Báo cáo (mỗi xlsx được lưu ở đây)

Sau khi được lưu dưới dạng xltx, đóng mẫu

Macro

Mở một sổ làm việc mới để giữ mã của chúng tôi. Đổi tên “Sheet1” thành “Main” và “Sheet2” thành “Database”.

Đặt một nút trên “Main” để điều khiển ứng dụng.

Thông thường, dữ liệu được lấy từ cơ sở dữ liệu. Vì không phải ai cũng có sẵn cơ sở dữ liệu nên bảng “Cơ sở dữ liệu” sẽ mô phỏng một bảng cơ sở dữ liệu.

Sao chép dữ liệu ở đầu bài viết này vào tab “Cơ sở dữ liệu” tại ô A1…

Mật mã

Cấu trúc mã bên dưới xác định rõ ràng các quy trình:

• Lấy dữ liệu từ “cơ sở dữ liệu”;
• Mở mẫu;
• Điền dữ liệu vào mẫu và đặt lại phạm vi dữ liệu của bảng tổng hợp;
• Lưu mẫu dưới dạng báo cáo

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

Đối tượng dữ liệu ActiveX

Để mô phỏng thao tác đọc cơ sở dữ liệu, chúng ta cần tham chiếu đến thư viện ActiveX. Thực hiện điều này thông qua Tools > References từ cửa sổ mã.

Kiểm tra mã

Gán nút trên “Main” cho Sổ làm việc phụ. Lưu sổ làm việc dưới dạng “Dân số Mẫu.xlsm”.

ĐÓNG sổ làm việc và mở lại.

Nhấn nút xem kết quả. Tăng số lượng hàng dữ liệu trong “Cơ sở dữ liệu” và chạy lại, xem Biểu đồ đã được cập nhật thông tin bổ sung chưa.

Trong đoạn mã trên, chúng tôi đã hiển thị mẫu sớm, với XL.Visible = Đúng. Trong môi trường trực tiếp, việc này có thể được thực hiện ở giai đoạn cuối để không hiển thị các cập nhật trên màn hình.

Xử lý thảm họa dữ liệu!

Ít có điều gì gây khó chịu hơn việc một tệp Excel đã được xây dựng tỉ mỉ lại bị lỗi, làm hỏng tệp nguồn và không có bản sao lưu nào. Trong những trường hợp như vậy, nếu Excel không thể khôi phục tệp bị hỏng, tất cả công việc đã thực hiện trên đó sẽ bị mất trừ khi bạn có sẵn một công cụ nào đó để khắc phục. sửa excel các tập tin.

Cũng nên thận trọng khi thường xuyên sao lưu các công việc có giá trị.

Giới thiệu tác giả:

Felix Hooker là một chuyên gia phục hồi dữ liệu trong DataNumen, Inc., công ty hàng đầu thế giới về công nghệ khôi phục dữ liệu, bao gồm sửa chữa rar và các sản phẩm phần mềm phục hồi sql. Để biết thêm thông tin, hãy truy cập www.datanumennăm