我在笔记本上开这个的时候没问题,在公司电脑上开就出现标准表达方式中的数据类型不匹配。
Sub 取得客户()
Application.ScreenUpdating = False
Set conn = CreateObject("adodb.connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ActiveWorkbook.FullName 'hdr=no
Sheet4.Activate
x = [E65536].End(3).Row + 1
Range("E" & x & ":G5000").ClearContents
Sql = "select dxkh,'未知' from (select distinct 代销客户 as dxkh,1 as xh from [代销情况表$] where 代销客户 > '' union all " & _
"select 代销客户 as dxkh,2 as xh from [信息表$E1:E] where 代销客户 > '') group by dxkh having sum(xh)=1 "
Range("E" & x).CopyFromRecordset conn.Execute(Sql)
Sheet11.Activate
conn.Close
Set conn = Nothing
Application.ScreenUpdating = True
End Sub
Sub 代销筛选客户()
Application.ScreenUpdating = False
Set conn = CreateObject("adodb.connection")
conn.Open "provider=microsoft.jet.oledb.4.0;extended properties='excel 8.0;imex=1';data source=" & ActiveWorkbook.FullName 'hdr=no
Range("AA2:AC500").ClearContents
Sql = "select a.代销客户,b.代销地区 from [代销情况表$] a inner join [信息表$] b on a.代销客户=b.代销客户 where 销售日期 >= cdate('" & [I2] & "') and 销售日期 <= cdate('" & [K2] & "') group by a.代销客户,b.代销地区 "
[AA2].CopyFromRecordset conn.Execute(Sql)
conn.Close
Set conn = Nothing
Application.ScreenUpdating = True
End Sub