Re: A Faster Script
Re: A Faster Script
- Subject: Re: A Faster Script
- From: nino <email@hidden>
- Date: Sat, 10 Apr 2004 10:32:28 +0100
>
I work in a chemistry lab at a university, and one of the things we
>
have to do is copy and past thousands of points into Microsoft Excel.
>
It's really very boring. So I tried to automate it with applescript.
>
My script works, but the problem is at the rate it is copying the data
>
into excel it will take some 5 to 6 hours to finish, which is far more
>
time than it takes me to do it myself! (And it hangs far before even
>
half an hour has past.) I'm certain there is a faster way to do this,
>
but I don't see it.
>
>
The data points are spread across around several tab delimited files.
>
Each files goes into two separate columns, and the columns are labeled
>
with a number and either an 'x' or a 'y'.
I've assumed your files are tab return separated two columns data
Store this VBA macro in an Excel file called exceltest and leave it open.
If you decide for a different name reflect that on the Evaluate script line.
Sub getnextfile(y, rif, fold)
Workbooks.OpenText FileName:=(fold & rif)
Workbooks(2).Activate
Workbooks(rif).Sheets(1).UsedRange.Copy _
Destination:=Worksheets(1).Cells(2, y * 2 - 1)
Workbooks(rif).Close
Worksheets(1).Cells(1, y * 2 - 1).Value = ("" & y) & "x"
Worksheets(1).Cells(1, y * 2).Value = ("" & y) & "y"
End Sub
The script can be streamlined as follow. I've removed buttons control since
pressing a Cancel button in dialogs will stop the script anyway.
set source_folder to (choose folder with prompt "Select folder with files to
be imported") as string --NO BREAK
set folder_files to list folder source_folder
--get the prefix of the desired files
set file_prefix to text returned of (display dialog "Enter the prefix of the
files to be imported into Excel." default answer "UN") --NO BREAK
--identify all of the desired files
set filesToExamine to {}
repeat with currentFile in folder_files
if currentFile begins with file_prefix then
set the end of filesToExamine to the contents of currentFile
end if
end repeat
set files_count to the count of filesToExamine
tell application "Microsoft Excel"
Activate
Create New Workbook
repeat with i from 1 to files_count
Evaluate "exceltest!getnextfile(" & i & ",\"" & item i of
filesToExamine & "\",\"" & source_folder & "\")" --NO BREAK
end repeat
end tell
Your import should take less then 5 minutes. This is an AS VBA cooperation
effort tested on OS 9 but shouldn't be so different on OSX.
good luck
nino
_______________________________________________
applescript-users mailing list | email@hidden
Help/Unsubscribe/Archives:
http://www.lists.apple.com/mailman/listinfo/applescript-users
Do not post admin requests to the list. They will be ignored.