AppleScript的多个Excel文件合并成一个工作表 [英] AppleScript to combine multiple Excel files into a single worksheet
本文介绍了AppleScript的多个Excel文件合并成一个工作表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我不是AppleScript的专家，所以我本想找到的AppleScript code的一个例子，可以成功地处理了一批Excel文件（每一个有一个工作表），每个拷贝的内容一进一出单个目标表。
这是伪code，我脑子里想的：
挑Excel文件的源文件夹;
挑选目的地Excel文件;在源文件夹中的每个文件：
从默认工作表复制数据;
数据粘贴到目标表的第一个未使用的行
结束
这是我想出了code。它正确地打开每个文件，但是复制/过去的操作只是没有发生。不知道如何得到它的工作？
设置main_folder选择具有提示文件夹请选择包含Excel文件的文件夹：设置target_excel选择具有提示的文件请选择目标Excel文件：设置excel_extension_list为{XLS，XLSX，CSV}告诉应用程序发现者
设置别名列表excel_files来（其扩展名是在excel_extension_list main_folder的文件）
告诉结束告诉应用程序Microsoft Excel中
开放target_excel 在excel_files a_file重复
开放a_file
激活a_file
告诉工作簿a_file的表1
the_range设置有二手范围值
设置number_of_source_rows计算the_range的行
告诉结束 激活target_excel
告诉工作簿target_excel的表1
设置new_range有二手范围值
设置number_of_destination_rows计算new_range的行
设置destination_range范围为A＆（number_of_destination_rows + 1）：E和（number_of_destination_rows + 1 + number_of_source_rows）
destination_range的设定值the_range
关闭工作簿保存a_file没有
告诉结束
重复结束
告诉结束
解决方案
久经考验在Excel 2011
我的假设
- 目标文件有一个名为表
工作表Sheet1 - 我从检索的所有文件的第1张的信息。改变如适用。
code
我评论了code，所以你不应该有了解它的任何问题。 ：）
子样品（）
昏暗WBI作为工作簿，WBO作为工作簿
昏暗lRowO只要
昏暗lRowI长，lColI只要
昏暗DestFile为Variant
昏暗RootFldr作为字符串，FilesFolder作为字符串，strFile作为字符串 ~~＆GT;获取根文件夹
RootFldr = MacScript（返回（路径桌面文件夹中）为字符串） ~~＆GT;显示文件夹浏览器选择具有文件的文件夹
FilesFolder = MacScript（（选择带有提示的文件夹请选择具有Excel文件＆放文件夹; _
默认位置的别名，与＆amp; RootFldr＆安培; ）作为字符串） ~~＆GT;如果用户不选择任何内容，然后退出
如果FilesFolder =然后退出小组 ~~＆GT;显示输出文件的文件选择对话框
DestFile = Application.GetOpenFilename（XLS8，XLS4） ~~＆GT;打开输出文件。
设置WBO = Workbooks.Open（DestFile） ~~＆GT;获取写入下一个可用行
lRowO = wbO.Sheets（工作表Sheet1）Cells.Find（什么：=*，_
后：= wbO.Sheets（工作表Sheet1），范围（A1），_
LOOKAT：= xlPart，_
看着：= xlFormulas）.Row + 1 ~~＆GT;通过在文件夹中的每个文件循环
strFile = DIR（FilesFolder） 做虽然莱恩（strFile）GT; 0
~~＆GT;检查的文件，如果它是CSV，XLS或XLSX
如果右（strFile，3）=CSV或_
右（strFile，3）=XLS或_
右（strFile，4）=XLSX然后
~~＆GT;从文件夹中打开文件
设置WBI = Workbooks.Open（FilesFolder＆安培; strFile） 随着WBI
~~＆GT;得到的最后一行从片＃1的文件中
lRowI = .Sheets（1）.Cells.Find（什么：=*，_
后：=表（1）.Range（A1），_。
LOOKAT：= xlPart，_
看着：= xlFormulas，_
SearchOrder：= xlByRows，_
SearchDirection：XL = previous，_
MatchCase：= FALSE）.Row ~~＆GT;从片＃1获得的文件中的最后一列
lColI = .Sheets（1）.Cells.Find（什么：=*，_
后：=表（1）.Range（A1），_。
LOOKAT：= xlPart，_
看着：= xlFormulas，_
SearchOrder：= xlByColumns，_
SearchDirection：XL = previous，_
MatchCase：=假）.Column 与.Sheets（1）
~~＆GT;复制所选范围
.Range（.Cells（1，1），.Cells（lRowI，lColI））。复印 ~~＆GT;粘贴目标文件
wbO.Sheets（工作表Sheet1），范围（A＆放大器; lRowO）。.PasteSpecial xlValues ~~＆GT;获取写入下一个可用行
lRowO = wbO.Sheets（工作表Sheet1）Cells.Find（什么：=*，_
后：= wbO.Sheets（工作表Sheet1），范围（A1），_
LOOKAT：= xlPart，_
看着：= xlFormulas，_
SearchOrder：= xlByRows，_
SearchDirection：XL = previous，_
MatchCase：=假）.Row +1
结束与
结束与
~~＆GT;从中复制后关闭该文件
wbI.Close的SaveChanges：=假
万一
strFile = DIR
循环 MSGBOX完成
结束小组
I am not an expert in AppleScript, so I've ben trying to find an example of AppleScript code that can successfully process a batch of Excel files (each one with a single worksheet), copying the content of each one into a single destination sheet.
This is the pseudo code that I had in mind:
pick source folder with Excel files;
pick destination Excel file;
for each file within the source folder:
copy data from default sheet;
paste data into destination sheet's first unused row
end
This is the code I came up with. It does open correctly each file, but the copy/past operation is just not happening. Any idea how to get it to work?
set main_folder to choose folder with prompt "Please select the folder containing the Excel files:"
set target_excel to choose file with prompt "Please select target Excel file:"
set excel_extension_list to {"xls", "xlsx", "csv"}
tell application "Finder"
set excel_files to (files of main_folder whose name extension is in excel_extension_list) as alias list
end tell
tell application "Microsoft Excel"
open target_excel
repeat with a_file in excel_files
open a_file
activate a_file
tell sheet 1 of workbook a_file
set the_range to value of used range
set number_of_source_rows to count of rows of the_range
end tell
activate target_excel
tell sheet 1 of workbook target_excel
set new_range to value of used range
set number_of_destination_rows to count of rows of new_range
set destination_range to range "A" & (number_of_destination_rows + 1) & ":E" & (number_of_destination_rows + 1 + number_of_source_rows)
set value of destination_range to the_range
close workbook a_file saving no
end tell
end repeat
end tell
解决方案
Tried and Tested in Excel 2011
My Assumptions
- The destination file has a sheet called
Sheet1 - I am retrieving info from the 1st sheet of all files. Change as applicable.
CODE
I have commented the code so you should not have any problem understanding it. :)
Sub Sample()
Dim wbI As Workbook, wbO As Workbook
Dim lRowO As Long
Dim lRowI As Long, lColI As Long
Dim DestFile As Variant
Dim RootFldr As String, FilesFolder As String, strFile As String
'~~> Get the Root Folder
RootFldr = MacScript("return (path to desktop folder) as String")
'~~> Show the Folder Browser to select the folder which has the files
FilesFolder = MacScript("(choose folder with prompt ""Please select the folder which has excel files""" & _
"default location alias """ & RootFldr & """) as string")
'~~> If user doesn't select anything then exit
If FilesFolder = "" Then Exit Sub
'~~> Show the File Select dialog for the output file
DestFile = Application.GetOpenFilename("XLS8,XLS4")
'~~> Open output file
Set wbO = Workbooks.Open(DestFile)
'~~> Get the next available row for writing
lRowO = wbO.Sheets("Sheet1").Cells.Find(What:="*", _
After:=wbO.Sheets("Sheet1").Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas).Row + 1
'~~> Loop through each file in the folder
strFile = Dir(FilesFolder)
Do While Len(strFile) > 0
'~~> Check for the file if it is csv,xls or xlsx
If Right(strFile, 3) = "csv" Or _
Right(strFile, 3) = "xls" Or _
Right(strFile, 4) = "xlsx" Then
'~~> Open the file from the folder
Set wbI = Workbooks.Open(FilesFolder & strFile)
With wbI
'~~> Get the last row in the file from sheet #1
lRowI = .Sheets(1).Cells.Find(What:="*", _
After:=.Sheets(1).Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
'~~> Get the last column in the file from sheet #1
lColI = .Sheets(1).Cells.Find(What:="*", _
After:=.Sheets(1).Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
With .Sheets(1)
'~~> Copy the selected range
.Range(.Cells(1, 1), .Cells(lRowI, lColI)).Copy
'~~> Paste in destination file
wbO.Sheets("Sheet1").Range("A" & lRowO).PasteSpecial xlValues
'~~> Get the next available row for writing
lRowO = wbO.Sheets("Sheet1").Cells.Find(What:="*", _
After:=wbO.Sheets("Sheet1").Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row + 1
End With
End With
'~~> Close the file after copying from it
wbI.Close SaveChanges:=False
End If
strFile = Dir
Loop
MsgBox "Done"
End Sub
这篇关于AppleScript的多个Excel文件合并成一个工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
