แก้ไข: Run Time Error 1004 ใน Excel

Run-time error 1004 เป็นรหัสข้อผิดพลาดของ Microsoft Visual Basic ที่ส่งผลต่อผู้ใช้ Excel พบมากใน Excel 2003 และ 2007 แท้แท้ทุกเวอร์ชันก็อาจเจอได้ ผู้ใช้มักเจอ 2 รูปแบบของข้อผิดพลาดนี้:

"Run-time error '1004':
Copy Method of Worksheet Class failed"

"Run-time error '1004':
Application-defined or object-defined error"

ข้อความผิดพลาดอาจแตกต่างเล็กน้อยแต่รหัส 1004 เหมือนกัน ปัญหาเกือบทุกครั้งเกิดขณะรันแมโครที่คัดลอกเวิร์กชีตแล้ววางในสมุดงานเดียวกัน

สาเหตุของ Run-time error 1004 ใน Excel ทราบแล้ว: เกิดเมื่อแมโครคัดลอกเวิร์กชีตต้นฉบับไปยังสมุดงานที่มีชื่อเฉพาะ (defined name) แต่ยังไม่ได้บันทึกและปิดก่อนรันแมโคร ตัวอย่างโค้ดที่ทำให้เกิดข้อผิดพลาด:

Sub CopySheetTest() Dim iTemp As Integer Dim oBook As Workbook Dim iCounter As Integer ' Create a new blank workbook: iTemp = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Set oBook = Application.Workbooks.Add Application.SheetsInNewWorkbook = iTemp ' Add a defined name to the workbook ' that RefersTo a range: oBook.Names.Add Name:="tempRange", _ RefersTo:="=Sheet1!$A$1" ' Save the workbook: oBook.SaveAs "c:\test2.xls" ' Copy the sheet in a loop. Eventually, ' you get error 1004: Copy Method of ' Worksheet class failed. For iCounter = 1 To 275 oBook.Worksheets(1).Copy After:=oBook.Worksheets(1) Next End Sub

เนื่องจากทราบสาเหตุแล้ว จึงมีวิธีแก้และวิธีหลีกเลี่ยง (workaround)

วิธีแก้ (The Solution):

แก้โดยแก้โค้ดแมโคร ให้บันทึกและปิดสมุดงานเป้าหมายเป็นระยะขณะคัดลอก โค้ดตัวอย่าง:

Sub CopySheetTest() Dim iTemp As Integer Dim oBook As Workbook Dim iCounter As Integer ' Create a new blank workbook: iTemp = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 1 Set oBook = Application.Workbooks.Add Application.SheetsInNewWorkbook = iTemp ' Add a defined name to the workbook ' that RefersTo a range: oBook.Names.Add Name:="tempRange", _ RefersTo:="=Sheet1!$A$1" ' Save the workbook: oBook.SaveAs "c:\test2.xls" ' Copy the sheet in a loop. Eventually, ' you get error 1004: Copy Method of ' Worksheet class failed. For iCounter = 1 To 275 oBook.Worksheets(1).Copy After:=oBook.Worksheets(1) 'Uncomment this code for the workaround: 'Save, close, and reopen after every 100 iterations: If iCounter Mod 100 = 0 Then oBook.Close SaveChanges:=True Set oBook = Nothing Set oBook = Application.Workbooks.Open("c:\test2.xls") End If Next End Sub

หมายเหตุ: จำนวนครั้งที่คัดลอกได้ก่อนต้องบันทึก/ปิด แตกต่างกันไป ขึ้นอยู่กับขนาดเวิร์กชีต

วิธีหลีกเลี่ยง (The Workaround):

ใช้การแทรกเวิร์กชีตใหม่จากเทมเพลต แทนการคัดลอกเวิร์กชีตเดิม ขั้นตอน:

1. เปิด Excel
2. สร้างสมุดงานใหม่ ลบ เวิร์กชีตทั้งหมดยกเว้นหนึ่งแผ่น
   
3. จัดรูปแบบ สมุดงาน
4. เพิ่มข้อความ ข้อมูล หรือแผนภูมิที่ต้องการในเทมเพลต ไว้ในแผ่นเดียวที่เหลือ
5. Excel 2003 หรือเก่า: File > Save As. Excel 2007 ขึ้นไป: ปุ่ม Microsoft Office > Save As
   
6. ในช่อง File name: พิมพ์ชื่อเทมเพลต
7. เมนู Save as type: เลือก Excel Template (.xlt) สำหรับ 2003 หรือ Excel Template (.xltx) สำหรับ 2007 ขึ้นไป
   
8. คลิก Save
   
9. หลังสร้างเทมเพลตเสร็จ ใช้โค้ดนี้แทรกผ่านโปรแกรม:
   Sheets.Add Type:=path\filename

หมายเหตุ: ในโค้ดด้านบน path\filename ต้องแทนที่ด้วยพาธเต็ม (รวมชื่อไฟล์) ของเทมเพลตที่สร้างไว้