หากมีคำถาม ขอให้ไปโพสต์ลง เว็บบอร์ดจีทูจีเน็ตดอตคอม ตัวใหม่แทนน่ะครับ

หรือติดต่อเข้ามาทาง Inbox ที่ เฟซบุ๊ค ผมครับ

หน้าหลัก
ข่าวสาร - บทความ ทั้งหมด
VB 6/VB.Net
ASP/ASP.Net
จับฉ่ายคอมพิวเตอร์
เรียนรู้ผ่าน Flash Movie
บทความที่มีผู้ตอบล่าสุด  
 RSS Feeds
 ดาวน์โหลดโปรแกรม RSS Reader ได้ที่นี่ ...   Download โปรแกรม RSS Reader

Forum - www.g2gnet.com
Webmaster - www.g2gnet.com
Visitors - Session views
 5 2 6 1 1 2 5

7 ธันวาคม พ.ศ.2549
1232 Users On-Line.
Visitors - Page views
 8 5 8 7 7 8 7
1 กุมภาพันธ์ พ.ศ.2551

Google   
เว็บ g2gnet.com
ขนาดตัวอักษร:  

การอ่านข้อมูลจาก MS Excel เข้าสู่ MS Access (ADO กับ MS Excel ภาค 2) +SourceCode

Category »  VB 6/VB.Net
โดย : Webmaster เมื่อ 7/7/2550 23:59:00
(อ่าน : 29701) 

แต่ขอบอกไว้ก่อนน่ะครับ เวลาเขียนโปรแกรมเพื่อใช้กับระบบงานจริงๆ มันไม่ได้ Simple แบบนี้หรอกครับ (ดูตัวอย่างประกอบทางด้านล่างโน่นครับ) ยิ่งบางงานที่ต้องอ่านโครงสร้างข้อมูลแบบ Dynamics นั่นก็ต้องอาศัย ADOX เข้ามาใช้งานอีก (โอกาสหน้าจะนำเสนอเรื่องนี้ ADOX ครับพี่น้อง) แต่ก็หวังว่าจะเป็นแนวทางให้พี่น้อง ลองทำความเข้าใจกันได้ในระดับหนึ่ง (ระดับไหนล่ะ) โปรดติดตามข่าวนี้อย่างใกล้ชิด (แล้วจะให้มันใกล้แค่ไหน) ... ไอ้ที่ต่อท้ายมาเนี่ย แบบว่ารำคาญ และ ไม่ค่อยเข้าใจตอนเวลาเขาอ่านข่าวกันน่ะครับ ... พี่น้อง

การอ่านข้อมูลจาก MS Excel เข้าสู่ MS Access
การอ่านข้อมูลจาก MS Excel เข้าสู่ MS Access
ให้เรามอง Sheet ใน MS Excel เป็นเสมือนตารางข้อมูลด้วยน่ะครับ

การอ่านข้อมูลจาก MS Excel
การใช้ ADO กับ Excel นี่มันจะมองเห็นแถวแรกที่ไม่ใช่แถวว่างเป็นเสมือนฟิลด์ของฐานข้อมูลเสมอครับ ... อัจฉริยะจริงๆเล้ยพ่อคุณ
ดังนั้นผลของการอ่านข้อมูลจะไม่ต่างจากภาพทางด้านบนเลย

ออกแบบตาราง (Table) เพื่อทำการเก็บข้อมูล
ออกแบบตาราง (Table) แบบอัตโนมือเพื่อทำการเก็บข้อมูล

เนื่องจากเรารู้ว่าไฟล์ Excel นั้นมันมีอยู่ Sheet เดียว (หรือ 1 ตาราง) ดังนั้นปลายทางที่ต้องการเก็บข้อมูลก็จะมี 1 ตารางด้วย การออกแบบฐานข้อมูลเพื่อมารองรับค่าต่างๆเหล่านี้ เราก็สามารถออกแบบ (Design) แบบอัตโนมือได้เลยครับ ถึงแม้ว่ามันจะมีอยู่เพียงตารางเดียว แต่ ... วิธีการนำเข้าข้อมูลด้วยการโค้ดโปรแกรมเนี่ย มันมีความยืดหยุ่น (Dynamics) กว่าการตัดแปะจากไฟล์ Excel เข้าไปยัง Access โดยตรงเป็นไหนๆล่ะกัน (ก็ลองพิจารณาจากภาพแล้วกันครับ ... ว่ามันจะต่างกันตรงไหน) ที่สำคัญเราได้เรียนรู้กระบวนการทำงานของโปรแกรม MS Visual Basic ไปด้วยเน้อ พี่น้อง

