Import ข้อมูลจาก Excel มา Access

Xcelvba · #1

Form ชื่อ F-Q-EMP ครับ ปุ่ม Command 14 ขึ้น Error ครับ

Private Sub Command14_Click()
 
    Dim fd As Object ' Late-bound FileDialog object
    Dim strFilePath As String
    Dim cnn As Object ' Late-bound ADODB connection object
    Dim rs As Object ' Late-bound ADODB recordset object
    Dim strSql As String
    
    ' Create a FileDialog object
    Set fd = Application.FileDialog(3) ' msoFileDialogFilePicker
    
    ' Set the title and filters for the dialog box
    With fd
        .Title = "Select Excel File"
        .Filters.Clear
        .Filters.Add "Excel Files", "*.xlsx;*.xls"
    End With
    
    ' Show the dialog box and get the file path
    If fd.Show = -1 Then
        strFilePath = fd.SelectedItems(1)
        ' Open the Excel file using an ADODB connection
        Set cnn = CreateObject("ADODB.Connection")
        cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFilePath & ";Extended Properties=""Excel 12.0 Xml;HDR=YES;"""
        
        ' Read the data from the Excel file into a recordset object
        Set rs = CreateObject("ADODB.Recordset")
        rs.Open "SELECT * FROM [Sheet1$]", cnn, adOpenStatic, adLockOptimistic
        
        ' Iterate through the recordset object and insert or update the data in the Access table as necessary.
        Do While Not rs.EOF
            strSql = "SELECT * FROM Tb_Employee WHERE [Employee ID]='" & rs("Employee ID").Value & "'"
            If DCount("*", "Tb_Employee", strSql) > 0 Then
                ' Record already exists in Access, update the record
                strSql = "UPDATE Tb_Employee SET [Pre_Name_TH]='" & rs("Pre_Name_TH").Value & "', [Firstname_TH]='" & rs("Firstname_TH").Value & "', [Lastname_TH]='" & rs("Lastname_TH").Value & "', [Pre_Name_EN]='" & rs("Pre_Name_EN").Value & "', [Firstname_EN]='" & rs("Firstname_EN").Value & "', [Lastname_EN]='" & rs("Lastname_EN").Value & "', [Date of Birth]=" & rs("Date of Birth").Value & ", [Gender]='" & rs("Gender").Value & "', [Nationality]='" & rs("Nationality").Value & "', [Contact Number]='" & rs("Contact Number").Value & "', [Department]='" & rs("Department").Value & "', [Position]='" & rs("Position").Value & "', [Address]='" & rs("Address").Value & "', [ZIP Code]='" & rs("ZIP Code").Value & "', [ID_card]='" & rs("ID_card").Value & "', [Image]=rs('Image').Value WHERE [Employee ID]='" & rs("Employee ID").Value & "'"
                CurrentDb.Execute strSql
            
            
            Else
                ' Record does not exist in Access, insert a new record
                   strSql = "INSERT INTO Tb_Employee ([Employee ID], [Pre_Name_TH], [Firstname_TH], [Lastname_TH], [Pre_Name_EN], [Firstname_EN], [Lastname_EN], [Date of Birth], [Gender], [Nationality], [Contact Number], [Department], [Position], [Address], [ZIP Code], [ID_card], [Image]) VALUES ('" & rs("Employee ID").Value & "', '" & rs("Pre_Name_TH").Value & "', '" & rs("Firstname_TH").Value & "', '" & rs("Lastname_TH").Value & "', '" & rs("Pre_Name_EN").Value & "', '" & rs("Firstname_EN").Value & "', '" & rs("Lastname_EN").Value & "', #" & Format(rs("Date of Birth").Value, "yyyy\/mm\/dd") & "#, '" & rs("Gender").Value & "', '" & rs("Nationality").Value & "', '" & rs("Contact Number").Value & "', '" & rs("Department").Value & "', '" & rs("Position").Value & "', '" & rs("Address").Value & "', '" & rs("ZIP Code").Value & "', '" & rs("ID_card").Value & "', rs('Image').Value)"
        CurrentDb.Execute strSql
    End If
End Sub

snasui · #2

จาก Error กรุณาตรวจสอบว่าหลัง Do ใส่ Loop ไว้ครบถ้วนแล้วหรือไม่ครับ

