Sub subGetTableValues()
Dim Conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim intColCounter As Integer
Dim sConnect As String
Dim strSqlInstance As String
Dim strSqlDB As String
Dim strSql As String Dim sStartDate As String, sEndDate As String
sStartDate = TextBox21
sEndDate = TextBox22
strSqlInstance = "10.10.0.10"
strSqlDB = "DB-A"
sConnect = "PROVIDER=SQLOLEDB;"
sConnect = sConnect & "DATA SOURCE=" & strSqlInstance & ";INITIAL CATALOG=" & strSqlDB & ";"
sConnect = sConnect & " User ID=USER" & strSqlUser & ";Password=123456789" & strSqlPWD & ";"
'Establish connection
Set Conn = New ADODB.Connection
strSql = "select A.MD001,C.MA003,A.MD002,B.ME002,A.MD004,A.MD005,A.MD006,(A.MD005-A.MD006) FROM ACTMD A INNER JOIN CMSME B ON A.MD002=B.ME001 INNER JOIN ACTMA C ON A.MD001=C.MA001 WHERE A.MD003='& Format(TextBox21.Text) &' AND A.MD004='&Format(TextBox22.Text) &' "
With Conn
.ConnectionString = sConnect
.CursorLocation = adUseClient
.Open
.CommandTimeout = 0
Set rs = .Execute(strSql)
End With
Worksheets("Data").Range("A8:L8").Clear
If rs.RecordCount > 0 Then
Worksheets("Data").Range("A8").CopyFromRecordset rs
Else
MsgBox ("找不到數據")
End If
'ActiveSheet.ScrollArea = ""
Columns("A:A").EntireColumn.AutoFit
[A7].CurrentRegion.Borders.Weight = 2
Columns("A:L").EntireColumn.AutoFit
Range("A8").Select
ActiveWindow.FreezePanes = True
'ActiveSheet.ScrollArea = "A:L"
rs.Close
Conn.Close
Set rs = Nothing
Set Conn = Nothing
End Sub
