excel的vba中使用sql的update语句更新来源于另一sheet的求和数据

icedragonking

需要实现的功能很简单，就是从[同发物料]sheet中根据MaterialNumber字段汇总求和同发skus字段，然后再把汇总求和得到的数据去匹配[物料频次]sheet中的Material Number字段，对应的更新到同发skus字段。运行中途报错如下，跪求大神帮忙解决！
以下是同发物料sheet

同发物料

以下是物料频次sheet

物料频次

具体代码如下：
Sub 汇总()

Dim adoconn As New ADODB.Connection
Dim strconn As String
Dim strsql As String

    ThisWorkbook.Application.ScreenUpdating = False
    Application.DisplayAlerts = False

adoconn.Mode = adModeReadWrite
strconn = "Provider=microsoft.ace.oledb.12.0;data source=" & ThisWorkbook.FullName & ";extended properties=""excel 12.0;hdr=yes;imex=0"";"
adoconn.Open strconn
strsql = "UPDATE [物料频次$] as a SET a.同发skus = DSum([同发skus],'[同发物料$]','MaterialNumber=' & [a].[Material Number]) WHERE a.[Material Number] Is Not Null"
adoconn.Execute (strsql)
adoconn.Close
Set adoconn = Nothing

    ThisWorkbook.Application.ScreenUpdating = Ture
    Application.DisplayAlerts = Ture

End Sub
运行中途报错如下：

报错

jiangxiaoyun

select a.[Material Number],a.频次,b.[同发skus] from [物料频次$a1:c] a left join (select [MaterialNumber],sum([同发skus]) as 同发skus from [同发物料$a1:c] group by  [MaterialNumber]) b on a.[Material Number]=b.[MaterialNumber]

jiangxiaoyun

icedragonking

jiangxiaoyun 发表于 2020-7-9 11:22

Hi,我是想通过vba来实现哦，并且必须要把汇总求和结果放到物料频次的C列

zpy2

DSum

是access 函数，需要在access vba 环境执行，excel不行的。

jiangxiaoyun

icedragonking 发表于 2020-7-9 13:57
Hi,我是想通过vba来实现哦，并且必须要把汇总求和结果放到物料频次的C列

excel的sql中update应该是不能用子查询的。
1、用链接来代替。
2、将子查询（同发物料求和放到工作表），然后用update，完了再删除子查询数据。

icedragonking

zpy2 发表于 2020-7-9 15:28
DSum

是access 函数，需要在access vba 环境执行，excel不行的。

感谢回答，那请问DCount为啥可以在excel vba里执行。
http://club.excelhome.net/forum. ... ;page=1#pid10349654

icedragonking

jiangxiaoyun 发表于 2020-7-9 19:45
excel的sql中update应该是不能用子查询的。
1、用链接来代替。
2、将子查询（同发物料求和放到工作表） ...

感谢回答，应该有一步实现的方法把，之前DCount就做到了
http://club.excelhome.net/forum. ... ;page=1#pid10349654

ly

1. strsql = "UPDATE [物料频次$] as a SET a.同发skus = DSum('[同发skus]','[同发物料$]','[MaterialNumber]="" & [a].[Material Number] & ""') WHERE a.[Material Number] Is Not Null"
   

复制代码

如果上面方法不行，则可以：
1、新增一个临时表作汇总用，假设表名为“temp同发物料”，第一行为与汇总相关的标题；
2、每次先汇总，再更新到“物料频次”表

1. Sheets("temp同发物料").Rows("2:65350").Clear
   
2. 
   
3. strsql = "INSERT INTO [temp同发物料$] ( MaterialNumber, 同发skus ) SELECT a.MaterialNumber, Sum(a.同发skus) AS 同发skus FROM [同发物料$] as a GROUP BY a.MaterialNumber"
   
4. adoconn.Execute (strsql)
   
5. 
   
6. strsql = "UPDATE [物料频次$] as a INNER JOIN [temp同发物料$] as b ON a.[Material Number] = b.MaterialNumber SET a.同发skus =b.[同发skus]"
   
7. adoconn.Execute (strsql)
   

复制代码

icedragonking

ly 发表于 2020-7-15 18:17
如果上面方法不行，则可以：
1、新增一个临时表作汇总用，假设表名为“temp同发物料”，第一行为与汇 ...

多谢大师指点，新增中间表的方法已测可行，非常感谢。
只是不懂之前的帖子中类似的汇总统计问题，您给我解答使用了DCount并且也是可行的，为啥DSum就存在问题了呢，也没有任何的提示报错信息。