VBA 实例:抓取用友导出数据并计算

0
2304

通过抓取用友导出数据,自动计算利息。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
Sub auto_analysis()
 
Dim filepath As String, i As Integer, j As Integer, k As Integer
 
 
 
 filepath = Application.GetOpenFilename("EXCEl Files (*.xls), *.xls*", 0, "选定文件", , False)
 
 ub = UBound(Split(filepath, "\"))
 
 Filename = Split(filepath, "\")(ub)
 
 Sheets.Add after:=Sheets("BASE")
 
 ActiveSheet.Name = Left(Filename, Len(Filename) - 4)
 
 With ActiveSheet
 .Range("a1:h1").MergeCells = True
 .Range("a1") = "利息单"
 .Range("a2:h2").MergeCells = True
 .Range("a2") = "单位:" & Left(Filename, Len(Filename) - 4)
 .Range("a3") = "序号"
 .Range("b3") = "款项"
 .Range("c3") = "起息日期"
 .Range("d3") = "结息日期"
 .Range("e3") = "天数"
 .Range("f3") = "年利率"
 .Range("g3") = "利息"
 .Range("h3") = "备注"
 .Range("a1:h3").HorizontalAlignment = xlCenter
 .Range("a1").Activate
 With Selection.Font
 .Name = "宋体"
 .Bold = True
 .Size = 16
 End With
 End With
 
 
 Start_date = ThisWorkbook.Sheets("BASE").Cells(1, 2)
 
 End_date = ThisWorkbook.Sheets("BASE").Cells(1, 3)
 
 Create_date = ThisWorkbook.Sheets("BASE").Cells(3, 2)
 
 StrIntrest = ThisWorkbook.Sheets("BASE").Cells(2, 2)
 
 With Workbooks.Open(filepath)
 
     m = .Sheets(1).UsedRange.Rows.Count
 
     For i = 2 To m
 
      If .Sheets(1).Range("c" & i) <> "" Then '凭证号不为空
 
      Select_date = DateSerial(2015, .Sheets(1).Range("a" & i), .Sheets(1).Range("b" & i))
 
 
      If Select_date > Start_date And Select_date < End_date Then
 
 
      k = k + 1
      With Workbooks("Intrest_Com").Sheets(Left(Filename, Len(Filename) - 4))
 
      .Range("a" & 3 + k) = k '序列号
      If k = 1 Then
 
      .Range("b" & 3 + k) = Workbooks(Filename).Sheets(1).Range("h" & i - 1)
      .Range("c" & 3 + k) = Start_date
      .Range("d" & 3 + k) = End_date
      .Range("e" & 3 + k) = End_date - .Range("c" & 3 + k) + 1
      .Range("f" & 3 + k) = StrIntrest
      .Range("g" & 3 + k) = StrIntrest * .Range("b" & 3 + k) * .Range("e" & 3 + k) / 360
      '序列号为2
      .Range("a" & 3 + k + 1) = k + 1
      .Range("b" & 3 + k + 1) = Workbooks(Filename).Sheets(1).Range("e" & i) + Workbooks(Filename).Sheets(1).Range("f" & i) * (-1)
      .Range("c" & 3 + k + 1) = Select_date
 
      Else
      .Range("b" & 3 + k + 1) = Workbooks(Filename).Sheets(1).Range("e" & i) + Workbooks(Filename).Sheets(1).Range("f" & i) * (-1)
      .Range("c" & 3 + k + 1) = Select_date
      End If
 
      .Range("d" & 3 + k + 1) = End_date
      .Range("e" & 3 + k + 1) = End_date - .Range("c" & 3 + k + 1) + 1
      .Range("f" & 3 + k + 1) = StrIntrest
      .Range("g" & 3 + k + 1) = StrIntrest * .Range("b" & 3 + k + 1) * .Range("e" & 3 + k + 1) / 360
 
      End With
 
 
      'MsgBox Select_date
 
      End If
 
      End If
 
     Next i
    .Close False
 
 End With
 
With Workbooks("Intrest_Com").Sheets(Left(Filename, Len(Filename) - 4))
mm = .UsedRange.Rows.Count
.Range("a" & mm + 1) = "合计"
.Range("b" & mm + 1) = Application.WorksheetFunction.Sum(.Range("b4:b" & mm))
.Range("g" & mm + 1) = Application.WorksheetFunction.Sum(.Range("g4:g" & mm))
.Range("a" & mm + 2 & ":h" & mm + 2).MergeCells = True
.Range("a" & mm + 2) = "XXX处" & Format(Create_date, "Long Date") & "制"
.Range("a3:h" & mm + 2).Font.Name = "宋体"
.Range("a3:h" & mm + 2).Font.Size = 12
.Range("a3:h" & mm + 1).Borders.LineStyle = xlContinuous
.Range("a3:a" & mm + 1).HorizontalAlignment = xlCenter
.Range("c3:f" & mm + 1).HorizontalAlignment = xlCenter
.Range("b3:b" & mm + 1).NumberFormat = "0.00"
.Range("g3:g" & mm + 1).NumberFormat = "0.00"
.Range("f3:f" & mm + 1).NumberFormat = "0.00%"
.Range("a2").HorizontalAlignment = xlLeft
.Range("a" & mm + 2).HorizontalAlignment = xlRight
End With
End Sub

Update 2016-01-13:
如遇报错,请在63行、103行Intrest_Com加上后缀.xlsm

发表回复

+ 47 = 54