I’m back with Excel woes, though different woes than I’d anticipated. My problem now is my inability to tell Excel to use Commas as a delimiter for the columns.
For those who are curious, the first thread is here (http://bbs.applescript.net/viewtopic.php?t=5638&highlight=)
I’ve tried to tackle this two different ways. Using a modified version of the final script from the thread posted above, I opted to have it paste the info into Excel instead of writing it to a file in the third block. That code is quite simple (paraphrasing: Tell “Excel”/New Worksheet/Paste/End tell). The only problem is, if it’s a fresh launch of Excel, it won’t use those comma’s to separate the info into its own column and everything falls into the first column.
The second approach is to save the files to text (as the original script was written) and to open the file from that. However, the problem there is my lack of ability to tell Excel to open a text file with the proper formatting. I hit Record and opened a file to format it the way I want, but I can’t make the path to the file a variable for some reason (and it’s infuriating!). Below is the code AppleScript spits back at me.
tell application "Microsoft Excel"
activate
OpenText "Vault:Users:filmsmith:Desktop:macros:Testing!:temporary files:AHWATUKEE temp.txt" Origin xlMacintosh StartAtRow 1 DataType xlDelimited TextQualifier xlDoubleQuote FieldInfo {{1.0, 1.0}, {2.0, 1.0}, {3.0, 1.0}, {4.0, 1.0}} with CommaDelimiter without TreatConsecutiveDelimitersAsOne, TabDelimiter, SemicolonDelimiter, SpaceDelimiter and Other
end tell
The variation I tried (a simplified version of the longer script hacked down for testing) on that to get Excel to open an undefined file is as such.
on run
open {choose file}
end run
on open these_items
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 (path 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 ¬
tell application "Microsoft Excel"
OpenText this_item Origin xlMacintosh StartAtRow 1 DataType xlDelimited TextQualifier xlDoubleQuote FieldInfo {{1.0, 9.0}, {2.0, 9.0}, {3.0, 9.0}, {4.0, 9.0}, {5.0, 1.0}, {6.0, 1.0}, {7.0, 1.0}, {8.0, 9.0}, {9.0, 9.0}, {10.0, 9.0}, {11.0, 9.0}, {12.0, 9.0}, {13.0, 9.0}, {14.0, 9.0}, {15.0, 9.0}, {16.0, 9.0}, {17.0, 9.0}, {18.0, 9.0}, {19.0, 9.0}, {20.0, 9.0}, {21.0, 9.0}, {22.0, 9.0}, {23.0, 1.0}, {24.0, 9.0}, {25.0, 9.0}, {26.0, 9.0}, {27.0, 9.0}, {28.0, 9.0}, {29.0, 9.0}, {30.0, 9.0}, {31.0, 9.0}, {32.0, 9.0}, {33.0, 9.0}, {34.0, 9.0}} with CommaDelimiter without TreatConsecutiveDelimitersAsOne, TabDelimiter, SemicolonDelimiter, SpaceDelimiter and Other
end tell
end repeat
on error error_message number error_number
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
end open
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
If someone would be so good as to help me get this text to open properly in Excel, I would greatly appreciate it.
Thanks
fs[/url]