|
再给你来一个更简洁的
- Sub 监测表()
- Application.ScreenUpdating = False
- Dim s As Date
- s = "2019-07-31"
- With ThisWorkbook.Sheets("监测表")
- .Range("t7") = DBv(s - 30, s)
- .Range("u7") = DBv(s - 90, s - 31)
- .Range("v7") = DBv(s - 180, s - 91)
- .Range("w7") = DBv(s - 360, s - 181)
- .Range("x7") = DBv(s - 100000, s - 361)
- End With
- Application.ScreenUpdating = True
- End Sub
- Function DBv(sday As Date, eday As Date)
- Dim cnn As Object, rst As Object
- Set cnn = CreateObject("ADODB.Connection")
- Set rst = CreateObject("ADODB.Recordset")
- cnn.Open "provider=Microsoft.ACE.OLEDB.12.0;Extended Properties=Excel 8.0;Data Source=" & ThisWorkbook.Path & "\输出\清单.xlsx"
- Set rst = cnn.Execute("select round(sum(余额/10000),0) from [清单$A:B] where 日期>=#" & sday & "# and 日期<=#" & eday & "#")
- DBv = rst(0)
- Set rst = Nothing
- cnn.Close
- Set cnn = Nothing
- End Function
复制代码 |
|