วันพุธที่ 27 พฤษภาคม พ.ศ. 2558

การจัดรูปแบบข้อมูล Excel ด้วย VBA และนำเข้าไปยัง Access

การถ่ายโอนข้อมูล จาก Excel 2010 ไปยัง Access 2010 สามารถทำได้หลายวิธี เช่น การใช้เมนู ข้อมูลภายนอก หรือ External Data แต่ปัญหาอยู่ที่การจัดโครงสร้างของข้อมูล ถ้าโครงสร้างระหว่าง Excel และ Access ไม่ตรงกัน การถ่ายโอนก็จะทำได้ค่อนข้างลำบาก ทั้งนี้เพราะ การถ่ายโอนข้อมูลจาก Excel ไปยัง Access นั้น หัวตารางของ Excel จะต้องเหมือนกับชื่อ ฟิลด์ในตารางที่จะนำข้อมูลเข้า หรือมิฉะนั้นก็นำเข้าเป็นตารางใหม่ แล้วค่อยจัดการภายหลัง

ในตัวอย่างนี้จะเป็นการถ่ายโอนเข้าตารางที่มีอยู่แล้ว

ตัวอย่าง
ต้องการถ่ายโอนข้อมูล ใน Excel ลงใน ตาราง ของ Access ต่อไปนี้

โครงสร้างของตาราง Access ที่ต้องการถ่ายโอนข้อมูลเข้ามา มีดังนี้
จะเห็นว่า โครงสร้างของข้อมูลใน Excel ไม่เหมือนกับใน Access ซึ่งเป็นเรื่องธรรมดา การถ่ายโอนข้อมูล จาก Excel จึงจำเป็นต้องจัดรูปแบบของข้อมูลใน Excel ให้เหมือนในตาราง ของ Access โดยถ้าจะให้ดี ก็ต้องเพิ่มชื่อหัวตาราง ให้เหมือนกับชื่อฟิลด์ในตารางของ Access ก็จะทำให้การถ่ายโอน สะดวก และง่ายได้ รูปแบบที่ต้องการ คือ

ฟิลด์ ID เป็น AutoNumber จึงไม่ต้องนำเข้าจาก Excel

วิธีการจัดรูปแบบตามที่ต้องการที่ง่ายที่สุด คือ การลากทีละเซลล์ มาวางในตำแหน่งที่ต้องการ และพิมพ์เพิ่มหัวตาราง

วิธีข้างต้น เป็นวิธีง่าย แต่ทำยาก โดยเฉพาะถ้ามีข้อมูลจำนวนมาก ๆ อาจจะต้องใช้เวลาหลายวัน และมีข้อผิดพลาดได้ง่าย

วิธีที่ดี คือการใช้ VBA คัดลอกข้อมูล และนำมาวางในตำแหน่งที่ต้องการ ซี่งมีโค้ด ดังนี้

Sub formatForAccess()

Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim thisName As String
Dim thisDate As Date

k = 5  'กำหนด แถวที่ 5 เป็นจุดเริ่มต้นการ Past
For i = 2 To 4 'จำนวนแถวที่มีข้อมูลที่ต้องการคัดลอก คือ ตั้งแต่แถวที่ 2 ถึงแถวที่ 4
        For j = 1 To 5 + 1 'จำนวนคอลัมน์ที่มีข้อมูล ตั้งแต่คอลัมน์ที่ 1 ถึงที่ 5 บวกไปเพื่อทดแทนกับที่มีการข้าม loop
            Sheets("Sheet1").Select
                If j = 1 Then 'ขึ้นแถวใหม่ หาชื่อคน
                    thisName = Cells(i, j).Value
                    j = j + 1  'ข้ามไป เพราะจะไม่พิมพ์ชื่อคนในตำแหน่งนี้
                End If
                Cells(i, j).Select
                Selection.Copy
                If Selection.Value <> "" Then  'ถ้าเป็นเซลล์ที่ไม่มีข้อมูล จะไม่คัดลอก
                         Sheets("Sheet2").Select
                            Cells(k, 2).Value = thisName  ' พิมพ์ชื่อคน ในคอลัมน์ที่ 2 ของ Sheet2
                         Cells(k, 3).Select
                         If j <> 1 Then
                            ActiveSheet.Paste
                         End If
                         Sheets("Sheet1").Select
                         Cells(1, j).Select  'แถวบนสุด คือว้นที่ คัดลอกมาในตำแหน่งที่ตรงกับข้อมูลในแถวนั้น ๆ
                         Selection.Copy
                         Sheets("Sheet2").Select
                         Cells(k, 4) = Format(Date, "yyyy-mm-dd") 'จัดรูปแบบของเซลล์ให้เป็นวันที่ short date
                         Cells(k, 4).Select
                         ActiveSheet.Paste
                         k = k + 1  ' เพิ่มค่า เพื่อให้เลื่อนลงมาอีกแถว ใน Sheet2 เพื่อเตรียม Paste
                End If
        Next j
