ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] EXCEL VBA SQL ÅܼƬd¸ß°ÝÃD

[µo°Ý] EXCEL VBA SQL ÅܼƬd¸ß°ÝÃD

·Q³z¹LEXCEL VBA ¿é¤J¬d¸ß¦~¤ë±ø¥ó ¬d¸ß³Ò¨úSQL DB
¦ý¥d¦í¤F¡A¬O§_½u¤W¤j¤jÀ°¦£¬Ý¬Ý
EXCEL ·s¼W¨â­Ó±±¨î¦V¤À§O¿é¤J ¦è¤¸¦~»P¤ë
³Ì³z °õ¦æVBA SQL¬d¸ß¡A¦ý§ä¤£¨ì¸ê®Æ À°À°¦£¬Ý¬Ý ¤U­±³o¼Ë¹ï¶Ü???

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

        ÀR«ä¦Û¦b : ½_ÁJµ²±o¶V¹¡º¡¡A¶V·|©¹¤U««¡A¤@­Ó¤H¶V¦³¦¨´N¡A´N­n¶V¦³Á¾¨Rªº¯ÝÃÌ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD