VBA源码(销售月度统计报表)

最新推荐文章于 2023-05-30 09:09:58 发布

原创

最新推荐文章于 2023-05-30 09:09:58 发布 · 2.7k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#报表 #vba #date #microsoft #sql server #integer

这篇博客展示了如何使用VBA进行SQL查询，动态填充组合框，并创建销售月度统计报表。通过建立ADODB连接，设置SQL查询语句，从数据库获取数据，并在用户窗体中进行数据展示和计算。

'引用Microsoft OLE DB Service Component 1.0 Type Libary
'引用Microsoft ActiveX Data Objects 2.x Library
'引用Microsoft ADO Data Control
Dim strSQL1 As String
Private Sub combobox5_Change()
Set cn = CreateObject("ADODB.Connection")
'Set cmd = CreateObject("ADODB.Command")
cn.ConnectionString = "Driver={SQL Server};server=127.0.0.1;uid=sa;pwd=;database=KIS_Sample"
cn.Open

Set rs = CreateObject("ADODB.recordset")

'strSQL1 = "select fnumber,fname from t_icitemcore where fitemid<=230 order by fnumber"
strSQL1 = "select top 200 fnumber from t_icitemcore where fnumber like '%" & ComboBox5.Value & "%'" & " order by fnumber"

'rs.Open strSQL1, cn, adOpenKeyset, adLockReadOnly
'MsgBox strSQL1
rs.Open strSQL1, cn, 1, 1

Dim i As Integer
If Not rs.EOF Then
     'ComboBox5.Clear
      For i = 0 To rs.RecordCount - 1
           ComboBox5.AddItem rs("fnumber")
           'ComboBox5.itemData(i) = rs("id").Value
           rs.MoveNext
        Next
End If

Set rs = Nothing

Set cn = Nothing

End Sub
Private Sub combobox6_Change()
Set cn = CreateObject("ADODB.Connection")
'Set cmd = CreateObject("ADODB.Command")
cn.ConnectionString = "Driver={SQL Server};server=127.0.0.1;uid=sa;pwd=;database=KIS_Sample"
cn.Open

Set rs = CreateObject("ADODB.recordset")

'strSQL1 = "select fnumber,fname from t_icitemcore where fitemid<=230 order by fnumber"
strSQL1 = "select top 200 fnumber from t_icitemcore where fnumber like '%" & ComboBox6.Value & "

最低0.47元/天解锁文章