Design
Design

Project --> References
Project --> References

Project --> Components
Project --> Components

Project
Project

ทำความใจพื้นฐานกันอีกนิดนึง (ที่เหลือก็ลองปรับแต่ง แก้ไขตามใจชอบเอาน่ะครับ ... ลองเล่นดูเอาเหอะ)

  • สร้าง Connection ชื่อ Conn แบบ Local มองเห็นเฉพาะในฟอร์ม frmExcel2Access.frm เพื่อเชื่อมต่อกับไฟล์ต้นทาง (MS Excel)
  • สร้าง Connection ชื่อ ConnMyDB แบบ Global อยู่ที่ Module --> gMyDB.bas จากตัวอย่างเดิมๆ เพื่อเชื่อมต่อไฟล์ปลายทาง (MS Access)
  • ประกาศตัวแปรแบบ Global ในการเชื่อมต่อกับตาราง (RecordSet) ที่ Module --> gMyDB.bas
    • RS สำหรับตารางต้นทางใน MS Excel
    • DS สำหรับตารางปลายทางใน MS Access

     

       ดาวน์โหลด Source Code สำหรับ MS Visual Basic 6.0 - Service Pack 6
       ดาวน์โหลดไฟล์ข้อมูลไปรษณีย์ทั่วไทย ต้นฉบับจาก บ.ไปรษณีย์ไทยจำกัด (Microsoft Excel) (ต้องเอาไปด้วยน่ะครับ)
    
    ' เรียกได้ว่าเป็น Module แบบหากินจริงๆครับ พี่น้อง
    Option Explicit
    
    Global ConnMyDB As New ADODB.Connection
    Global RS As New ADODB.Recordset    ' ตัวหลัก
    Global DS As New ADODB.Recordset    ' ตัวรอง
    Global Statement As String
    '
    Public Sub OpenDataBase()
    On Error GoTo Err_Handler
    Dim DB_File As String
        DB_File = App.Path
        If Right$(DB_File, 1) <> "\" Then DB_File = DB_File & "\"
        DB_File = DB_File & "PostCode.MDB"
        ' Open a connection.
        Set ConnMyDB = New ADODB.Connection
        ConnMyDB.ConnectionString = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & DB_File & ";" & _
            "Persist Security Info=False"
        ConnMyDB.Open
        Exit Sub
    Err_Handler:
        MsgBox "Error : " & Err.Number & " " & Err.Description
        End
    End Sub
    
     Public Sub CloseDataBase()
        ' ตรวจสอบว่ามีการเชื่อมโยง - Connect ข้อมูลหรือไม่
        If ConnMyDB.State = adStateOpen Then
            ConnMyDB.Close
            Set ConnMyDB = Nothing
        End If
    End Sub
    
    Form_Load
    Form_Load
    
    Private Sub Form_Load()
        txtPathNameXLS.Text = ""
        cmbWorkSheet.Clear
        lblDescription.Caption = "โปรแกรมตัวอย่างการอ่านข้อมูล MS Excel เข้าสู่ MS Access - www.g2gnet.com"
        Me.Move (Screen.Width - Width) \ 2, (Screen.Height - Height) \ 2
        ' นับจากนี้ไปก็เป็นการควบคุมโปรแกรมแบบ Event/Driven แล้วครับ
    End Sub
    
    เปิดไฟล์ MS Excel โดยการกดปุ่ม cmdOpenXLS
    เปิดไฟล์ MS Excel โดยการกดปุ่ม cmdOpenXLS
    
    ' เริ่มต้นการเปิดไฟล์ Excel
    Private Sub cmdOpenXLS_Click()
    ' แบบไม่สนใจ Error สั่งให้ทำงานต่อ ... แบบนี้ไม่ค่อยดีเท่าไรนักหรอกครับ
    'On Error Resume Next
    
    ' อันนี้คอยดัก Error
    On Error GoTo ErrHandler
        With dlgOpenFile
            .DialogTitle = "เลือกไฟล์ Microsoft Excel"
            .InitDir = App.Path
            ' เลือกเฉพาะไฟล์ Excel
            .Filter = "All Microsoft Excel Files (*.xls)|*.xls"
            
            ' ผมตั้งไว้เพื่อดักการกดปุ่ม Cancel ตอนเลือกไฟล์ครับ ซึ่งใช้ร่วมกับ On Error GoTo ErrHandler
            ' และต้องสั่งให้ dlgOpenFile.CancelError = True
            ' เพื่อให้เกิดการแจ้ง Error  โดย Err.Number = 32755 หมายความว่าเกิดการกดปุ่ม Cancel
            ' ตรงนี้ผมอธิบายให้ลึกซึ้งมันยากครับ โปรดลองเล่นดูเอาล่ะกัน
            .CancelError = True
            .ShowOpen
            If .FileName <> "" Then txtPathNameXLS.Text = .FileName
        End With
        
        ' ไม่มีชื่อไฟล์กลับมาน่ะขอรับ ดังนั้นจะให้มันไปฟังค์ชั่น Connect ทำไมให้เกิด Error เล่า ... พี่น้อง
        If txtPathNameXLS.Text = "" Then Exit Sub
        '
        ' พิจารณาดูน่ะครับว่าหากมีการเรียกใช้โปรแกรมย่อย (Sub หรือ Fucntion) เรียงต่อๆกันไปนี่
        ' บางทีก็หาจุดของการเกิด Error ลำบากเหมือนกัน
        ' ซึ่งนี่แหละ ... ทำไมในงานนี้จึงต้องคอยดัก Error ซ่ะเหลือเกิน
        If Connect Then ' มีการเรียกโปรแกรมย่อย Connect และเกิดการส่งค่ากลับมาแบบ Boolean
            cmbWorkSheet.Clear
            ' อ่านรายชื่อ WorkSheet หรือ ตาราง (Table) เข้าสู่ ComboBox
            Call GetExcelTables
        ' กรณีเกิดการส่งค่ากลับมาเป็น False แล้วจะทำอะไรล่ะ ?????
        Else
            ' ลองคิดดูด้วย
        End If
    
    ExitProc:
        Exit Sub
    
    ErrHandler:
        Select Case Err.Number
            Case 32755
                Err.Clear
                Resume ExitProc
            ' หรือคอย Trap Error ตัวอื่นๆ
            ' Case xxxx
                ' แจ้งความผิดพลาดเกี่ยวกับอะไร ...
            
            Case Else
                MsgBox Err.Number & vbCrLf & Err.Description
            End Select
            ' การใช้ Resume คือการกระโดดไปแบบมีเงื่อนไข (การเกิด Error นี่แหละคือเงื่อนไขของมัน)
            ' ส่วน GoTo มันกระโดดไปแบบไม่มีเงื่อนไขเลยครับ ... พี่น้อง
            Resume ExitProc
        
    End Sub
    
    ' ฟังค์ชั่นที่ใช้ในการเปิดไฟล์ MS Excel
    ' และคืนค่ากลับ "จริง" หรือ "เท็จ" เพื่อแจ้งสถานะของการติดต่อไฟล์ด้วย
    Private Function Connect() As Boolean
    On Error GoTo ErrorHandler
        Set Conn = New ADODB.Connection
        With Conn
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            .ConnectionString = "Data Source=" & txtPathNameXLS.Text & ";Extended Properties=Excel 8.0;"
            .Open
        End With
        
        ' แสดงว่าสามารถเปิดไฟล์ MS Excel ได้ (หรือ Connect - เชื่อมต่อได้) ก็แจ้งกลับด้วยสถานะที่เป็นจริง
        Connect = True
    ExitProc:
        Exit Function
        
    ErrorHandler:
        ' แจ้งสถานะของความผิดพลาด (Trap Error)
        MsgBox Err.Number & vbCrLf & Err.Description, vbOKOnly + vbCritical, "Error: ฟังค์ชั่น Connect"
        
        ' การติดต่อล้มเหลวผิดพลาด ต้องส่งค่ากลับเป็น False
        Connect = False
        
        ' การใช้ Resume ในกรณีที่เกิด Error ขึ้นมา ส่วน GoTo มันกระโดดไปแบบไม่มีเงื่อนไขเลยครับ ... พี่น้อง
        Resume ExitProc
    End Function
    
    ' ทำการอ่าน WorkSheet หรือเสมือนกับว่ามันคือตารางข้อมูล (Table) นั่นแหละครับพี่น้อง
    Private Sub GetExcelTables()
        ' ตัวแปรนี้ได้ย้ายไปไว้ที่ Module แล้วน่ะครับ
        Set RS = New ADODB.Recordset
        With Conn
            ' อ่านค่า Sheet ที่คุณเลือกเข้าสู่ RecordSet (มองเหมือนรูปแบบของตาราง - SchemaTables)
            Set RS = .OpenSchema(adSchemaTables)
        End With
        '
        ' Loop ไปเรื่อยๆ จนกว่าจะหมดจำนวนของ WorkSheet
        Do While Not RS.EOF
            ' นำชื่อ WorkSheet (หรือ ชื่อตาราง) มาใส่ไว้ใน ComboBox
            cmbWorkSheet.AddItem (RS.Fields("TABLE_NAME").Value)
            RS.MoveNext
        Loop
        '
    End Sub
    
    ติดต่อกับตาราง หรือ Sheet เมื่อเกิดเหตุการณ์ cmbWorkSheet_Click
    ติดต่อกับตาราง หรือ Sheet เมื่อเกิดเหตุการณ์ cmbWorkSheet_Click
    
    Private Sub cmbWorkSheet_Click()
        If cmbWorkSheet.ListIndex < 0 Then Exit Sub
        ' อ่านข้อมูลจาก WorkSheet เข้าสู่ตารางของ MS Access
        Call GetExcelData
    End Sub
    
    ' อ่านข้อมูลที่อยู่ในเซลล์ต่างๆเข้าสู่ตารางของ MS Access
    ' เหมือนอ่านข้อมูลออกจากตาราง (Table) ใน MS Access ที่เราคุ้นเคยยังไงยังงั้นครับ ... พี่น้อง
    Private Sub GetExcelData()
        ' ไว้ใช้เป็น Primary Key
        Dim iRec As Long
        
        Set RS = New ADODB.Recordset
        With RS
            .ActiveConnection = Conn
            .CursorLocation = adUseClient
            .CursorType = adOpenStatic
            .LockType = adLockReadOnly
            ' กำหนด SQL Statement
            .Source = "SELECT * FROM [" & cmbWorkSheet.Text & "]"
            .Open
        End With
        ' หรือ
        'RS.Open Conn, "SELECT * FROM [" & cmbWorkSheet.Text & "]", _
                            adOpenForwardOnly, adLockReadOnly, adCmdText
        
        ' เชื่อมต่อเข้ากับไฟล์ฐานข้อมูล MS Access ที่จะทำการรับค่าจากตารางของ MS Excel
        Call OpenDataBase
    
        ' อันที่จริงควรลบข้อมูลในตารางเดิมออกก่อนด้วยน่ะครับ ...
        'Statement = "DELETE * FROM PostCode"
        'Set DS = ConnMyDB.Execute(Statement)
        
        ' อ่านข้อมูลเข้าตารางของ MS Access โดยกำหนดให้ DS เป็นตาราง (RecordSet) ที่รับค่า
        ' นี่แหละครับในโปรเจคงานแต่ละตัวของผม สิ่งที่ทำประจำคือ
        ' RS เป็น RecordSet ตัวหลัก
        ' DS เป็น RecordSet ตัวรอง
        ' ซึ่งทั้งสองจะเป็นตัวแปรแบบ Global นั่นคือสามารถมองเห็นตัวแปรชนิดนี้ได้ในทุกๆฟอร์ม
        ' แต่หากไม่จำเป็นจริงก็อย่าประกาศตัวแปรแบบ Global ให้มันฟุ่มเฟือยนักน่ะครับ ... พี่น้อง
        Set DS = New Recordset
        Statement = "SELECT * FROM PostCode "
        DS.Open Statement, ConnMyDB, adOpenKeyset, adLockOptimistic, adCmdText
        ' adOpenKeySet และ adLockOptimistic เราใช้ในกรณีที่ต้องเขียนข้อมูลลงไป
        
        ' ตั้งค่าเริ่มต้นให้ตัวแปร iRec
        iRec = 1
        Do Until RS.EOF
            DS.AddNew
            ' iRec นี่แหละคือการสร้าง Primary Key
            DS("PostCodeID") = iRec
            ' Trim คือคำสั่งที่ใช้ในการตัดช่องว่าง(Space - CHR(32)) ทั้งด้านหน้า และ ด้านหลังของข้อมูล
            DS("PostCode") = "" & Trim(RS("PostCode"))
            DS("Tumbon") = "" & Trim(RS("Tumbon"))
            DS("Amphur") = "" & Trim(RS("Ampur"))
            DS("Province") = "" & Trim(RS("Province"))
            DS("Note") = "" & Trim(RS("Remark"))
            ' เพิ่มฟิลด์เอาไว้ใช้ประโยชน์อย่างอื่นล่ะกัน
            DS("AddDate") = "1/1/2550 23:59:59"
            DS("EditDate") = "1/1/2550 23:59:59"
            DS.Update
            ' เพิ่มค่า Primary Key ขึ้นอีก 1
            iRec = iRec + 1
            ' เลื่อนตำแหน่งของ Record
            RS.MoveNext:        DS.MoveNext
        Loop
        ' ผมฝากโจทย์ทิ้งไว้ให้
        ' 1. กรณีจำนวนข้อมูลที่มีมากๆ ควรเปลี่ยนไปใช้คำสั่ง SQL โดยตรงนั่นคือใช้การ INSERT จะเร็วกว่า
        ' ที่ผมเขียนโค้ดเอาไว้ด้านบนก็เพื่อ ให้มือใหม่ทั้งหลายได้มองเห็นภาพชัดๆของการคัดลอกข้อมูลระหว่าง 2 ตาราง
        ' 2. ควรทำ Progress Bar หรือ การแสดงสถานะอื่นๆให้ผู้ใช้งานรับรู้ด้วยว่าขณะนี้โปรแกรมกำลังทำงานอะไรอยู่
        ' หากท่านทำเพื่อใช้งานเอง ก็คงไม่ต้องเสียเวลาโค้ดเพิ่มเติมก็ได้
        
        ' ปิดตารางคืนค่ากลับให้หน่วยความจำ
        RS.Close:    Set RS = Nothing
        DS.Close:    Set DS = Nothing
        
        ' เมื่อคัดลอกเรียบร้อยแล้วก็ตัดการเชื่อมต่อ และจบโปรแกรมไปเลย (ก็ได้)
        Conn.Close: Set Conn = Nothing
        Call cmdExit_Click
        ' หรือ
        'Call CloseDataBase
        'End
    End Sub
    
    ตัวอย่างงานจริง
    ตัวอย่างงานจริง

    จากภาพด้านบน พี่น้องคงจะเห็นแล้วว่ามันไม่สามารถทำการ Copy เพื่อมาทำการตัดแปะๆ เข้าสู่ตารางข้อมูลได้เลย (ใครจะตัดแปะ ก็อย่ามาเขียนโปรแกรมเลย) ดังนั้นจึงจำเป็นต้องโค้ดโปรแกรมเข้าไปช่วย เพื่อที่จะสามารถวนลูปในการอ่าน Sheet ได้ครบ (ผมคงไม่บ้ามานั่งเลือกทีละ Sheet หรอกครับ ... พี่น้อง) และในแต่ละ Sheet (หรือตาราง) เราต้องทำการจับแยกข้อมูลที่อยู่ ให้แยกเป็นส่วนๆ คือ

  • ที่อยู่ (Address)
  • อำเภอ (Amphur) - ไม่เอา อ.
  • จังหวัด (Province) - ต้องดึงออกไปอยู่อีกตารางหนึ่งด้วย
  • รหัสไปรษณีย์ (PostCode)
  • โทรศัพท์ (Telephone) - ต้องใส่รหัสทางไกลนำหน้าให้อีก
  • อื่นๆ ... เช่น เพิ่มฟิลด์ที่จำเป็นเพิ่มเติม - ชื่อผู้ติดต่อ, eMail, Web Site ...
    งานแบบนี้แหละครับที่หากินกันง่ายๆมานักต่อนักแล้ว ... จากนั้นก็ทำเป็นโปรแกรมเล็กๆ ขยายต่อยอดให้เจ้าของงานไป ไม่ได้มีความยุ่งยากสลับซับซ้อน ซ่อนเงื่อนอะไรเลย

     


  • จี ทู จี เน็ต ดอต คอม - g2gNet Dot Com
    เลขทะเบียนพาณิชย์อิเล็กทรอนิกส์ 0407314800231
    CopyLeft © 2004 - 2099 g2gNet.Com All rights reserved.
    Email: [email protected] หรือ โทร. 08-6862-6560