การจัดรูปแบบข้อมูล 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

หลักการ

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

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

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

ดาวน์โหลดไฟล์ Excel คลิกที่นี่

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

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