EXCEL TOOLS
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
Excel Add-ins ที่พัฒนาโดยคุณสันติพงศ์ ณสุย (MVP Excel 2010-2020) ด้วยภาษา C# เพื่อแก้ไขปัญหาไฟล์ใหญ่ คำนวณนาน ทำงานช้า จัดการข้อมูลต่าง ๆ ที่ทำงานประจำวันได้อย่างสะดวกรวดเร็ว สนใจคลิกไปดูได้ที่นี่ครับ => Excel Tools
[code]
และปิดด้วย [/code]
ตัวอย่างเช่น [code]dim r as range[/code]
เพื่อให้แตกต่างจากข้อความทั่วไป สะดวกในการอ่านและทดสอบ (คลิกเพื่อดูตัวอย่าง)Code: Select all
'Other code...
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
'Other code...
sql = "select * from " & shtName & "Where RefNo IN ('xxx7','xxx8','xxx9') Order By [RefNo] ASC"
เช่นนี้เป็นต้นsql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
ผมจะศึกษา SQL ให้มากกว่านี้ครับ ขอบคุณอาจารย์มากครับหากต้องการหา 3 รายการสุดท้ายสามารถปรับได้เป็น
sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
จะต้องศึกษาเกี่ยวกับ SQL Statement เพิ่มเติมในการทำงานลักษณะนี้ครับ
Code: Select all
Sub DataReview(ByVal GoToRow As Integer)
Dim sFile As String, sh As Worksheet
Dim sCnstr As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, shtName As String
Dim arr() As Variant, i As Integer, j As Integer, k As Integer
Dim strS As String
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
With Sheets("input")
.Range("InputRefNo") = rs.Fields(GoToRow, 0).Value
.Range("InputName") = rs.Fields(GoToRow, 5).Value
.Range("InputHN").Value = rs.Fields(GoToRow, 6).Value
.Range("C12").Value = rs.Fields(GoToRow, 7).Value
.Range("InputPayer").Value = rs.Fields(GoToRow, 8).Value
.Range("InputEstimateDateTime").Value = rs.Fields(GoToRow, 94).Value
End With
Set sCnstr = Nothing
End Sub
Sub PreviousData()
Dim GoToRow As Integer
Dim sFile As String, sh As Worksheet
Dim sCnstr As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, shtName As String
Dim arr() As Variant, i As Integer, j As Integer, k As Integer
Dim strS As String
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
If Worksheets("Input").Range("InputRow") = "" Then
GoToRow = rs.Fields
Else
GoToRow = rs.Fields.Range("InputRow").Value - 1
End If
Worksheets("Input").Range("InputRow").Value = GoToRow
DataReview (GoToRow)
End Sub
sql = sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
ให้ลบ sql =
ออกไป 1 ชุดครับCode: Select all
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
If Worksheets("Input").Range("InputRow") = "" Then
GoToRow = rs.Fields(0)
Else
GoToRow = rs.Fields.Range("InputRow").Value - 1
End If
Code: Select all
If Worksheets("Input").Range("InputRow") = "" Then
GoToRow = rs.Fields(0)
End If
Code: Select all
Dim GoToRow As String
Dim sFile As String, sh As Worksheet
Dim sCnstr As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, shtName As String
Dim arr() As Variant, i As Integer, j As Integer, k As Integer
Dim strS As String
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
If Worksheets("Input").Range("InputRow") = "" Then
GoToRow = rs.Fields(0)
End If
Worksheets("Input").Range("InputRow").Value = GoToRow
DataReview (GoToRow)
จาก Code ที่เขียนมา GoToRow จะเป็น Integer ไม่ได้ เพราะถูกกำหนดให้รับค่ามาจาก rs.Fields(0) ซึ่ง rs.Fields(0) คือค่าในคอลัมน์ A ซึ่งเป็น Text หากต้องการหาเลขลำดับต้องใช้ Statement อื่นไม่ใช่นำ rs.Fields(0) มาใช้ครับ
Code: Select all
Sub DataReview(ByVal GoToRow As Integer)
Dim sFile As String, sh As Worksheet
Dim sCnstr As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, shtName As String
Dim arr() As Variant, i As Integer, j As Integer, k As Integer
Dim strS As String
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
With Sheets("input")
.Range("InputRefNo") = rs.Fields(1)
.Range("InputName") = rs.Fields(6)
.Range("InputHN").Value = rs.Fields(7)
.Range("C12").Value = rs.Fields(8)
.Range("InputPayer").Value = rs.Fields(9)
.Range("InputEstimateDateTime").Value = rs.Fields(95)
End With
Set sCnstr = Nothing
End Sub
Sub PreviousData()
Dim GoToRow As Integer
Dim sFile As String, sh As Worksheet
Dim sCnstr As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, shtName As String
Dim arr() As Variant, i As Integer, j As Integer, k As Integer
Dim strS As String
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
If Worksheets("Input").Range("InputRow") = "" Then
GoToRow = rs.Fields(0)
End If
Worksheets("Input").Range("InputRow").Value = GoToRow
DataReview (GoToRow)
End Sub
Code: Select all
Sub DataReview(ByVal GoToRow As Integer)
Dim sFile As String, sh As Worksheet
Dim sCnstr As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, shtName As String
Dim arr() As Variant, i As Integer, j As Integer, k As Integer
Dim strS As String
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select * from " & shtName & " where [No] = " & GoToRow
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
With Sheets("input")
.Range("InputRow").Value = rs.Fields(0)
.Range("InputRefNo") = rs.Fields(1)
.Range("InputName") = rs.Fields(6)
.Range("InputHN").Value = rs.Fields(7)
.Range("C12").Value = rs.Fields(8)
.Range("InputPayer").Value = rs.Fields(9)
.Range("InputEstimateDateTime").Value = rs.Fields(95)
End With
Set sCnstr = Nothing
End Sub
Sub PvLine()
If Range("InputRow").Value = 1 Then Exit Sub
Call DataReview(Range("InputRow").Value - 1)
End Sub
Sub NxLine()
Call DataReview(Range("InputRow").Value + 1)
End Sub
Sub PreviousData()
Dim GoToRow As Integer
Dim sFile As String, sh As Worksheet
Dim sCnstr As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String, shtName As String
Dim arr() As Variant, i As Integer, j As Integer, k As Integer
Dim strS As String
sFile = "\\10.21.4.97\File Sharing2\DataPricing\All Data Estimated.xlsx"
shtName = "[Data$]"
sql = "select top 3 * from " & shtName & " Order By [RefNo] DESC"
sCnstr.CursorLocation = adUseClient
sCnstr.Open "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & sFile & ";" _
& "Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
rs.Open sql, sCnstr
Worksheets("Input").Range("InputRow") = rs.Fields(0)
End Sub
Assign Macro ปุ่ม Previous > PvLine() และปุ่ม ปุ่ม Next > NxLine() ใช่หรือไม่ครับอาจารย์อย่าลืม Assign Macro ให้กับปุ่ม Previous และ Next ก่อนการรัน