Mã:
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 F2 LIKE " & txt1 & " AND F6 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
'On Error Resume Next
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()
'On Error Resume Next
Dim Res()
Res = SearchDataFromClosedWorkbook("DS.xlsx", CStr(UserForm6.TextBox1), CStr(UserForm6.TextBox2))
UserForm6.ListBox1.Clear
If UBound(Res) Then
UserForm6.ListBox1.ColumnCount = UBound(Res, 2)
UserForm6.ListBox1.List = Res
Else
UserForm6.ListBox1.ColumnCount = UBound(Res) + 1
UserForm6.ListBox1.List = Res
End If
End SubLiên quan tới kết nối dữ liệu,em có đọc 1 bài trên diễn đàn.Nhưng điều kiên là hai file phải nằm trong 1 folder.
Bây giờ em muốn, hai file nằm ở hai nơi khác nhau, thì biến đổi thế nào ah?
