Public Function SearchDataFromClosedWorkbook(CloseWb As String, txt1 As String, txt2 As String)
'CloseWb: file chua data
'SQL: cu phap truy van du lieu
'txt1: gia tri Textbox MA SO
'txt2: gia tri Textbox Ten cong ty
Dim cnn As Object, Rst As Object, SQL As String, SearchRes(), WbPath As String
Set cnn = CreateObject("ADODB.Connection")
Set Rst = CreateObject("ADODB.Recordset")
'WbPath = ThisWorkbook.Path & "\" & CloseWb
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & ThisWorkbook.Path & "\" & CloseWb & _
";Extended Properties=""Excel 12.0;HDR=No;"""
txt1 = """%" & txt1 & "%"""
txt2 = """%" & txt2 & "%"""
SQL = "SELECT * FROM [Sheet1$] WHERE F1 LIKE " & txt1 & " AND F2 Like " & txt2
Set Rst = cnn.Execute(SQL)
If Not Rst.EOF And Not Rst.bof Then
SearchRes = Rst.getrows
SearchDataFromClosedWorkbook = f_transpose2DArray(SearchRes)
Else
SearchDataFromClosedWorkbook = Array("khong co du lieu")
End If
Rst.Close
cnn.Close
End Function
' ----------------------------------------------------------------
' Purpose: Transpose a 2D array
' ----------------------------------------------------------------
Public Function f_transpose2DArray(inputArray As Variant) As Variant
Dim x As Long, yUbound As Long
Dim y As Long, xUbound As Long
Dim tempArray As Variant
xUbound = UBound(inputArray, 2) + 1
yUbound = UBound(inputArray, 1) + 1
ReDim tempArray(1 To xUbound, 1 To yUbound)
For x = 1 To xUbound
For y = 1 To yUbound
tempArray(x, y) = inputArray(y - 1, x - 1)
Next y
Next x
f_transpose2DArray = tempArray
End Function
Public Sub FillListBox()
Dim Res()
Res = SearchDataFromClosedWorkbook("id_Name.xlsx", CStr(UserForm1.TextBox3), CStr(UserForm1.TextBox2))
UserForm1.ListBox1.Clear
If UBound(Res) Then
UserForm1.ListBox1.ColumnCount = UBound(Res, 2)
UserForm1.ListBox1.List = Res
Else
UserForm1.ListBox1.ColumnCount = UBound(Res) + 1
UserForm1.ListBox1.List = Res
End If
End Sub
