vba 编写自动报表

来源：互联网发布：hse矩阵管理规定编辑：程序博客网时间：2024/04/28 16:31
 Sub qr_report_week_click()    Dim conn As ADODB.Conn    Dim rs As ADODB.Reco    Dim i As Integer    Dim sql As String, sdate As String, edate As String    Set conn = CreateObjct("ADDB.Contion")    conn.ConnecnString = "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=192.168.0.3;Database=test;Uid=test;Pwd=test"          conn.Open    Set rs = New ADODB.Recordset    sdate = InputBox("请输入起始日期，格式如 2016-02-01", "起始日期", Format(DateAdd("d", -7, Now), "yyyy-mm-dd"))    edate = InputBox("请输入终止日期，格式如 2016-02-07", , Format(Now, "yyyy-mm-dd"))    sql = "SELECT ....."    rs.Open sql, conn    rs.MoveFirst   Set sht = ThisWorkbook.Worksheets("周新增统计")   sht.Range("A2:E2").ClearContents    i = 2Do While Not rs.EOF    sht.Cells(i, 1) = rs("date")    sht.Cells(i, 2) = rs("register")    sht.Cells(i, 3) = rs("bind")    sht.Cells(i, 4) = rs("activat")    sht.Cells(i, 5) = rs("lv")        rs.MoveNext    i = i + 1Loop'累计统计Set rs = New ADODB.Recordset    sql = "SELECT ....."    rs.Open sql, conn    rs.MoveFirst       Set sht = ThisWorkbook.Worksheets("累计统计")   sht.Range("A2:E2").ClearContents    i = 2Do While Not rs.EOF        sht.Cells(i, 1) = rs("register")    sht.Cells(i, 2) = rs("activat")    sht.Cells(i, 3) = rs("lv")        rs.MoveNext    i = i + 1Loop'周（每日统计）Set rs = New ADODB.Recordset    sql = "SELECT DATE_....."    rs.Open sql, conn    rs.MoveFirst       Set sht = ThisWorkook.Worksheets("周（每日统计）")   sht.Rang("A2:E100").ClearContents    i = 2Do While Not rs.EOF        sht.Cells(i, 1) = rs("date")    sht.Cells(i, 2) = rs("register")    sht.Cells(i, 3) = rs("bind")    sht.Cells(i, 4) = rs("activat")    sht.Cells(i, 5) = rs("lv")        rs.MoveNext    i = i + 1Loop    MsgBox "报表生成完成！"    conn.Close    Set conn = NothingEnd Sub
得到的测试结果如下：
重新跑后结果
0 0