如何把批量的excel文件整合到一张表-Excel教程

相信各位职场人士经常会面临将大量excel文件内的数据整合到一张表里面,有时候这些excel文件的数量达数百张,如果一份一份文件打开,工作量将非常巨大,现在教大家如何利用excel内嵌的VBA程序完成这项工作并附上程序代码。

软件版本:Excel2007

制作步骤:

在电脑的H盘(其它盘也行)建立“库存数据”(文件夹名称随意),文件夹的路径为:“H:库存数据”

将大量需要整合的excel文件放于“H:库存数据”,所有文件的结构必须完全一致。

在H盘根目录下建立".xlsm"类型的Excel文件,这里命名为“库存整合3”，打开文件，将第二步中的文件字段名结构复制到"sheet1"工作簿中。

在“sheet2”添加activeX控件-命令按钮，如下图，选中命令按钮后，在工作表中左击并拖动鼠标，把命令按钮添加到"sheet1"区域中。

鼠标放在“CommandButton1”右击，点击“属性”选项，并设置其属性，如下两图：

双击“整合数据“按钮，打开“Microsoft Visual Basic”编辑器，注意：我这里是用一个已经做好的案例示范，下图中第二个红框的对象是“commandButton2”,各位的应该是“commandButton1”！然后在第四个红框和“ Sub”之间插入如下代码：

Application.ScreenUpdating=False

Application.DisplayAlerts=False

Dim s, lj As String

Dim fso, fldPath, fld

Dim wjs, i, j, k, l, y, t, r, e, w, b, n, m As Integer

m=2

For j=2 To 300000 '导入数据前测试原有数据非空单元格

If ThisWorkbook.Worksheets("sheet1").Cells(j, 1)="" Then

GoTo u

If

Next

u:

If j=2 Then

GoTo yy

If

UserForm1.Show 0

For k=2 To j '清除旧数据

DoEvents

UserForm1.Label4.Caption="清除旧数据 :"

UserForm1.Label3.Width=(k / (j - 2)) * 400

If ThisWorkbook.Worksheets("sheet1").Cells(k, 1)="" Then

GoTo y

If

ThisWorkbook.Worksheets("sheet1").Rows(k).Delete

Next

y:

yy:

On Error Resume Next

fldPath="H:库存数据"

Set fso=CreateObject("Scripting.FileSystemObject")

Set fld=fso.GetFolder(fldPath)

wjs=fld.Files.Count

lj="H:库存数据"

s=Dir("H:库存数据*.xls")

Workbooks.Open Filename:=lj & s

For i=1 To 30000 '测试第一个导入数据的非空单元格

If ActiveWorkbook.Worksheets(1).Cells(i, 1)="" Then

GoTo x

If

Next

x:

For l=2 To i '开始导入第一个数据

For y=1 To 11

ThisWorkbook.Worksheets("sheet1").Cells(l, y)=ActiveWorkbook.Worksheets(1).Cells(l, y)

Next

Next

ActiveWorkbook.Close

UserForm1.Show 0

For t=1 To wjs - 1

s=Dir

DoEvents

UserForm1.Label4.Caption=s

UserForm1.Label1.Width=(t / (wjs - 1)) * 200

For r=2 To 300000 '测试导入位置的非空单元格

If ThisWorkbook.Worksheets("sheet1").Cells(r, 1)="" Then

GoTo w

If

Next

w:

m=r

Workbooks.Open Filename:=lj & s

For e=1 To 30000 '测试每一个导入数据的非空单元格

If ActiveWorkbook.Worksheets(1).Cells(e, 1)="" Then

GoTo e

If

Next

e:

For b=r To r + e - 3 '正式导入数据

DoEvents

UserForm1.Label3.Width=((b - r) / e) * 200

For n=1 To 11

ThisWorkbook.Worksheets("sheet1").Cells(b, n)=ActiveWorkbook.Worksheets(1).Cells(b - m + 2, n)

Next

Next

ActiveWorkbook.Close

Next

UserForm1.Label4.Caption="成功了！"

Application.OnTime Now() + TimeValue("00:00:02"), "CL"

程序还设计到显示工作进度的窗体-进度窗体，如下图：

进度窗体的添加方法如下，请各位务必看清每一个细节，否则程序运行出错！

上图中的“进度窗体”为已经设计好的，以下教大家如何制作上图的青色和紫色的窗体，如下图，在窗体中添加4个“Label”空间，并按顺序排序。

“Label2”属性设置：

“Label4”属性设置将“Caption”属性设为“ ”，其它属性和“Label4”同。

“Label4”属性设置如下图：

“Label3”属性设置如下图：

至此显示窗体效果如下图：

添加"Textbok1",并按下图设置属性

并将"Textbox1”移动到绿色条的位置，完全重合，效果如下：

然后添加"Textbox2"，其属性设置和“Textbox1”完全一样，并移动至“Label3”下面，完全重合。

至此进度条效果如下：

插入模块，如下图：

将程序模块命名为“CL”,代码入校如下：

Sub CL()

Unload UserForm2

Sub

大功告成！点击保存按钮！

返回"sheet1"工作区，如下图：

点击“整合数据”按钮，运行数据整合程序：程序首先检查是否存在旧数据，如果存在旧数据则清除完旧数据后启动数据导入功能，如下图：

完满成功！

经验内容仅供参考，如果您需解决具体问题(尤其法律、医学等领域)，建议您详细咨询相关领域专业人士。作者声明：本文系本人依照真实经历原创，未经许可，谢绝转载。