本帖最后由 过江龙 于 2013-11-4 08:59 编辑
请老师帮忙,看了几天的封装代码的几个列子,自己学将代码封装,怎么来出错。有哪位老师能否指点下,最好将VB6.0的源码放出来学习研究哈,谢谢!
- Dim xlApp As Excel.Application
- Public Sub JL()
- Dim x%, y%
- Dim Name$, WBname$
- Dim i%, j%, k%
- Dim Danwei, Bianma, c, d, t
- Dim f As Long
- Set xlApp = GetObject(, "Excel.application")
- With xlApp
- WBname = .ActiveWorkbook.Name
- .Application.ScreenUpdating = False
- With .Application.FileDialog(1)
- .Title = "请选择要导入的Excel文件"
- If .Show = -1 Then
- xlApp.Workbooks.Open .SelectedItems(1)
- Else
- Exit Sub
- End If
- End With
- Name = .ActiveWorkbook.Name
- With .ActiveWorkbook.Worksheets(1)
- .Select
- .Copy Before:=xlApp.Workbooks(WBname).Worksheets(1)
- End With
- .Workbooks(Name).Close False
- With .Sheets(1)
- .Select
- .Name = "temp"
- End With
- With .Worksheets("预内资金") 'with sheet1
- i = .[A65536].End(xlUp).Row - 2
- .Range("A5:J" & i).ClearContents
- .Range("H" & i + 1).ClearContents
- End With
- With xlApp.Worksheets("专户资金")
- i = .[A65536].End(xlUp).Row - 2
- .Range("A5:J" & i).ClearContents
- .Range("H" & i + 1).ClearContents
- End With
- Danwei = .Application.InputBox("请输入申请单位:", "提示", "宜宾市翠屏区宗场镇中心小学校", , , , , 2)
- If Danwei = False Then
- 删除temp表
- .Application.ScreenUpdating = True
- Exit Sub
- End If
- Bianma = .Application.InputBox("请输入单位编码:", "提示", 113757, , , , , 2)
- If Bianma = False Then
- 删除temp表
- .Application.ScreenUpdating = True
- Exit Sub
- End If
- With .Worksheets("预内资金")
- .Range("A2") = "申请单位(盖章):" & Danwei & " 单位编码:" & Bianma & " " & Format(Now, "yyyy""年""m""月""d""日""") & " 金额单位:元"
- xlApp.Worksheets("专户资金").Range("A2") = "申请单位(盖章):" & Danwei & " 单位编码:" & Bianma & " " & Format(Now, "yyyy""年""m""月""d""日""") & " 金额单位:元"
- y = 5
- f = 5
- For Each c In xlApp.Range("D3:D" & xlApp.Range("D65536").End(xlUp).Row)
- If Left(c, xlApp.Application.Search("-", c) - 1) <> "12" Then 'Left第一个起,Right最后一个起,Len文本个数
- x = c.Row
- .Cells(y, 1) = Left(xlApp.Cells(x, 5), xlApp.Application.Search("-", xlApp.Cells(x, 5)) - 1)
- .Cells(y, 2) = Right(xlApp.Cells(x, 5), Len(xlApp.Cells(x, 5)) - xlApp.Application.Search("-", xlApp.Cells(x, 5)))
- .Cells(y, 3) = Left(xlApp.Cells(x, 6), xlApp.Application.Search("-", xlApp.Cells(x, 6)) - 1)
- .Cells(y, 4) = Right(xlApp.Cells(x, 6), Len(xlApp.Cells(x, 6)) - xlApp.Application.Search("-", xlApp.Cells(x, 6)))
- .Cells(y, 5) = Right(xlApp.Cells(x, 4), Len(xlApp.Cells(x, 4)) - xlApp.Application.Search("-", xlApp.Cells(x, 4)))
- .Cells(y, 6) = Right(xlApp.Cells(x, 11), Len(xlApp.Cells(x, 11)) - xlApp.Application.Search("-", xlApp.Cells(x, 11)))
- .Cells(y, 7) = .Cells(y, 4)
- .Cells(y, 8) = xlApp.Cells(x, 7)
- y = y + 1
- If .Cells(y, 1) = "合 计" Then
- .Cells(y, 1).EntireRow.Insert
- End If
- Else
- x = c.Row
- xlApp.Worksheets("专户资金").Cells(f, 1) = Left(xlApp.Cells(x, 5), xlApp.Application.Search("-", xlApp.Cells(x, 5)) - 1)
- xlApp.Worksheets("专户资金").Cells(f, 2) = Right(xlApp.Cells(x, 5), Len(xlApp.Cells(x, 5)) - xlApp.Application.Search("-", xlApp.Cells(x, 5)))
- xlApp.Worksheets("专户资金").Cells(f, 3) = Left(xlApp.Cells(x, 6), xlApp.Application.Search("-", xlApp.Cells(x, 6)) - 1)
- xlApp.Worksheets("专户资金").Cells(f, 4) = Right(xlApp.Cells(x, 6), Len(xlApp.Cells(x, 6)) - xlApp.Application.Search("-", xlApp.Cells(x, 6)))
- xlApp.Worksheets("专户资金").Cells(f, 5) = Right(xlApp.Cells(x, 4), Len(xlApp.Cells(x, 4)) - xlApp.Application.Search("-", xlApp.Cells(x, 4)))
- xlApp.Worksheets("专户资金").Cells(f, 6) = Right(xlApp.Cells(x, 11), Len(xlApp.Cells(x, 11)) - xlApp.Application.Search("-", xlApp.Cells(x, 11)))
- xlApp.Worksheets("专户资金").Cells(f, 7) = xlApp.Worksheets("专户资金").Cells(f, 4)
- xlApp.Worksheets("专户资金").Cells(f, 8) = xlApp.Cells(x, 7)
- f = f + 1
- If xlApp.Worksheets("专户资金").Cells(f, 1) = "合 计" Then
- xlApp.Worksheets("专户资金").Cells(f, 1).EntireRow.Insert
- End If
- End If
- Next c
- For Each d In .Range("A5:A" & .[A65536].End(xlUp).Row)
- If d = "合 计" Then
- j = d.Row
- .Cells(j, 8) = Application.Sum(.Range("H5:H" & j - 1))
- If j > 16 Then
- .Range("A16:A" & j).SpecialCells(4).Delete (3)
- End If
- End If
- Next d
- For Each t In xlApp.Worksheets("专户资金").Range("A5:A" & xlApp.Worksheets("专户资金").[A65536].End(xlUp).Row)
- If t = "合 计" Then
- k = t.Row
- xlApp.Worksheets("专户资金").Cells(k, 8) = Application.Sum(xlApp.Worksheets("专户资金").Range("H5:H" & k - 1))
- If k > 16 Then
- xlApp.Worksheets("专户资金").Range("A16:A" & k).SpecialCells(4).Delete (3)
- End If
- End If
- Next t
- End With
- 删除temp表
- Application.ScreenUpdating = True
- End With
- MsgBox "导入完成", vbInformation + vbOKOnly
- End Sub
- Private Sub 删除temp表()
- With xlApp
- .Application.DisplayAlerts = False '屏弊删除提示
- .Sheets("temp").Delete
- .Application.DisplayAlerts = True
- .Worksheets("预内资金").Select
- End With
- End Sub
复制代码
|