如何用vbs来合并多个excel文件
答案:2 悬赏:10
解决时间 2021-12-30 20:20
- 提问者网友:傀儡离开
- 2021-12-30 14:39
如何用vbs来合并多个excel文件
最佳答案
- 二级知识专家网友:怪咖小青年
- 2021-12-30 15:49
在存放文件的目录之外打开一个空的Excel文档
运行下面分宏:(注意文件目录)
Sub cfl()
Dim fs, f, f1, fc, s, x
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("e:\test\") '存放文件的目录
Set fc = f.Files
For Each f1 In fc
If Right(f1.Name, 3) = "xls" Then
x = x + 1
Workbooks.Open (f1.Path)
For i = 1 To 255
Workbooks(1).Sheets(1).Cells(x, i).Value = _
Workbooks(2).Sheets(1).Cells(1, i).Value
Next
Workbooks(2).Close savechanges:=False
End If
Next
End Sub
运行下面分宏:(注意文件目录)
Sub cfl()
Dim fs, f, f1, fc, s, x
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder("e:\test\") '存放文件的目录
Set fc = f.Files
For Each f1 In fc
If Right(f1.Name, 3) = "xls" Then
x = x + 1
Workbooks.Open (f1.Path)
For i = 1 To 255
Workbooks(1).Sheets(1).Cells(x, i).Value = _
Workbooks(2).Sheets(1).Cells(1, i).Value
Next
Workbooks(2).Close savechanges:=False
End If
Next
End Sub
全部回答
- 1楼网友:邪性洒脱
- 2021-12-30 16:11
简单代码如下:
dim fd as filedialog
dim wbook as workbook
dim rowindex as integer
set fd = application.filedialog(msofiledialogfilepicker)
dim vrtselecteditem as variant
with fd
.filters.add "excel 文件", "*.xls", 1 ‘过滤
if .show = -1 then
rowindex = 1
for each vrtselecteditem in .selecteditems
set wbook = workbooks.open(vrtselecteditem)
workbooks("综合表.xls").worksheets("sheet1").cells(rowindex, 1) = wbook.worksheets("sheet1").cells(1, 1).value
workbooks("综合表.xls").worksheets("sheet1").cells(rowindex, 2) = wbook.worksheets("sheet1").cells(1, 2).value
rowindex = rowindex + 1
wbook.close
next vrtselecteditem
else
end if
end with
set fd = nothing
综合表.xls是要最终的表,rowindex初值可以设从哪一行开始,cells后括号里的索引分别是行号和列号
运行后多选你文件夹中的档案或全选,按确定后等着就行了。
具体的你再完善和修改吧,不行hi我。
我要举报
如以上问答内容为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