|
由于基础的成交汇总查询报表太多,手工生成汇总日报表特别麻烦。希望能够写一个vba程序,在界面上增加一个按钮,不必打开其他excel,手工点击该按钮更新汇总日报报表的内容。希望将所有的买入、卖出成交金额汇总填写到汇总日报表对应的行,然后将分红信息单独复制到下面
求各个大神帮忙。
- Sub 导入文件()
- Application.ScreenUpdating = False
- Dim Filename, wb As Workbook, Sht As Worksheet, MySht As Worksheet
- Filename = Dir(ThisWorkbook.Path & "\*.xls")
- Set d = CreateObject("scripting.dictionary")
- Set MySht = ActiveSheet
- MySht.Rows("11:65536").ClearContents
- Do While Filename <> ""
- If Filename <> ThisWorkbook.Name Then
- fn = ThisWorkbook.Path & "" & Filename
- Set wb = Workbooks.Open(fn)
- Set Sht = wb.Worksheets(1)
- arr = Sht.[a1].CurrentRegion
- For i = 2 To UBound(arr)
- x = arr(i, 2) & arr(i, 4) '产品名称+业务类别为key
- If arr(i, 4) = "买入" Or arr(i, 4) = "卖出" Then
- d(x) = d(x) + arr(i, 8) '成交金额累加为item
- ElseIf arr(i, 4) = "分红" Then
- r = r + 1
- Sht.Rows(i).Copy MySht.Rows(r + 10) '复制“分红”行
- End If
- Next
- wb.Close False
- End If
- Filename = Dir
- Loop
- With MySht '显示结果
- arr = .[a3:e5]
- For i = 2 To UBound(arr)
- For j = 2 To UBound(arr, 2)
- x = arr(1, j) & Left(arr(i, 1), 2) '产品名称+业务类别为key
- arr(i, j) = d(x)
- Next
- Next
- .[a3:e5] = arr
- End With
- Application.ScreenUpdating = True
- End Sub
复制代码
|
|