属性的使用无效

brothersonline

想在EXCELVBA 中运行SQL存储过程，将查询到的数据写入到表中。
请诸位大神赐教，为什么运行时会提示“属性的使用无效”，是我定义变量有错误吗？我应该如何修改代码？

1. Private Sub CommandButton1_Click()
   
2. 
   
3.     Dim startdata, enddata As Date
   
4.     startdata = TextBox1.Value
   
5.     enddata = TextBox2.Value
   
6.     Dim conn As ADODB.Connection
   
7.     Dim strSQL As ADODB.Recordset
   
8. '    Dim strSQL As String
   
9.     Set conn = New ADODB.Connection
   
10.     Set strSQL = New ADODB.Recordset
    
11.     '配置连接串
    
12.     conn.ConnectionString = "Provider=SQLOLEDB;Server=(local);Database=TnServer;Uid=sa;Pwd=123456"
    
13.     conn.Open
    
14. 
    
15.     MsgBox ("连接成功！" & vbCrLf & "数据库状态：" & conn.State & vbCrLf & "数据库版本：" & conn.Version)
    
16.    
    
17. <font color="#ff0000">    strSQL </font>= "if OBJECT_ID('tempdb..#EnquiryInfo') is not null drop table #EnquiryInfo" _
    
18.             & "select F.PATIENT_NAME as 姓名,F.PRESCRIBED_BY as 开方医生,F.IDENTITYs  as 身份类别,convert(char(16),A.CreateDate,120) as 就诊日期,D.MaterialName as 药品名称,D.Model as 规格,D.PackingNumber as 包装数量,E.Text as 包装单位,A.Num as 数量,A.Money as 金额 into #EnquiryInfo" _
    
19.             & "From mms_warehouseStockHistory A    Left join mms_warehouse B on B.WarehouseCode = A.OutWarehouseCode    Left join mms_warehouse C  on C.WarehouseCode = A.IntoWarehouseCode     Left join mms_material D  on D.MaterialCode = A.MaterialCode     left join sys_code E  on E.Value = D.PackingUnit_Code    left join GXS_DRUG_PRESC_MASTER F on F.PRESC_NO = A.SrcBillNo Where 1=1 And A.SrcBillType='drug' And datediff(day,startdata,convert(char(23), A.CreateDate, 21)) >=0 and datediff(day,enddata,convert(char(23), A.CreateDate, 21)) <=0 order by 就诊日期 asc insert into #EnquiryInfo" _
    
20.             & "select null as 姓名,null as 开方医生,身份类别,case when grouping(身份类别) = 0 then 身份类别 + '_小计' else '总计' end as 就诊日期,null as 药品名称,null as 规格,null as 包装数量,null as 包装单位,null as 数量,SUM(金额) as 金额 from #EnquiryInfo group by 身份类别 with rollup order by 金额" _
    
21.             & "select convert(char(10), 就诊日期, 20) as 就诊日期,药品名称,规格,包装数量,包装单位,数量,金额 from #EnquiryInfo"
    
22.     '设置表头
    
23. '    Range("A3:G3").Value = Array("就诊日期", "药品名称", "规格", "包装数量", "包装单位", "数量", "金额")
    
24.     '将数据输出到工作表
    
25.     Range("A4").CopyFromRecordset conn
    
26.     Unload Me
    
27.     '关闭连接
    
28. '    conn.Close: Set strSQL = Nothing
    
29. '    conn.Close: Set conn = Nothing
    
30. 
    
31. 
    
32. End Sub
    

复制代码

药材流水分类汇总.zip (67.75 KB, 下载次数: 1)

2023-8-30 11:46 上传

点击文件名下载附件