软件总结整理(1)
标签(空格分隔): 程序猿
源代码
Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim text1text As String
Dim text2text As String
Dim temp As String
Dim count As Integer
Dim count2 As Integer
Dim newxls As New excel.Application
Dim newbook As New excel.Workbook
Dim newsheet As New excel.Worksheet
Set newxls = CreateObject("Excel.Application")
Set newbook = newxls.Workbooks.Open("" & App.Path & "\金具统计.xlsx") '创建工作簿
Set newsheet = newbook.Worksheets(1) '创建工作表
newsheet.Range("A1:IV65536").Clear
Dim aa() As Integer '单个图号,每个金具的数量,最后叠加成bb
Dim bb() As Integer '所有图号,每个金具的数量
Dim ee() As Integer '单个图号,每个金具的数量(未乘个数)
Dim cc() As String '与aa对应,表示相应金具的型号
Dim dd() As String '与aa对应,表示相应金具的名称
Dim temppp() As String
ReDim temppp(0 To DataCombo1.UBound)
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\jjk.mdb;Persist Security Info=False"
sql = "select * FROM jjk"
rs.Open sql, cn
count = rs.Fields.count
ReDim aa(count)
ReDim bb(count)
ReDim cc(count)
ReDim ee(count)
ReDim dd(count)
ReDim jj(count)
cn.Close
For i = 0 To DataCombo1.UBound '所有复合框循环,实现累加
temp = Trim(DataCombo1(i).Text)
If temp <> "" Then
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\jjk.mdb;Persist Security Info=False"
sql = "select * FROM jjk where sno='" & temp & "'"
rs.Open sql, cn
count = rs.Fields.count
If rs.EOF <> True Then
For ii = 2 To count - 1 '所有字段循环,计算每个金具的数量
aa(ii) = 0
aa(ii) = rs.Fields(ii) * Val(Text1(i).Text)
cc(ii) = rs(ii).Name
ee(ii) = rs.Fields(ii)
If ee(ii) <> 0 Then
temppp(i) = temppp(i) & cc(ii) & "=" & ee(ii) & vbCrLf
End If
bb(ii) = bb(ii) + aa(ii)
Next ii
If DataCombo1(i).Text <> "" Then
text2text = text2text & vbCrLf & "第" & i + 1 & "个串号:" & DataCombo1(i).Text & " 共计" & Text1(i) & "串,每串对应金具及数量如下:" & vbCrLf & temppp(i)
End If
Else
newbook.Close (True)
newxls.Quit
Set newxls = Nothing
MsgBox "第" & i + 1 & "串图号不存在,或者该串图号输入错误,请检查!"
Exit Sub
End If
cn.Close
End If
Next i
For ii = 2 To count - 1
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & App.Path & "\jjk.mdb;Persist Security Info=False"
sql = "select [" & cc(ii) & "] FROM jjm"
rs.Open sql, cn
On Error Resume Next
If rs.EOF <> True Then
dd(ii) = rs(0)
End If
cn.Close
Next ii
ss = 3
Text2.Text = ""
For h = 1 To count '循环所有字段,如果总数不等于0,就在excel中记录下来
If bb(h) <> 0 Then
newsheet.Cells(ss, 1) = ss - 2
newsheet.Cells(ss, 2) = dd(h)
newsheet.Cells(ss, 3) = cc(h)
newsheet.Cells(ss, 4) = bb(h)
text1text = text1text & dd(h) & " " & cc(h) & "=" & bb(h) & vbCrLf
ss = ss + 1
End If
Next h
newsheet.Cells(1, 1) = "金具数量统计表"
newsheet.Cells(2, 1) = "序号"
newsheet.Cells(2, 2) = "金具名称"
newsheet.Cells(2, 3) = "金具型号"
newsheet.Cells(2, 4) = "金具数量"
newsheet.Cells(1, 1).Font.Bold = True
newsheet.Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).Merge
newsheet.Range(Cells(2, 2), Cells(100, 3)).Columns.EntireColumn.AutoFit
Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).HorizontalAlignment = xlCenter
Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).VerticalAlignment = xlCenter
Text2.Text = "金具统计报告" & vbCrLf & "金具统计情况如下:" & vbCrLf & text1text & vbCrLf & vbCrLf & "请仔细核对串号对应金具及数量是否正确!" & text2text
newbook.Close (True)
newxls.Quit
Set newxls = Nothing
End Sub
技巧总结
新建并引用EXCEL表
打开和关闭具体参见代码
打开之后记得关闭,先关闭文件,再关闭程序,再释放内存
redim的使用
Dim aa() As Integer
ReDim aa(count)
重新定义会刷新数组的值,如果原值不变则需要:
Redim Preserve MyArray(15)
数据集的操作
数据集的值:rs.fields(i)或者rs(i)或者rs("U-10")
数据集的字段名:rs(i).name
数据集的字段总数:rs.fields.count
几个SQL语句
sql = "select * FROM jjk where sno='" & temp & "'"
sql = "select [" & cc(ii) & "] FROM jjm"
On Error Resume Next的使用
jjk里面有个字段,但是jjm里面没有,在运行sql = "select [" & cc(ii) & "] FROM jjm"时出错,此时可以运用这个语句,忽略这个问题,不会对结果造成影响。
exit sub 的使用
要是运行到一半想退出,可运行这条语句
在EXCEL中的操作语言
- 写入excel
ss = 3
For h = 1 To count
If bb(h) <> 0 Then
newsheet.Cells(ss, 1) = ss - 2
newsheet.Cells(ss, 2) = dd(h)
newsheet.Cells(ss, 3) = cc(h)
newsheet.Cells(ss, 4) = bb(h)
ss = ss + 1
End If
Next h
- 合并、加粗、居中、自动变化大小等
newsheet.Cells(1, 1).Font.Bold = True
newsheet.Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).Merge
newsheet.Range(Cells(2, 2), Cells(100, 3)).Columns.EntireColumn.AutoFit
Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).HorizontalAlignment = xlCenter
Range(newsheet.Cells(1, 1), newsheet.Cells(1, 4)).VerticalAlignment = xlCenter
控件数组的个数
控件数组的个数:DataCombo1.UBound
Dim temppp() As String
ReDim temppp(0 To DataCombo1.UBound)
网友评论