EXCEL TOOLS
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
snasui.com ยินดีต้อนรับ 
สมาชิกท่านใดที่ยังไม่ได้ระบุ Version ของ Excel ที่ใช้งานจริง สามารถทำตาม Link นี้เพื่อจะได้รับคำตอบที่ตรงกับ Version ของท่านครับ 
โปรดทราบ 
Code: Select all
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
