Blockdit Logo (Mobile)
safetybuddybb84
•
12 มี.ค. 2023 เวลา 01:59 • การศึกษา

การติดต่อกับฐานข้อมูลชนิดExcel

สำหรับเนื้อหาในตอนนี้จะเป็นการโค๊ดที่ใช้ติดต่อกับฐานข้อมูล โดยจะยังไม่เป็นการลงลึกในส่วนของการใช้งานSQL แต่จะเป็นเพียงพื้นฐานในการใช้งานเท่านั้น ขั้นตอนแลกเราต้องทำการดึง libraly ตัวหนึ่งเข้ามาก่อน นั้นคือ
Microsoft ActiveX Data Object 6.1 Libraly นั้นเอง
libraly ที่จำเป็น
โดยให้เรากด Alt +F11 จะเข้าที่หน้าDeveloper> visual basic> microsoft visual basic for application จะเป็นหน้าตาประมาณนี้ (ขึ้นอยู่กับว่าตอนเริ่มต้นเราset ค่าเอาไว้แบบไหน)
หน้าตาของVBA
หลังจากนี้เราจะเริ่มทำการเขียนโค๊ดเพื่อให้เราสามารถเข้าถึงข้อมูลของฐานข้อมูลที่เราได้ทำเป็นExcel เอาไว้ เป็นไฟล์ หรือ ขอให้ชื่อว่าไฟล์ Number1 ก็แล้วกันซึ่งในFile นี้จะมีข้อมูล แค่ column ID ,column Number ซึ่อข้อมูลทั้งหมดมีเพียงตัวเลขหนึ่ง ทั้งหมด โดยเราจะเปิดไฟล์ excel ชื่อว่า "sample.xlsx" เป็นไฟล์template สำหรับดึงข้อมูลมาจากไฟล์อื่นๆ ในโฟล์เดอร์
Sub getdataSQL()
 
Dim filePath As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws As Worksheet
Dim ColCount As Long
 
 
 
 
 
End Sub
1
คำสั่งด้านบนเป็นคำสั่งฟังก์ชั่น สำหรับติดต่อกับฐานข้อมูล โดยตั้งชื่อ ฟังก์ชั่นว่า getdataSQL หรือจะชื่ออื่นก็ได้ ไม่สำคัญ เพียงแต่ควรมีข้อมูลอย่างน้อยตามตัวอย่างเท่านั้นเอง ในตอนเริ่มต้นเราต้องกำหนดค่าต่างๆ ก่อน เพื่อเอามาใช้ในการเขียนโค๊ดสำหรับติดต่อฐานข้อมูลในภายหลัง
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & filePath & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
cnn.Open
ต่อมาให้set ค่าconnection โดยใช้ข้อความตามด้านบน โดยเป็นการต่อ connection แบบ ADODB (มีแบบอื่นแต่ตอนนี้ยังไม่ขอกล่าวถึง)
ต่อมาเราต้อง การสร้าง Record set ขึ้นมาเพื่อเก็บข้อมูลที่เราไปquery มา ดังนี้
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn
rs.CursorType = adOpenStatic
rs.Source = SQLQuery
rs.Open
ถ้า run program ตอนนี้จะเกิด Bug ขึ้นเนื่องจากเรายังไม่ได้กำหนด ว่า SQLQuery คืออะไร และไม่ได้กำหนด filePath ให้กลับไปแก้ไขโค๊ดดังนี้
Dim SQLQuery As String
 
SQLQuery = _
"SELECT *" & _
"FROM " & _
"[Tabelle1$] "
filePath = ThisWorkbook.Path & "\Number1.xlsx"
หลังจากนั้นให้set workbookและ add worksheet เพื่อให้เราสามารถโหลด recordset data ลงไปได้ หลังจากนั้นให้ทำการปิด connection และลบobject rs และcnn ออกเพื่อคืนทรัพยากรให้กับคอมพิวเตอร์ต่อไป
Set ws = ThisWorkbook.Worksheets.Add
ThisWorkbook.Activate
ws.Select
ws.Range("A1").CopyFromRecordset rs
 
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
โค๊ดโดยสรุปมีหน้าตาดังนี้
Sub getdataSQL()
 
Dim filePath As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim ws As Worksheet
Dim ColCount As Long
Dim SQLQuery As String
SQLQuery = _
"SELECT * " & _
"FROM " & _
"[Tabelle1$] "
 
filePath = ThisWorkbook.Path & "\Number1.xlsx"
 
 
Set cnn = New ADODB.Connection
cnn.ConnectionString = _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & filePath & ";" & _
"Extended Properties='Excel 12.0 Xml;HDR=YES';"
 
cnn.Open
Set rs = New ADODB.Recordset
rs.ActiveConnection = cnn
rs.CursorType = adOpenStatic
rs.Source = SQLQuery
rs.Open
 
Set ws = ThisWorkbook.Worksheets.Add
ThisWorkbook.Activate
ws.Select
ws.Range("A1").CopyFromRecordset rs
 
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
End Sub

ดูเพิ่มเติมในซีรีส์

โฆษณา