Option Explicit
Function Table_Query(strTableName As String, _
shDestinationSheet As Worksheet, _
Optional strDestinationRange As String = "A1", _
Optional strProvider As String = "ODBC", _
Optional strDataSource As String, _
Optional strSQL As String, _
Optional strUserID As String, _
Optional strPassword As String) As Object
' Cac tham so cho ham Table_Query:
'- strTableName As String: Chuoi bieu dien Name nguon Co so du lieu, chung ta nen dat mot Name trong
' Database de tien quan ly va truy xuat
' (Trong vi du cua anh, mo file Data.xls de xem Name nay)
'- shDestinationSheet As Worksheet: Bang tinh dich chi ra rang du lieu ket xuat se duoc dat vao do
'- strDestinationRange As Range: O bat dau de du lieu dua xuong trong shDestinationSheet
'- Optional strProvider As String = "ODBC": chi ra rang CSDL duoc cung cap boi "Nha Cung cap" nao, mac dinh la ODBC
'- Optional strDataSource As String: Cai ma MrOkeBiladen dang thac mac, de co duong dan thay doi theo thu muc cua File
' chua module co code nay
' su dung thuoc tinh Path cua ThisWorkbook (xem vi du ben duoi)
'- Optional strSQL As String: Chuoi SQL
'- Optional strUserID As String: (Optional)
' Optional strPassword As String: (Optional)
If strUserID = "" Then strUserID = "ADMIN"
If strDataSource = "" Then strDataSource = ThisWorkbook.Path & "\" & ThisWorkbook.NAME
Dim strConnection As String
'NÕu sö dông tr×nh kÕt nèi ODBC, Table_Queryle sÏ ®îc cung cÊp CommanText lµ mét SQL
If strProvider = "ODBC" Then
strConnection = "ODBC;DBQ=" & strDataSource
strConnection = strConnection & ";Driver={Microsoft Excel Driver (*.xls)}"
Else
strConnection = "OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;"
strConnection = strConnection & "Password=" & strPassword & ";"
strConnection = strConnection & "User ID=" & strUserID & ";"
strConnection = strConnection & "Data Source=" & strDataSource
Table_Query.CommandType = xlCmdTable & "$"
Table_Query.AdjustColumnWidth = False
End If
' Neu shDestinationSheet.QueryTables da ton tai thi xoa di:
If shDestinationSheet.QueryTables.Count > 0 Then
TEMP.QueryTables(1).Delete
End If
On Error GoTo TRY ' Co gang thu lai, co the shDestinationSheet chu duoc Activate (ma loi:2147024809)
REFRESH_NOW:
Set Table_Query = shDestinationSheet.QueryTables.Add(strConnection, shDestinationSheet.Range(strDestinationRange))
Table_Query.NAME = strTableName
Application.DisplayAlerts = False
Table_Query.RefreshStyle = xlOverwriteCells
Table_Query.SourceDataFile = strDataSource
Table_Query.CommandText = IIf(strProvider = "ODBC", strSQL, strTableName & "$")
Table_Query.REFRESH
Application.DisplayAlerts = True
Exit Function
TRY:
Select Case ERR.Number
Case -2147024809
shDestinationSheet.Activate
GoTo REFRESH_NOW
Case Else ' Khong duoc roi, xem lai chuoi ket noi da chuan chua:
MsgBox ERR.Number & "-" & ERR.Description & ":" & vbNewLine & _
"Connect string:" & vbNewLine & strConnection & vbNewLine & _
"SQL string:" & vbNewLine & strSQL
End Select
End Function
