[Help] Tạo excel template có chứa maro và auto load template trong excel 2007/2010 (1 người xem)

[bugatino]

Chào các bạn trên diễn đàn Giải pháp excel. Mình có 1 vấn đề cần sự trợ giúp từ các bạn.

Mình có tạo 1 template có macro trong đó và sử dụng Custom UI Editor for MS để tạo custom menu. Mục đích của mình như sau:
+ khi ta mở một file excel bất kỳ, thì trên menu ribbon sẽ xuất hiện custom menu mà mình tạo. (nếu như mở file bất kỳ thì ko thấy hiện custom menu mình tạo, còn khi mở trực tiếp file template thì mới hiện ra custom menu.)
+ Chạy macro gán vào menu để thực hiện nhiệm vụ định trước.

File template và các macro mình tạo có tác dụng đổi tên sheet 1 thành Questions, insert 1 sheet có tên là Auto Quiz Creator bên cạnh Sheet Questions và lấy dữ liệu từ sheet Question này sang để xử lý theo các hàm đã nhập trước.

Mình làm theo hướng dẫn tạo template trên mạng: Xoá hết các sheet đi, chỉ để lại 1 sheet có chứa công thức. Nhưng khi mở ra thì file template của mình bị lỗi #REF! do sai tham chiếu.

Mình phải làm thế nào để khắc phục tình trạng trên:
+ Khi mở excel lên có custom menu
+ Insert đc template và lấy thông tin từ sheet 1 (Question) sang để xử lý.

Cám ơn các bạn rất nhiều.

 

[bugatino]

Mình đã tự tìm phương pháp xử lý và đã thành công. Mình làm theo cách sau:
+ Dùng Macro thêm vào 1 Sheet mới, và đổi tên sheets(1) thành tên sheet muốn tham chiếu.
+ Dùng Macro ghi formula vào trong các cells ở sheet mới thêm vào.

Ví dụ: Bạn muốn tạo 1 sheet mới có tên là Auto Quiz Creator chứa sẵn Formula tham chiếu tới sheet 1 có tên là Questions. Chúng ta sẽ sử dụng macro như sau:

