《Excel VBA+SQL数据管理与应用模板开发》_7.3.2 按合同进度制作统计报表

【摘要】 本书摘自《Excel VBA+SQL数据管理与应用模板开发》一书中第7章，第3节，韩小良著。

7.3.2 按合同进度制作统计报表

也许我们需要了解以下信息：哪些合同执行完毕了?哪些合同还未开发票?哪些合同还 有应付款? … … 对于这样的报表，也可以通过设计一个自动化程序来实现。

根据合同进度情况，分成以下7种情况。

● 合同执行完毕

● 未全额开票

● 未全额付款

● 全额开票、未全额付款

● 未全额开票、全额付款

● 未全额开票、未全额付款

● 未开票、未付款

因此，需设计一个可以选择不同进度类型报表的窗口，如图7-45所示。窗体的名称和 Caption 属性均为“供货商合同进度跟踪”。

“供货商合同进度跟踪”窗体的功能是：选择需要了解的进度类型，即可得到在该进度 的所有供货商合同汇总表，同时还可以了解某个供货商的开票和付款情况。

“供货商合同进度跟踪”窗口的各个控件说明如表7-13所示。

窗体的初始化程序如下：

Private Sub UserForm_Initialize)

Dim rs As New ADODB.Recordset

Dim SQL As String

'指定工作表对象

Set wsCon =ThisWorkbook.Worksheets("合同信息")

Set wslnv =ThisWorkbook.Worksheets("发票信息")

Set wsPay =ThisWorkbook.Worksheets("付款信息")

Set ws =ThisWorkbook.Worksheets("供货商合同跟踪")

'清除汇总表旧数据

ws.Select

ws.Range("A6:BA1000").Clear

'建立与工作簿的连接

With cnn

Provider ="microsoft.ace.oledb.12.0"

.ConnectionString ="Extended Properties=Excel 12.0;Data Source="_

&ThisWorkbook.FullName

.Open

End With

'清除供货商列表的旧数据

供货商名称列表.RowSource =""

End Sub

为“查询”按钮设置Click 事件，当单击此按钮时，按照选择的进度类型进行查询。程 序代码如下：

Private Sub 查询_Click0

Dim i As Integer,n As Integer,k As Integer

Dim ID As String

Dim 供货商 As String

Dim 合同额As Currency,发票总额 As Currency,付款总额 As Currency

Dim rs As ADODB.Recordset

Dim SQL As String

判断是否选择了进度类型选项按钮

If 合同执行完毕.Value=False And 未全额开票.Value =False_

And 未全额付款.Value =False_

And 全额开票未全额付款.Value=False And未全额开票全额付款.Value =False_ And 未全额开票未全额付款.Value=False And未开票未付款.Value =False Then

MsgBox"没有选择统计的类型!"vbCritical," 警告"

Exit Sub

End If

清除查询表的旧数据

ws.Range("A6:BA1000").Clear

ws.Range("K2")=""

供货商名称列表.RowSource =""

n=wsCon.Range("A10000").End(xlUp).Row

'开始查询满足条件的合同信息

k =6

Fori=2 To n

ID=wsCon.Range("A"&i)

合同额= wsCon.Range("F"&i)

发票总额=WorksheetFunction.Sumlf(wsInv.Range("A:A"),ID,_

wslnv.Range("H:H"))

付款总额=WorksheetFunction.Sumlf(wsPay.Range("A:A"),ID,_

wsPay.Range("F:F"))

If 合同执行完毕.Value =True Then

con= (合同额=发票总额 And 合同额=付款总额)

ws.Range("A4")= 合同执行完毕.Caption

Elself 未全额开票.Value =True Then

con= (合同额◇发票总额)

ws.Range("A4")= 未全额开票.Caption

Elself 未全额付款.Value =True Then

con =(合同额◇付款总额)

ws.Range("A4")= 未全额付款.Caption

Elself 全额开票未全额付款.Value =True Then

con= (合同额=发票总额And 合同额>付款总额)

ws.Range("A4")= 全额开票未全额付款.Caption

Elself 未全额开票全额付款.Value =True Then

con= (合同额>发票总额 And 合同额=付款总额)

ws.Range("A4")= 未全额开票全额付款.Caption

Elself 未全额开票未全额付款.Value =True Then

con =(合同额>发票总额 And 合同额<>付款总额)

ws.Range("A4")= 未全额开票未全额付款.Caption

Elself未开票未付款.Value =True Then

con =(发票总额=0 And 付款总额=0)

ws.Range("A4")= 未开票未付款.Caption End If

If con =True Then

With ws

wsCon.Range("C"&i&":D"&i).Copy wsCon.Range("A"&i&":B"&i).Copy wsCon.Range("E"&i&":H"&i).Copy Range("I"&k)= 发票总额

.Range("J"&k)= 付款总额

Destination:=.Range("A"&k)

Destination:=.Range("C"&k)

Destination:=.Range("E"&k)

.Range("K"&k)=.Range("F"&k)-.Range("I"&k)

.Range("L"&k)=.Range("F"&k)-.Range("J"&k)

k=k+1

End With

End If

Next i

'设置单元格格式，并把供货商名称填写到供货商列表

With ws

.Range("F:F,I:L").NumberFormat ="#,##0.00"

.Range("G:H").NumberFormat ="yyyy-m-d"

If.Range("D1000").End(xIUp).Row >5 Then

供货商名称列表.RowSource=" 供货商合同跟踪!D6:D"&.Range("D1000").End(xIUp).

Row

Else

供货商名称列表.RowSource =""

End If

.Range("A6:L"&.Range("D1000").End(xlUp).Row).Borders.Weight =xlThin

End With

End Sub

启动窗体，选择进度类型，单击“查询”按钮，即可得到图7-46所示的效果，同时在 工作表中得到这个进度类型的合同报表

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们 18664393530@aliyun.com 处理，核实后本网站将在24小时内删除侵权内容。