将多个Excel文件批量导入某个Excel文件中
来源:互联网 发布:淘宝权全民直播 编辑:程序博客网 时间:2024/06/10 10:53
这是我老婆提出的一个需求:(1)选中Excel表格中的某些行或列,运行某个Macro,自动根据选中的cells创建新的worksheets,worksheets的名字就是选中的cells的名字。(2)创建新的worksheet的同时,要把某指定目录下与新的worksheet同名的Excel文件的内容copy到新的worksheet中去。
下面是搜索了半天之后搞出来的Macro,没有异常处理,而且使用了copy/paste,文件太多时效率肯定不高。其实完全可以参照
Copy a sheet from each workbook into your workbook in a folder using VBA in Microsoft Excel 和 Optimize Slow VBA Code 中的方法来优化。
Sub LoadSheets() Application.ScreenUpdating = False ' stop screen flickering ' Create new sheets according to selected cells and copy the ' contebts of other files into the new sheets Dim YesNo As Variant, myFolder As String, MyLF As String MyLF = Chr(10) & Chr(13) ' a line feed command myFolder = "D:\Documents and Settings\bonny\My Documents\tools" 'change this to the location of saved XML files 'Windows 7下C盘默认不可写,请修改DefFolder的值 YesNo = MsgBox("This Macro is going to create new worksheets according to your selected cells." & myclf & _ "Do you want to continue?", _ vbYesNo, "Caution") Select Case YesNo Case vbYes myFolder = InputBox("Please enter the folder where all your Excel files locates", Default:=myFolder) 'Create new worksheets For Each cell In Selection.Cells Call CreateNewWorksheet(cell.Value, myFolder) Next Case vbNo Exit Sub End Select Application.ScreenUpdating = TrueEnd SubFunction CreateNewWorksheet(SheetName As String, FolderName As String) As String Dim oSheet As Worksheet, vRet As Variant Dim fName As String 'creating a new excel worksheet Set oSheet = Worksheets.Add With oSheet .Name = SheetName .Move after:=Sheets(Sheets.Count) '在当前所有worksheet后创建新的工作表 .Activate End With 'open Excel files and copy the contents to this sheet Dim newWB As Workbook, curWB As Workbook Dim startRange As Range Set curWB = ThisWorkbook ' For WorkBook and Range objects, set is necessary during assignment fName = FolderName + "\" + SheetName + ".xls" Set newWB = Workbooks.Open(fName) newWB.Activate newWB.Sheets(1).Activate ' 假定只有第一个worksheet需要copy Set startRange = newWB.Sheets(1).UsedRange ' Only used range will be copied newWB.Sheets(1).UsedRange.Copy 'select the destination cell curWB.Activate Sheets(SheetName).Activate Range(startRange.Address).Select ActiveSheet.Paste Application.CutCopyMode = False 'Clear Clipboard newWB.Close Exit FunctionEnd Function
- 将多个Excel文件批量导入某个Excel文件中
- Asp.net中关于excel文件批量导入SQL Server数据表
- 根据导入Excel文件批量修改Oracle数据库中订单信息
- 导入Excel文件批量导入ToSQLServer数据库
- SAP中导入EXCEL文件
- AOS中Excel文件导入
- 如何使用VBA实现将多个Excel文件中的数据复制到某个Excel文件中
- Foxpro、excel文件批量导入数据到Mysql、Oracle
- SSIS将excel文件批量导入到sql server 2005
- SSIS将excel文件批量导入到sql server 2005
- Asp.net中Excel文件导入sqlServer
- JSP中导入导出Excel文件
- 在sqlserver2005中导入excel文件
- 在access中批量导入excel文件
- txt数据格式化导入excel文件中
- c# excel文件导入到sqlserver中
- 将excel文件导入到数据库中
- java中excel文件数据的导入
- 光脚丫学ASP.NET MVC(0004):在ASP.NET MVC中使用Forms身份验证
- .net 读取pdf文件方法
- objective-c学习笔记第九章《objective-c 中数组,字符串,集合》
- Play local Video(flv mainly) in ActionScript3.0
- 全局快门与卷帘式快门
- 将多个Excel文件批量导入某个Excel文件中
- stdafx.h在C++中起到的作用
- 固定窗口大小的方法
- ANDROID获取设备ID、型号及其它信息
- 窗口创建中的 WS_CLIPCHILDREN 和 WS_CLIPSBLINGS风格 细说
- AIX系统管理界面工具SMIT快捷方式
- define特殊用法
- Spinner用法案例
- 登录