So if you want to automate Excel 2008 with Applescript, you have to minimize your tells. There are some strange scoping interferences. For instance, this code breaks.
tell application "Microsoft Excel"
 set intOffset to (offset of "E" in "ABCDEFG")
end tell
Try to compile/run and you get "Syntax Error -- Can't get "E" in "ABCDEFG". Access not allowed." Wack. Fun times with text manipulation and Excel automation, eh?

So code that borks like that needs to be pulled out of the tell block. You maintain values that were pulled out of Excel references, so it's not a big deal other than the inherent wackiness. So in my code now, the tell block to Excel is very small.
-- it seems like you could do all this work within tell
-- tags, but that's not the case.  For instance, the
-- following code WON'T work within a tell to MS Excel:
--  set intOffset to (offset of "E" in "ABCDEFG")
-- I'm not sure what the problem is, or if it's keyword
-- overload or something, but reducing the tell to a
-- minimum seems to be the best move.
tell application "Microsoft Excel"
 set theValue to get value of cell c of row r of active sheet
end tell
theValue sticks around afterwards and my stock/simple string manipulation calls work again, so no problems. Not sure if doing the tell inside of instead of outside of my logic's loops causes some big bridge between Excel and Applescript to be built, destroyed, and built again, but it's already so freakin' slow it's not like it really matters. There's fast as in "give it to me now" and 'fast' as in "give it to me in a few hours after I go for a run, to the grocery store, and do the dishes". This is in the second, and is nearly already in the "go to sleep on it and hope it's done in the morning" category that comes afterwards. I hate this.

(I ran into this "sleep on it" speed at a shop that used Crystal Reports against very large databases and it drove me MAD. It was bad enough I took the time to figure out a way to have Crystal use SQL directly against the DB, moving the logic where it's supposed to go -- the database -- and left Crystal just with formatting. Thank heavens. Even if I personally only used it a few times, give me a break, folk. We went from clients waiting all night -- even DAYS at times -- for reports to them waiting a few minutes at worst. It's not 1980 any more! Your workstations are fast if you're not gaming, okay? (Not 1980, that is, unless you're using Applescript to automate Excel 2008, apparently.))

FUN IN KLUDGEVILLE!!! I'll probably repost my "practical example" as it is now in a bit. It's not clean at all. Drives me a little crazy.

Labels: , , ,