Sub Insert_Sheet_Template(control As IRibbonControl)
   
    Dim numQz As Integer 'numQz là số row trong sheet(1)
    Dim shName As String 'shName là tên của Sheet mới
    numQz = Application.WorksheetFunction.CountA(Range("A:A"))
    'Get name of sheet.
    shName = Left(Sheets(1).Range("A1"), InStr(1, Sheets(1).Range("A1"), "-") - 1) & "-XML"
   
   
    If ActiveSheet.Name = "Questions" Then
        'Insert new template sheet
        Sheets.Add After:=Sheets(Sheets.Count)
        
        'Neu co loi
        On Error GoTo catchErr
        ActiveSheet.Name = shName
    Else
        'Renames first worksheet tab to Questions.
        Sheets(1).Name = "Questions"
        'Insert new template sheet
        Sheets.Add After:=Sheets(Sheets.Count)
        
        'Neu co loi
        On Error GoTo catchErr
        ActiveSheet.Name = shName
    End If
    
    'Dien du lieu vao cac Cells
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "<BeginQuiz>"
    
    'Add category
    Range("A2").Select
    ActiveCell.Formula = "=IF(LEN(Questions!A1)>1,LEFT(Questions!A1,FIND(""-"",Questions!A1,1)-1)&""_TOP""&""/""&LEFT(Questions!A1,FIND(""-"",Questions!A1,1)-1)&""_""&""B""&MID(Questions!A1,FIND(""-"",Questions!A1,1)+2,1),"""")"
    Range("A2").Select
    Selection.AutoFill Destination:=Range("A2:A" & numQz), Type:=xlFillDefault
    
    'Add Question ID
    Range("B2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!A1)>1,""<QID>""&Questions!A1&""</QID>"","""")"
    Range("B2").Select
    Selection.AutoFill Destination:=Range("B2:B" & numQz), Type:=xlFillDefault
    
    'Add Question Text
    Range("C2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!B1)>1,""<QTEXT>""&Questions!B1&""</QTEXT>"","""")"
    Range("C2").Select
    Selection.AutoFill Destination:=Range("C2:C" & numQz), Type:=xlFillDefault
    
    'Add Correct FB
    Range("D2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!H1)>1,""<CorrectFB>""&Questions!H1&""</CorrectFB>"","""")"
    Range("D2").Select
    Selection.AutoFill Destination:=Range("D2:D" & numQz), Type:=xlFillDefault
    
    'Add Incorrect FB
    Range("E2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!H1)>1,""<IncorrectFB>""&REPLACE(Questions!H1,1,7,""Sai"")&""</IncorrectFB>"","""")"
    Range("E2").Select
    Selection.AutoFill Destination:=Range("E2:E" & numQz), Type:=xlFillDefault
    
    'Add PAD/PAS
    Range("F2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!C1)>1,IF(LEFT(Questions!C1,1)=Questions!$G1,""<PAD>""&REPLACE(Questions!C1,1,3,"""")&""</PAD>"",""<PAS>""&REPLACE(Questions!C1,1,3,"""")&""</PAS>""),"""")"
    Range("F2").Select
    Selection.AutoFill Destination:=Range("F2:F" & numQz), Type:=xlFillDefault
    
    'Add PAD/PAS
    Range("G2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!D1)>1,IF(LEFT(Questions!D1,1)=Questions!$G1,""<PAD>""&REPLACE(Questions!D1,1,3,"""")&""</PAD>"",""<PAS>""&REPLACE(Questions!D1,1,3,"""")&""</PAS>""),"""")"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:G" & numQz), Type:=xlFillDefault
    
    'Add PAD/PAS
    Range("H2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!E1)>1,IF(LEFT(Questions!E1,1)=Questions!$G1,""<PAD>""&REPLACE(Questions!E1,1,3,"""")&""</PAD>"",""<PAS>""&REPLACE(Questions!E1,1,3,"""")&""</PAS>""),"""")"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H" & numQz), Type:=xlFillDefault
    
    'Add PAD/PAS
    Range("I2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!F1)>1,IF(LEFT(Questions!F1,1)=Questions!$G1,""<PAD>""&REPLACE(Questions!F1,1,3,"""")&""</PAD>"",""<PAS>""&REPLACE(Questions!F1,1,3,"""")&""</PAS>""),"""")"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I" & numQz), Type:=xlFillDefault

    'Add End Question
    Range("J2").Select
    ActiveCell.Formula = _
        "=IF(LEN(Questions!A1)>1, ""</EndQuestion>"","""")"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J" & numQz), Type:=xlFillDefault
    
    'Add EndQuiz
    Range("A" & numQz + 1).Select
    ActiveCell.Formula = "</EndQuiz>"
    
    Columns("A:A").ColumnWidth = 10.86
    Columns("B:B").ColumnWidth = 32.43
    Columns("C:C").ColumnWidth = 27
    Columns("D:D").ColumnWidth = 32.86
    Columns("E:E").ColumnWidth = 29
    Columns("F:F").ColumnWidth = 5.14
    Columns("G:G").ColumnWidth = 5.14
    Columns("H:H").ColumnWidth = 5.14
    Columns("I:I").ColumnWidth = 5.14
    Columns("J:J").ColumnWidth = 14.71
    
    MsgBox "Them sheet " & shName & " thanh cong.", vbOKOnly, "Thanh cong."
   Exit Sub
catchErr:
MsgBox "Sheet co ten la " & shName & " da ton tai." & vbCr _
& "Ban can xoa sheet nay truoc khi tao them sheet moi", vbOKOnly, "Error"
End Sub

Đoạn Macro trên mình sử dụng Formula cho mục đích của mình. Bạn nào muốn dùng cho mục đích khác thì thay Formula đi nhé. Hi vọng mình giúp đc các bạn phần nào.