I apologize if this isn’t the appropriate place to post this but I really wanted to recognize all of those who helped me either directly or indirectly. I had considered uploading this script as just a script, but it is rather specific to just my need and I felt there would be some out there who could benefit more by having it come up in the BBS Search Engine.
For those of you who remember, I needed to format a large block of raw text and spit out a clean and very easy to read Excel file. Jonn, specifically, was of great help because it was his original script that taught me a great deal about layout, labeling and different subroutines. I also scoured the BBS almost daily to try and pick up new techniques of modifying information inside Excel and pooled together several posts to create my final script.
Ok, enough with sounding like I just won a damn Oscar. Here’s the script in its production ready (and probably a smidge messy) form.
Agan, thank you all for your tremendous help.
Dennis
--This script was to trim the junk out of the Home Sales Transactions
--and format them for publication in the East Valley Tribune.
--Feel free to extract anything you find useful and allow the same of anyone
--else who may need a little help.
--Written by Dennis Alaniz
property type_list : {text}
property extension_list : {"txt"}
global whole_kaboodle
global file_path
global target_file
on run
open {choose file}
end run
on open these_items
set an_error to false
set whole_kaboodle to ""
try
repeat with i from 1 to the count of these_items
set this_item to item i of these_items
set item_info to info for this_item
tell application "Finder"
set file_path to ((container of this_item) as text)
end tell
if (folder of item_info = false) and ¬
(alias of item_info = false) and ¬
(file type of item_info is in type_list) or ¬
(name extension of item_info is in extension_list) then ¬
my write_to_file((my process_item(this_item, (name of item_info))), ((((file_path) as text) & text 1 thru -5 of (name of item_info) as text) & " 1.xls"), false)
end repeat
on error error_message number error_number
set an_error to true
set this_error to "Error: " & error_number & ". " & error_message & return
set log_file to ((path to desktop) as text) & "Script Error Log.txt"
my write_error(this_error, log_file, true)
end try
my write_kaboodle(whole_kaboodle, file_path)
if an_error = false then
display dialog "All finished, your Majesty!" buttons {"Viola!"}
else
display dialog "The script encountered an error. Please read the error log now on your desktop" buttons {"Sorry"}
end if
end open
on process_item(this_item, the_name)
set all_text to read (this_item as alias)
set return_text to ""
repeat with this_paragraph in (paragraphs of all_text)
set old_atid to AppleScript's text item delimiters
set AppleScript's text item delimiters to {","}
set the_values to text items of (text of this_paragraph)
set AppleScript's text item delimiters to old_atid
try
if (count of item 8 of the_values) = 5 then
set return_text to return_text & ((item 8 of the_values) as number) & tab & (text 2 thru -2 of (item 6 of the_values)) & tab & (text 2 thru -2 of (item 7 of the_values)) & tab & (item 24 of the_values as number) & return
else if (text 6 thru -5 of item 8 of the_values) = "-" then
set return_text to return_text & ((text 1 thru -6 of (item 8 of the_values)) as number) & tab & (text 2 thru -2 of (item 6 of the_values)) & tab & (text 2 thru -2 of (item 7 of the_values)) & tab & (item 24 of the_values as number) & return
end if
end try
end repeat
return return_text
end process_item
on write_to_file(this_data, target_file, append_data)
set the clipboard to this_data
tell application "Microsoft Word"
try
tell application "System Events"
set visible of process "Microsoft Word" to false
end tell
end try
do Visual Basic " Documents.Add DocumentType:=wdNewBlankDocument"
paste
do Visual Basic " Selection.WholeStory"
do Visual Basic " Selection.Range.Case = wdTitleWord"
copy
close document 1 of window 1 saving no
end tell
tell application "Microsoft Excel"
try
tell Application "System Events"
set visible of process "Microsoft Excel" to false
end tell
end try
Create New Workbook
Paste
Sort Selection Key1 Range "R1C1" Order1 xlAscending Header xlGuess OrderCustom 1 Orientation xlTopToBottom without MatchCase
Select Range "C4"
set NumberFormat of Selection to "$#,##0"
Save ActiveWorkbook In (target_file as string) As xlNormal Password "" WriteReservedPassword "" without ReadOnlyRecommended and CreateBackup
--Select Range "R49C11"
set all_finished to false
set finished to false
set j to 1
repeat until all_finished is equal to true
set zip_format to Value of Cell ("R" & j & "C1")
set zip_cell to "R" & j & "C1" as string
set new_cell to "R" & j & "C2" as string
set new_row to "R" & j as string
repeat until finished is equal to true
set test_zip to Value of Cell ("R" & (j + 1) & "C1")
if zip_format ? test_zip then
set finished to true
end if
set j to j + 1
end repeat
try
if Formula of Cell test_zip = 0 then
set all_finished to true
else
set finished to false
end if
on error
set all_finished to true
end try
Select Range zip_cell
CopyObject Selection
Select Range new_row
set CutCopyMode to false
Insert Selection Shift xlDown
Select Range new_cell
Paste
set Bold of Font of Selection to true
set j to j + 1
end repeat
Select Range "R1:R65536"
CopyObject Selection
set whole_kaboodle to whole_kaboodle & (the clipboard)
set CutCopyMode to true
-- Select Range "C1"
-- Delete Selection Shift xlToLeft
-- Select Range "C2"
-- Delete Selection Shift xlToLeft
Close ActiveWorkbook saving Yes
end tell
return whole_kaboodle
end write_to_file
on write_kaboodle(final_text, final_location)
set the clipboard to whole_kaboodle
tell application "Microsoft Excel"
try
tell Application "System Events"
set visible of process "Microsoft Excel" to false
end tell
end try
Create New Workbook
Paste
Save ActiveWorkbook In ((final_location as string) & "Whole Kitten Kaboodle.xls") As xlNormal Password "" WriteReservedPassword "" without ReadOnlyRecommended and CreateBackup
-- Select Range "C1"
-- Delete Selection Shift xlToLeft
-- Select Range "C2"
-- Delete Selection Shift xlToLeft
Close ActiveWorkbook saving Yes
end tell
end write_kaboodle
on write_error(this_data, target_file, append_data)
try
set target_file to target_file as text
set open_target_file to open for access file target_file with write permission
if append_data = false then set eof of the open_target_file to 0
write this_data to the open_target_file starting at eof
close access the open_target_file
return true
on error
try
close access file target_file
end try
return false
end try
end write_error