Next i

Sheets("Sheet1").Select
Cells(1, 1).Select
Application.CutCopyMode = False  'ยกเลิกการคัดลอก (เส้นประ)


End Sub

หลักการ

  1. เป็นการจัดโครงสร้างรูปแบบข้อมูลใน Sheet1 โดยนำไปไว้ที่ Sheet2 เพื่อให้สามารถนำเข้า Access ได้
  2. ใช้วิธีการวน 2 ชั้น คือ วนไปตามคอลัมน์ และเมื่อจบแถวที่ 1 ซึ่งเป็นข้อมูลของคนที่ 1 แล้ว จึงเลื่อนมายังแถวที่ 2 ซึ่งเป็นข้อมูลของคนที่ 2  ทั้งนี้ โดยใช้ตัวแปร เพื่อกำหนดตำแหน่งของ คอลัมน์และแถว เพื่อให้สะดวกในการวน และเป็นการจำตำแหน่งเดิมไว้ด้วย
  3. เนื่องจากข้อมูลของแต่ละคนไม่เท่ากัน ดังนั้น เมื่อไปถึงแต่ละเซลล์ จึงต้องมีการตรวจสอบว่า มีข้อมูลหรือไม่ ถ้าไม่มีข้อมูล ก็ไม่ต้องทำอะไร
  4. คำสั่งหลักที่ใช้ในการระบุตำแหน่ง คือ Cells(Row, Column) เช่น Cells(2, 4) คือ แถวที่ 2 คอลัมน์ที่ 4


วิธีการใช้งาน

  1. เรียกใช้ Developer tab หรือ นักพัฒนา
  2. กดปุ่ม Alt + F11 เพื่อเปิดหน้าต่างเขียนโค้ด คลิก Insert > Module และวางโค้ดที่นี่
  3. สร้าง ปุ่ม บน Sheet1 ซึ่งมีข้อมูลที่ต้องการจัดรูปแบบ 
  4. เมื่อสร้างปุ่มครั้งแรก โปรแกรมจะมีหน้าจอให้เขียนโค้ด ให้เลือก สร้าง
  5. พิมพ์ชื่อ Sub ที่คัดลอกมาวางไว้
  6.  เมื่อคลิกปุ่ม โปรแกรมจะทำงานทันที
  7. เมื่อเสร็จแล้ว ให้ไปที่ Sheet2 และพิมพ์หัวตารางให้เหมือนฟิลด์ ในตารางของ Access ตามภาพข้างบน
  8. บันทึกเป็นไฟล์ xlsm เนื่องจากมีการใช้โค้ด VBA
ดาวน์โหลดไฟล์ Excel คลิกที่นี่



วิธีการนำเข้า Access

  1. เปิดโปรแกรม Access ที่มีตารางข้อมูล ที่จะนำข้อมูลนี้เข้าไปเพิ่มเติม
  2. ไปที่ ข้อมูลภายนอก เลือก Excel
  3. เลือกผนวกไฟล์ และเลือกไฟล์ Excel ที่จัดข้อมูลแล้ว (ถ้าใช้โค้ดข่างบนจัดโครงสร้างใหม่ ข้อมูลจะอยู่ใน Sheet2)
  4. คลิกตกลง
  5. คลิกเลือก Sheet2 เพื่อเลือกข้อมูลที่จัดรูปแบบแล้ว
  6. คลิก ตกลง
  7. ข้อมูลจะถูกนำไปต่อท้ายข้อมูลเดิมที่มีอยู่แล้ว
  8. เป็นอันเสร็จการถ่ายโอนข้อมูล จาก Excel ไปยัง Acess





ไม่มีความคิดเห็น:

แสดงความคิดเห็น