[会计精品]用EXCEL+ACCESS做的凭证报表处理系统

jiutian

<p>打不开啊,按电脑的年+月+日作为密码打不开呢</p><p></p>

mingg

很不错,希望楼主把开发的另外一个会计小软件(记帐凭证汇总表)公布出来让我们共同学习.

zbs112

<p><br/>Private Sub CommandButton1_Click()<br/>&nbsp; On Error Resume Next<br/>&nbsp; Application.ScreenUpdating = False<br/>&nbsp; With Sheets("财务分析表")<br/>&nbsp;&nbsp;&nbsp;&nbsp; Call 清空表格数据<br/>&nbsp;&nbsp;&nbsp;&nbsp; If ComboBox1 = "" Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox "点击借方(贷方)汇总、年度", vbInformation, "凭证处理系统"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Call 清空表格数据: Exit Sub<br/>&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp; SQL = "Select * From 科目表"<br/>&nbsp;&nbsp;&nbsp;&nbsp; If ComboBox3 = "" Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL = SQL &amp; " Order by 科目编码"<br/>&nbsp;&nbsp;&nbsp;&nbsp; Else<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SQL = SQL &amp; " Where 类型='" &amp; ComboBox3.Text &amp; "' Order by 科目编码"<br/>&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp; RST1.Open SQL, CNN, adOpenKeyset, adLockOptimistic<br/>&nbsp;&nbsp;&nbsp;&nbsp; If RST1.EOF Or RST1.BOF Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox "没有发现" &amp; ComboBox3.Text &amp; "类的会计科目!", vbInformation, "凭证处理系统"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Call 清空表格数据: RST1.Close: Set RST1 = Nothing: Exit Sub<br/>&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp; Cells(2, "B") = ComboBox1.Text &amp; "年度" &amp; ComboBox3.Text &amp; "科目分月汇总表"<br/>&nbsp;&nbsp;&nbsp;&nbsp; ProgressBar1.Max = RST1.RecordCount '进度条的最大值<br/>&nbsp;&nbsp;&nbsp;&nbsp; For I = 1 To RST1.RecordCount<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MaxRow = Range("B65536").End(xlUp).Row + 1<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ProgressBar1.Value = I&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '进度条的动态值<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, "B") = RST1.Fields("科目编码")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, "C") = RST1.Fields("总账科目")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, "D") = RST1.Fields("明细科目")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, "S") = RST1.Fields("现金编码")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If OptionButton1.Value = True Then Cells(MaxRow, "E") = "借"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If OptionButton2.Value = True Then Cells(MaxRow, "E") = "贷"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If OptionButton3.Value = True Then Cells(MaxRow, "E") = "※"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; If OptionButton4.Value = True Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, "E") = "借"&nbsp;&nbsp; '科目方向填充<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow + 1, "B") = RST1.Fields("科目编码")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow + 1, "C") = RST1.Fields("总账科目")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow + 1, "D") = RST1.Fields("明细科目")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow + 1, "E") = "贷" '科目方向填充<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow + 1, "S") = RST1.Fields("现金编码")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End If<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; For Months = 1 To CInt(ComboBox2.Text) '科目逐月汇总<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Call MonthCount&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '逐月汇总模块<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Next Months<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; RST1.MoveNext<br/>&nbsp;&nbsp;&nbsp;&nbsp; Next I<br/>&nbsp;&nbsp;&nbsp;&nbsp; RST1.Close: Set RST1 = Nothing<br/>&nbsp;&nbsp;&nbsp;&nbsp; Range("R4:R" &amp; MaxRow + 1).Formula = "=SUM(F4:Q4)"<br/>&nbsp;&nbsp;&nbsp;&nbsp; Range("F4:R" &amp; MaxRow + 1).NumberFormatLocal = "#,##0.00"<br/>&nbsp;&nbsp;&nbsp;&nbsp; Call 字体线框4<br/>&nbsp;&nbsp;&nbsp;&nbsp; If OptionButton4.Value = True Then Call 月份汇总数据<br/>&nbsp; End With<br/>&nbsp; Unload Me<br/>&nbsp; Application.ScreenUpdating = True<br/>End Sub</p><p></p><p>Sub MonthCount()<br/>&nbsp;&nbsp; On Error Resume Next<br/>&nbsp;&nbsp; Dim JFHJ As Double, DFHJ As Double, JDCY As Double<br/>&nbsp;&nbsp; SQL = "Select sum(借方金额) as 借方合计,sum(贷方金额) as 贷方合计 From 分录表 "<br/>&nbsp;&nbsp; SQL = SQL &amp; " Where 年=" &amp; CInt(ComboBox1.Text) &amp; ""<br/>&nbsp;&nbsp; SQL = SQL &amp; " And 月=" &amp; Months &amp; ""<br/>&nbsp;&nbsp; SQL = SQL &amp; " And 科目编码='" &amp; Trim(Cells(MaxRow, "B")) &amp; "'"<br/>&nbsp;&nbsp; RST2.Open SQL, CNN, adOpenKeyset, adLockOptimistic<br/>&nbsp;&nbsp; JFHJ = RST2.Fields("借方合计")<br/>&nbsp;&nbsp; DFHJ = RST2.Fields("贷方合计")<br/>&nbsp;&nbsp; If OptionButton1.Value = True Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, Months + 5) = JFHJ&nbsp; '借方<br/>&nbsp;&nbsp; End If<br/>&nbsp;&nbsp; If OptionButton2.Value = True Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, Months + 5) = DFHJ&nbsp; '贷方<br/>&nbsp;&nbsp; End If<br/>&nbsp;&nbsp; If OptionButton3.Value = True Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Select Case RST1.Fields("方向")<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case "借"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JDCY = JFHJ - DFHJ<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Case "贷"<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; JDCY = DFHJ - JFHJ<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; End Select<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, Months + 5) = JDCY&nbsp; '借贷差额<br/>&nbsp;&nbsp; End If<br/>&nbsp;&nbsp; If OptionButton4.Value = True Then<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow, Months + 5) = JFHJ&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; '借方<br/>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Cells(MaxRow + 1, Months + 5) = DFHJ&nbsp; '贷方<br/>&nbsp;&nbsp; End If<br/>&nbsp;&nbsp; JFHJ = 0: DFHJ = 0: JDCY = 0<br/>&nbsp;&nbsp; RST2.Close: Set RST2 = Nothing<br/>End Sub</p>

[此贴子已经被作者于2007-9-26 16:08:26编辑过]

mingg

<strong>zbs112，你好！我看到你楼上的代码了，好象应该是关于“记帐凭证汇总表”的代码，但我不知道工作表以及数据库的结构，能否发一份完整的给我。谢谢！我用了一下，你的这个系统非常好用，我也试用了论坛上其他人的帐务系统，感觉你这个最棒，技术最先进，利用了sql，速度最快！</strong>

GEB_0219

系统做的不错,可惜与EH里其他会计系统一样,只能支持二级科目,财务上能满足,实际工作中管理要求经常需要到四级科目,另外货币能加入外币多好.

lh442246

此财务系统我用几个月,感觉很不错,作者辛苦了.谢谢分享!

maowei3511

真是太牛了，，，佩服啊！<br/>真是太牛了，，，佩服啊！<br/>[em05]

zhou8108

<p>为什么无法下载?</p>

lindw

zbs112真是太伟大了,这是我见过用EXCEL做的最精品的账务处理系统

slese

支持,学习一下