VBA+ADO对某一含有字符多行和空值的字段求和(解决标准数据类型不匹配问题)

Sub 分厂家统计()


Set cnn = CreateObject("adodb.Connection")
Set rs = CreateObject("adodb.Recordset")

cnn.Open "Provider = Microsoft.ace.Oledb.12.0;Extended Properties =TEXT;Data Source =" & ThisWorkbook.Path & "\"

A = "(select  distinct 时间,厂家名称,CGI,VOLTE语音话务量 from [yy.csv] WHERE VOLTE语音话务量 IS NOT NULL  )"
Sql = "SELECT 时间,厂家名称,COUNT(CGI) AS 小区数,SUM(VAL(VOLTE语音话务量)) AS 话务量 FROM " & A & " GROUP BY 时间,厂家名称"

 Set rs = cnn.Execute(Sql) '

  Sheets("结果").Cells.ClearContents       '清理保存数据的区域
     For I = 0 To rs.Fields.Count - 1
       Worksheets("结果").Cells(1, I + 1) = rs.Fields(I).Name
     Next
Sheets("结果").Range("a2").CopyFromRecordset rs
rs.Close
cnn.Close
Set rs = Nothing
Set cnn = Nothing
Worksheets("结果").Activate
MsgBox ("OK")
End Sub