Sub order()
Dim strConn As String, strSQL As String
Dim conn As ADODB.Connection
Dim ds As ADODB.Recordset
Dim Str As String
Dim Str1 As String
Dim st As String
Dim i As Integer, j As Integer
Str = "'" & Worksheets("报表").Range("C1").Value & "'"
Str1 = "'" & Worksheets("报表").Range("E1").Value & "'"
'st = Worksheets("sheet1").Range("A2").Value
Worksheets("报表").Range("A3:F100000").Cells.Clear
Set conn = New ADODB.Connection
Set ds = New ADODB.Recordset
'连接数据库的字符串
strConn = "Provider=MSDASQL;Driver={MySQL ODBC 5.3 ANSI Driver};Server=aaaaa;Port=33146;Database=afs;uid=dep;Password=*****;Data Source Name=ods_qc;"
strSQL = "SELECT date(c.inputdate) as date,i.CUSTOMERNAME,i.mobile ,h.create_by,g.name,g.tl from afs.c002_business_contract c"
strSQL = strSQL & " LEFT JOIN afs.c002_customer_info i on c.CUSTOMERID=i.CUSTOMERID"
strSQL = strSQL & " LEFT JOIN (SELECT c.phone,t.create_at,t.create_by,t.channel from afs.e007_t_task_history t LEFT JOIN afs.e007_t_product_customer p on t.product_customer_id=p.id LEFT JOIN afs.e007_t_customer c on c.id_no=p.customer_id where p.product_id='CROSS_CAR_LOAN' GROUP BY c.phone) h on h.phone=i.mobile"
strSQL = strSQL & " LEFT JOIN oper.dx_agent g on h.create_by=g.agentno"
strSQL = strSQL & " where MARKETCHANNELFLAG='XSELL-CAR'and h.channel='outbound' and c.CONTRACTSTATUS<>3002 and DATE(c.inputdate) BETWEEN" & Str & "and" & Str1
'打开数据库连接
conn.Open strConn
With ds
'根据查询语句获得数据
.Open strSQL, conn
'自动控制加入所有列标题
'For col = 0 To ds.Fields.Count - 1
'请注意Offset(0, col)中的参数一定要正确噢
' Worksheets("CS-Outbound").Range("A1").Offset(0, col).Value = ds.Fields(col).Name
'Next
'加入所有行数据
Worksheets("报表").Range("A2").Offset(1, 0).CopyFromRecordset ds
End With
'以下是关闭数据库连接和清空资源
Set ds = Nothing
conn.Close
Set conn = Nothing
End Sub
VBA查询MySQL数据
最新推荐文章于 2022-09-08 16:13:29 发布