Xcelvba · #3

เจอแล้วครับพอ กด open file จาก browse แล้ว error บรรทัดนี้ครับ

Code: Select all

rs.Open "SELECT * FROM [Sheet1$]", cnn, adOpenStatic, adLockOptimistic

snasui · #4

จับภาพ Popup ที่แสดง Error มาด้วยครับ

Xcelvba · #5

snasui wrote: Mon Apr 03, 2023 4:56 pm จับภาพ Popup ที่แสดง Error มาด้วยครับ

ขอเพิ่มเติมรูปภาพครับ +ไฟล์ที่ import ครับ

snasui · #6

แนบไฟล์ Access ล่าสุดที่ได้ปรับ Code มาแล้วด้วยจะได้ตอบต่อไปจากนั้นครับ

Xcelvba · #7

snasui wrote: Mon Apr 03, 2023 5:09 pm แนบไฟล์ Access ล่าสุดที่ได้ปรับ Code มาแล้วด้วยจะได้ตอบต่อไปจากนั้นครับ

ไฟล์ที่ปรับปรุงแล้วครับ

snasui · #8

ลองปรับ recordset โดยไม่ใช้ Parameter 2 ตัวท้ายครับ

จาก rs.Open "SELECT * FROM [Sheet1$]", cnn , adOpenStatic, adLockOptimistic ปรับเปํน rs.Open "SELECT * FROM [Sheet1$]", cnn

ส่วนของ Loop ใน Code ใส่ไว้ผิดตำแหน่ง กรุณาตรวจสอบและวางไว้ยังตำแหน่งที่ถูกต้องด้วยครับ

Xcelvba · #9

เรียนอาจารย์ยัง error อยู่ครับ

Code: Select all

Do While Not rs.EOF
            strSql = "SELECT * FROM Tb_Employee WHERE [Employee ID]='" & rs("Employee ID").Value & "'"
            
            If DCount("*", "Tb_Employee", strSql) > 0 Then
                ' Record already exists in Access, update the record
                strSql = "UPDATE Tb_Employee SET [Pre_Name_TH]='" & rs("Pre_Name_TH").Value & "', [Firstname_TH]='" & rs("Firstname_TH").Value & "', [Lastname_TH]='" & rs("Lastname_TH").Value & "', [Pre_Name_EN]='" & rs("Pre_Name_EN").Value & "', [Firstname_EN]='" & rs("Firstname_EN").Value & "', [Lastname_EN]='" & rs("Lastname_EN").Value & "', [Date of Birth]=" & rs("Date of Birth").Value & ", [Gender]='" & rs("Gender").Value & "', [Nationality]='" & rs("Nationality").Value & "', [Contact Number]='" & rs("Contact Number").Value & "', [Department]='" & rs("Department").Value & "', [Position]='" & rs("Position").Value & "', [Address]='" & rs("Address").Value & "', [ZIP Code]='" & rs("ZIP Code").Value & "', [ID_card]='" & rs("ID_card").Value & "', [Image]=rs('Image').Value WHERE [Employee ID]='" & rs("Employee ID").Value & "'"
                CurrentDb.Execute strSql
            
            
            Else
                ' Record does not exist in Access, insert a new record
                   strSql = "INSERT INTO Tb_Employee ([Employee ID], [Pre_Name_TH], [Firstname_TH], [Lastname_TH], [Pre_Name_EN], [Firstname_EN], [Lastname_EN], [Date of Birth], [Gender], [Nationality], [Contact Number], [Department], [Position], [Address], [ZIP Code], [ID_card], [Image]) VALUES ('" & rs("Employee ID").Value & "', '" & rs("Pre_Name_TH").Value & "', '" & rs("Firstname_TH").Value & "', '" & rs("Lastname_TH").Value & "', '" & rs("Pre_Name_EN").Value & "', '" & rs("Firstname_EN").Value & "', '" & rs("Lastname_EN").Value & "', #" & Format(rs("Date of Birth").Value, "yyyy\/mm\/dd") & "#, '" & rs("Gender").Value & "', '" & rs("Nationality").Value & "', '" & rs("Contact Number").Value & "', '" & rs("Department").Value & "', '" & rs("Position").Value & "', '" & rs("Address").Value & "', '" & rs("ZIP Code").Value & "', '" & rs("ID_card").Value & "', rs('Image').Value)"
        CurrentDb.Execute strSql
        End If
        Loop

snasui · #10

บรรทัดที่ Highlight กับ Popup Error ไม่สอดคล้องกัน กรุณาตรวจสอบใหม่ว่า Error ที่บรรทัดไหนครับ

Xcelvba · #11

snasui wrote: Mon Apr 03, 2023 4:56 pm บรรทัดที่ Highlight กับ Popup Error ไม่สอดคล้องกัน กรุณาตรวจสอบใหม่ว่า Error ที่บรรทัดไหนครับ

ผมลอง Import ไฟล์ Excel 123 เข้าไปใหม่อีกรอบนึง pop up เด้งขึ้นมาตามรูปภาพเลยครับ พอกด debug เด้งไปที่ตรงเส้นสีเหลืองครับ

snasui · #12

หากเป็นเช่นนั้นให้ตรวจสอบ 2 อย่างครับ

Statement ที่เกิดใน Popup ได้ผลลัพธ์เป็นอะไร ซึ่งจะต้องเข้าใจ Statement ของ SQL และสามารถนำไปทดสอบเองได้ใน Access หากย้งไม่ทราบวิธีการ ควรศึกษามาก่อน
Function Dcount มีส่วนประกอบอะไรบ้าง แต่ละส่วนประกอบมีความหมายว่าอย่างไร ข้อนี้สัมพันธ์กับข้อ 1 เพราะเป็นการนำข้อ 1 มาใช้เป็นส่วนประกอบของ Dcount และทำให้เกิด Popup เช่นนั้น

Xcelvba · #13

Error เปลี่ยนแล้วครับ

Code: Select all

 Do While Not rs.EOF
            strSql = "SELECT * FROM Tb_Employee WHERE [Employee ID]='" & rs("Employee ID").Value & "'"
            
            If DCount("*", "Tb_Employee", strSq) > 0 Then
                ' Record already exists in Access, update the record
                strSql = "UPDATE Tb_Employee SET [Pre_Name_TH]='" & rs("Pre_Name_TH").Value & "', [Firstname_TH]='" & rs("Firstname_TH").Value & "', [Lastname_TH]='" & rs("Lastname_TH").Value & "', [Pre_Name_EN]='" & rs("Pre_Name_EN").Value & "', [Firstname_EN]='" & rs("Firstname_EN").Value & "', [Lastname_EN]='" & rs("Lastname_EN").Value & "', [Date of Birth]=" & rs("Date of Birth").Value & ", [Gender]='" & rs("Gender").Value & "', [Nationality]='" & rs("Nationality").Value & "', [Contact Number]='" & rs("Contact Number").Value & "', [Department]='" & rs("Department").Value & "', [Position]='" & rs("Position").Value & "', [Address]='" & rs("Address").Value & "', [ZIP Code]='" & rs("ZIP Code").Value & "', [ID_card]='" & rs("ID_card").Value & "', [Image]=rs('Image').Value WHERE [Employee ID]='" & rs("Employee ID").Value & "'"
                CurrentDb.Execute strSql
            
           
            Else
                ' Record does not exist in Access, insert a new record
                   strSql = "INSERT INTO Tb_Employee ([Employee ID], [Pre_Name_TH], [Firstname_TH], [Lastname_TH], [Pre_Name_EN], [Firstname_EN], [Lastname_EN], [Date of Birth], [Gender], [Nationality], [Contact Number], [Department], [Position], [Address], [ZIP Code], [ID_card], [Image]) VALUES ('" & rs("Employee ID").Value & "', '" & rs("Pre_Name_TH").Value & "', '" & rs("Firstname_TH").Value & "', '" & rs("Lastname_TH").Value & "', '" & rs("Pre_Name_EN").Value & "', '" & rs("Firstname_EN").Value & "', '" & rs("Lastname_EN").Value & "', #" & Format(rs("Date of Birth").Value, "yyyy\/mm\/dd") & "#, '" & rs("Gender").Value & "', '" & rs("Nationality").Value & "', '" & rs("Contact Number").Value & "', '" & rs("Department").Value & "', '" & rs("Position").Value & "', '" & rs("Address").Value & "', '" & rs("ZIP Code").Value & "', '" & rs("ID_card").Value & "', rs('Image').Value)"
        CurrentDb.Execute strSql
        End If
        Loop

