当前位置：首页 > news >正文

vb操作电子表格文件夹内多表格提取数据在生成一个新表格

news 2024/10/23 13:27:56

Private Sub 生成电子表格汇总表_Click()

Dim xlApp As New Excel.Application '定义并创建EXCEL对象
Dim xlBook As New Excel.Workbook '创建工作簿
Dim I As Integer
Dim ger As New Excel.Workbook '创建工作簿
I = 7
Dim sfzh, gjz As String
xlApp.DisplayAlerts = False
xlApp.Visible = True '让Excel可见
Set xlBook = xlApp.Workbooks.Open(App.Path & “/清算人员汇总表.xls”) '打开Excel模板
Dim fso As Object, fd As Object, f As Object
Set fso = CreateObject(“Scripting.FileSystemObject”)
Set fd = fso.GetFolder(“D:\清算人员”)
For Each f In fd.Files
'这里是对遍历出的每个文件进行操作
'其中f.Name是文件名，f.Path是文件路径，
'f.Size是文件大小，f.Type是文件类型，等等
'为了防止卡顿，可以在这个循环里用下面这行：
'DoEvents
I = I + 1
wjm = f.Name
Set ger = xlApp.Workbooks.Open(“D:\清算人员/” & f.Name) '打开个人表
xlBook.ActiveSheet.Range(“b” & I & “”) = ger.ActiveSheet.Range(“b5”) '姓名
xlBook.ActiveSheet.Range(“c” & I & “”) = ger.ActiveSheet.Range(“g5”) '身份证
xlBook.ActiveSheet.Range(“d” & I & “”) = ger.ActiveSheet.Range(“j5”) '状态
xlBook.ActiveSheet.Range(“c” & I & “”) = ger.ActiveSheet.Range(“g5”) '身份证
xlBook.ActiveSheet.Range(“e” & I & “”) = ger.ActiveSheet.Range(“n5”) '清算时间
xlBook.ActiveSheet.Range(“G” & I & “”) = ger.ActiveSheet.Range(“d11”) '计划缴费工资
xlBook.ActiveSheet.Range(“H” & I & “”) = ger.ActiveSheet.Range(“e11”) '实际发放发放金额
xlBook.ActiveSheet.Range(“M” & I & “”) = ger.ActiveSheet.Range(“K11”)
xlBook.ActiveSheet.Range(“N” & I & “”) = ger.ActiveSheet.Range(“L11”)
xlBook.ActiveSheet.Range(“O” & I & “”) = ger.ActiveSheet.Range(“M11”)

ger.Close
'‘ger = Nothing
Next
Set fd = Nothing
Set fso = Nothing

If Dir(“D:\医院清算人员汇总表.xls”) <> “” Then
Kill “D:\医院清算人员汇总表.xls”
End If
'‘ActiveWorkbook.SaveAs FileName:=“D:医院清算人员汇总表.xls”
xlBook.SaveAs FileName:=“D:\医院清算人员汇总表.xls”
xlBook.Close
Set xlBook = Nothing
xlApp.Quit
Set xlApp = Nothing
L:
End Sub

查看全文

http://www.mrgr.cn/news/53609.html