如何将多个Excel文件合并成一个且保留原有数据?
时间:2023-05-12 08:38:07
如何将多个Excel文件合并成一个且保留原有数据?事实上,我们可以借用Excel宏命令来处理该操作,并且保留原有数据,建立多个Sheet页,具体操作请看下文多个Excel文件合并成一个的方法。
如何将多个Excel文件合并成一个且保留原有数据?
1、将需合并的Excel文件放在同一个文件夹中,并这个文件中新建一个Excel文件。
2、打开新建的Excel文件,按 Alt + F11 键,打开宏,选择视图→代码窗口。
3、将下面的代码拷贝进去:
01Sub sheets2one()
02'定义对话框变量
03Dim cc As FileDialog
04Set cc = Application.FileDialog(msoFileDialogFilePicker)
05Dim newwork As Workbook
06Set newwork = Workbooks.Add
07With cc
08If .Show = -1 Then
09Dim vrtSelectedItem As Variant
10Dim i As Integer
11i = 1
12For Each vrtSelectedItem In .SelectedItems
13Dim tempwb As Workbook
14Set tempwb = Workbooks.Open(vrtSelectedItem)
15tempwb.Worksheets(1).Copy Before:=newwork.Worksheets(i)
16newwork.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".xls", "")
17tempwb.Close SaveChanges:=False
18i = i + 1
19Next vrtSelectedItem
20End If
21End With
22Set cc = Nothing
23End Sub
复制代码
Sub sheets2one()'定义对话框变量Dim cc As FileDialogSet cc = Application.FileDialog(msoFileDialogFilePicker)Dim newwork As WorkbookSet newwork = Workbooks.AddWith ccIf .Show = -1 ThenDim vrtSelectedItem As VariantDim i As Integeri = 1For Each vrtSelectedItem In .SelectedItemsDim tempwb As WorkbookSet tempwb = Workbooks.Open(vrtSelectedItem)tempwb.Worksheets(1).Copy Before:=newwork.Worksheets(i)newwork.Worksheets(i).Name = VBA.Replace(tempwb.Name, ".xls", "")tempwb.Close SaveChanges:=Falsei = i + 1Next vrtSelectedItemEnd IfEnd WithSet cc = NothingEnd Sub
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
Word查找和替换这样用,批量处理工作,高明!
![](https://img.aspxhome.com/file/2023/7/22437_0s.gif)
excel数字转日期
![](https://img.aspxhome.com/file/2023/9/a167609_0s.jpg)
Win10重装系统 Win10在线一键重装电脑系统
![](https://img.aspxhome.com/file/2023/26/a220524_0s.png)
Win10 Mobile一周年更新预览版14383三个未修复问题
![](https://img.aspxhome.com/file/2023/29/a261140_0s.jpg)
Win11摄像头怎么设置对比度? win11连接相机调整对比度的技巧
![](https://img.aspxhome.com/file/2023/28/a240004_0s.jpg )
Acrobat Pro DC 教程「18」,如何拆分PDF文件?
![](https://img.aspxhome.com/file/2023/8/a202878_0s.jpeg)
win10怎么关闭wifi?win10关闭WiFi的两种方法
![](https://img.aspxhome.com/file/2023/28/a242240_0s.jpg)
如何使用移动硬盘全新安装win7
如何将wps演示文稿输出为视频
Win10/Win8.1原生支持FFmpeg:视频格式通吃
![](https://img.aspxhome.com/file/2023/28/a249785_0s.jpg)
excel表格如何使用查找替换功能?
![](https://img.aspxhome.com/file/2023/7/41457_0s.jpg)
在C2单元格中输入公式: =IF($B$11-B2E2
wps表格如何设置为无框线
![](https://img.aspxhome.com/file/2023/3/a182613_0s.png)
Acrobat中pdf文件页面怎么双联显示?
![](https://img.aspxhome.com/file/2023/9/15129_0s.png)
windows 中用cmd 实现定时关机的方法
win10如何快速调出任务管理器?win10快速打开任务管理器的四种方法
![](https://img.aspxhome.com/file/2023/28/a241176_0s.jpg)
PS拉伸放大图片变形怎么办?PS拉伸图片不变形的教程
![](https://img.aspxhome.com/file/2023/5/46665_0s.png)
如何解决在Word中格式刷之后公式靠上
![](https://img.aspxhome.com/file/2023/4/35624_0s.jpg)
WinXP系统电脑桌面我的文档图标消失的恢复教程
![](https://img.aspxhome.com/file/2023/2/a285375_0s.jpg)
超级简单的 win10 系统安装U盘制作方法
![](https://img.aspxhome.com/file/2023/30/a263041_0s.png)