snasui · #14

ทำการ Debug Statement นี้ครับ

Code: Select all

strSql = "UPDATE Tb_Employee SET [Pre_Name_TH]='" & rs("Pre_Name_TH").Value & "', [Firstname_TH]='" & rs("Firstname_TH").Value & "', [Lastname_TH]='" & rs("Lastname_TH").Value & "', [Pre_Name_EN]='" & rs("Pre_Name_EN").Value & "', [Firstname_EN]='" & rs("Firstname_EN").Value & "', [Lastname_EN]='" & rs("Lastname_EN").Value & "', [Date of Birth]=" & rs("Date of Birth").Value & ", [Gender]='" & rs("Gender").Value & "', [Nationality]='" & rs("Nationality").Value & "', [Contact Number]='" & rs("Contact Number").Value & "', [Department]='" & rs("Department").Value & "', [Position]='" & rs("Position").Value & "', [Address]='" & rs("Address").Value & "', [ZIP Code]='" & rs("ZIP Code").Value & "', [ID_card]='" & rs("ID_card").Value & "', [Image]=rs('Image').Value WHERE [Employee ID]='" & rs("Employee ID").Value & "'"

โดย Debug ไปทีละค่าว่าค่าที่นำมาเชื่อมกันนั้นให้ผลลัพธ์เป็นอะไรบ้าง ยกตัวอย่างเช่น
rs("Pre_Name_TH").Value ให้ค่าเป็นอะไร
rs("Firstname_TH").Value ให้ค่าเป็นอะไร
ฯลฯ

การใช้ VBA ต้อง Debug โดยวิธีการติดตามตัวแปรเองให้เป็นหรืออย่างน้อยสามารถคีย์สอบถามใน Immediate Windows ได้ครับ

วิธีการคร่าว ๆ

เปิดหน้าต่าง Immediate Windows จากเมนู View
คีย์เพื่อตรวจสอบผลลัพธ์ของสิ่งที่สนใจเช่น
? strSql
Enter
สังเกตผลลัพธ์ที่ได้

Xcelvba · #15

ผมลอง Debug แล้วครับ ได้ค่าดังนี้
[Pre_Name_TH]=Null
[Firstname_TH]='·´ÒÍººº'
[Lastname_TH]='ÅÍ§'
[Pre_Name_EN]=Null
[Firstname_EN]=Null
[Lastname_EN]=Null
[Date of Birth]=13/4/2566
[Gender]='ªÒÂ'
[Nationality]='ä·Â'
[Contact Number]='081-1111111'
[Department]='·´ÊÍº¢éÍÁÙÅ'
[Position]=Null
[Address]=Null
[ZIP Code]='50210'
[ID_card]=''
[Image]=rs('Image').Value WHERE [Employee ID]='10000'

ผมเจอบรรทัดนี้ แปลก ผมเลยปรับโค้ด เป็น

Code: Select all

strSql = "UPDATE Tb_Employee SET [Pre_Name_TH]='" & rs("Pre_Name_TH").Value & "', [Firstname_TH]='" & rs("Firstname_TH").Value & "', [Lastname_TH]='" & rs("Lastname_TH").Value & "', [Pre_Name_EN]='" & rs("Pre_Name_EN").Value & "', [Firstname_EN]='" & rs("Firstname_EN").Value & "', [Lastname_EN]='" & rs("Lastname_EN").Value & "', [Date of Birth]=" & rs("Date of Birth").Value & ", [Gender]='" & rs("Gender").Value & "', [Nationality]='" & rs("Nationality").Value & "', [Contact Number]='" & rs("Contact Number").Value & "', [Department]='" & rs("Department").Value & "', [Position]='" & rs("Position").Value & "', [Address]='" & rs("Address").Value & "', [ZIP Code]='" & rs("ZIP Code").Value & "', [ID_card]='" & rs("ID_card").Value & "', [Image]= '" & rs("Image").Value & "' WHERE [Employee ID]='" & rs("Employee ID").Value & "'"
                CurrentDb.Execute strSql

