excel 用宏连接DB生成数据

Sub Kpi_Tree()

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim SQL As String
Dim title As Range
Dim i%, j%, k%, m%, rg As Range


Set cn = New ADODB.Connection
cn.ConnectionString = "provider = SQLOLEDB ;  Data Source =" & Sheets("database").Cells(2, 2) & "; user id= " & Sheets("database").Cells(3, 2) & "; password = " & Sheets("database").Cells(4, 2) & ";Initial catalog = " & Sheets("database").Cells(5, 2)

cn.Open

''LY数据

 
  SQL = "select store,TTLRev , newcust_rev, existing_rev, newcust_firsttrans_rev, Newcust_Repeat_rev"
  SQL = SQL & " ,pure_rev ,convert_rev ,newcust_repurase_rate ,rentionrate ,pure_new_J_rev ,pure_new_W_rev ,pure_new_J_W_rev ,pure_new_J_cust ,pure_new_W_cust ,pure_new_J_W_cust"
  SQL = SQL & " from Base_QuartelyReport_KpiTree"
  SQL = SQL & " where yymm= '" & Sheets("Cover").Cells(2, 2) & "' and yr=year('" & Sheets("Cover").Cells(2, 2) & "')-1"
  SQL = SQL & "order by store_order"

 
  Sheet7.Range("B5:AV21").ClearContents
  Sheet7.Range("B5:AV21").CopyFromRecordset cn.Execute(SQL)
  Sheet7.Range("B5:AV21").Font.Size = 10
  Sheet7.Range("B5:AV21").Font.Name = "Calibri"

 

 

End Sub