แต่ก็ยัง ERROR และ pop up เช่นเดิมครับอาจารย์

snasui · #16

หลังจากปรับ Code แล้ว ตัวแปร strSql ได้เป็นค่าใดครับ

Xcelvba · #17

UPDATE Tb_Employee SET
[Pre_Name_TH]='',
[Firstname_TH]='·´ÒÍººº',
[Lastname_TH]='ÅÍ§',
[Pre_Name_EN]='',
[Firstname_EN]='',
[Lastname_EN]='',
[Date of Birth]=13/4/2566,
[Gender]='ªÒÂ',
[Nationality]='ä·Â',
[Contact Number]='081-1111111',
[Department]='·´ÊÍº¢éÍÁÙÅ',
[Position]='',
[Address]='',
[ZIP Code]='50210',
[ID_card]='',
[Image]= ''
WHERE [Employee ID]='10000'

ได้เป็นค่านี้ครับ

snasui · #18

ดูแล้วสามารถรันได้ ลองทดสอบเอา Statement นี้ไปรันใน Query ของ Access (ซึ่งจะต้องเข้ามุมมอง SQL) เพื่อดูว่าได้ผลเป็นเช่นไรครับ

หากรันไม่ผ่านแสดงว่า Statement นี้มีปัญหา ให้แจ้งปัญหานั้นมาอีกรอบครับ

้สังเกตว่าวันที่ไม่ได้ครอบด้วย ' ในการรัน Query หากไม่ผ่านอาจทดสอบด้วยการครอบด้วย ' ดูอีกรอบว่าผ่านหรือไม่

Xcelvba · #19

snasui wrote: Tue Apr 04, 2023 4:41 pm ดูแล้วสามารถรันได้ ลองทดสอบเอา Statement นี้ไปรันใน Query ของ Access (ซึ่งจะต้องเข้ามุมมอง SQL) เพื่อดูว่าได้ผลเป็นเช่นไรครับ

หากรันไม่ผ่านแสดงว่า Statement นี้มีปัญหา ให้แจ้งปัญหานั้นมาอีกรอบครับ

้สังเกตว่าวันที่ไม่ได้ครอบด้วย ' ในการรัน Query หากไม่ผ่านอาจทดสอบด้วยการครอบด้วย ' ดูอีกรอบว่าผ่านหรือไม่

ลองแก้วันที่แล้วครับ Error ตลอดครับ แต่ถ้านำ CurrentDb.Execute strSql ออกจะเด้ง pop-up ดังรูปครับ ส่วนโค็ดที่ทดสอบคือ

Code: Select all

 UPDATE Tb_Employee SET [Pre_Name_TH]='" & rs("Pre_Name_TH").Value & "', [Firstname_TH]='" & rs("Firstname_TH").Value & "', [Lastname_TH]='" & rs("Lastname_TH").Value & "', [Pre_Name_EN]='" & rs("Pre_Name_EN").Value & "', [Firstname_EN]='" & rs("Firstname_EN").Value & "', [Lastname_EN]='" & rs("Lastname_EN").Value & "', [Date of Birth]='" & rs("Date of Birth").Value & "', [Gender]='" & rs("Gender").Value & "', [Nationality]='" & rs("Nationality").Value & "', [Contact Number]='" & rs("Contact Number").Value & "', [Department]='" & rs("Department").Value & "', [Position]='" & rs("Position").Value & "', [Address]='" & rs("Address").Value & "', [ZIP Code]='" & rs("ZIP Code").Value & "', [ID_card]='" & rs("ID_card").Value & "', [Image]= '" & rs("Image").Value & "' WHERE [Employee ID]='" & rs("Employee ID").Value & "'

snasui · #20

ต้องเอา Statement ที่ได้จากตัวแปร strSql ไปทดสอบ ไม่ใช่นำ Statement ของ VBA ไปใช้โดยตรงครับ

Snasui Excel Forum

File Anatomy Add-in: ตรวจสอบโครงสร้างไฟล์ Excel อย่างมืออาชีพ

Import ข้อมูลจาก Excel มา Access

Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access

Re: Import ข้อมูลจาก Excel มา Access