This stinks. Really. Say what you will about Microsoft, they know how to create and maintain a programming language. Whoever thought that s/vbscript/Applescript/gi would be a fair substitution in Office for Mac (even ignoring that it breaks legacy macros) was not a programmer.

Here's what my "final" code looks like. It's a mess and needs cleaning. But something that's INSANELY EASY in vbscript takes the below to do something similar. I couldn't even get it to know what cell format selections I'd made in Excel and ended up with an ugly kludge for zip codes and a hellah ugly kludge for phone numbers which were coming out in a format like "9.195553662E+9". What a mess.

I could clean it up some, taking out lesser kludges that aren't working, but I think you get the point better as is. Not intuitive. Not fully-featured. The stuff you have to roll yourself in Applescript (afaict) is insane and inane. Boy. Talk about not feeling fully featured.

Now I have to let it wade through over 3000 lines of Excel tonight...

   1 on replaceText(find, replace, subject)
   2         set prevTIDs to text item delimiters of AppleScript
   3         set text item delimiters of AppleScript to find
   4         set subject to text items of subject
   5         
   6         set text item delimiters of AppleScript to replace
   7         set subject to "" & subject
   8         set text item delimiters of AppleScript to prevTIDs
   9         
  10         return subject
  11 end replaceText
  12 
  13 tell application "Microsoft Excel"
  14         
  15         -- from http://lists.apple.com/archives/applescript-users/2009/Jun/msg00331.html
  16         set hc to find (row 1 of used range of active sheet) what "PHONE"
  17         set n to first column index of hc
  18         set f to first column index of (column 1 of used range of active sheet)
  19         set col to column (n - f + 1) of used range of active sheet
  20         set number format of col to "0"
  21         -- doesn't seem to work
  22         
  23         
  24         --      say "Saving with every row"
  25         
  26         set lastCol to 10 -- yes, I'm cheating.  
  27         -- you have to know the last col and row for this to work
  28         set lastRow to 100 -- I suppose I could add dialog boxes to ask max nums
  29         set c to 1
  30         set r to 90 -- that's the first row that's got info it in; skip the first row's col headers
  31         set strCmd to ""
  32         
  33         try
  34                 repeat until r = lastRow + 1
  35                         set strCmd to strCmd & "INSERT INTO restaurants (phone, company, address, ยฌ
  36                         city, state, zip, owner, gm, agent, chef)"
  37                         set strCmd to strCmd & " VALUES ("
  38                         set daComma to ""
  39                         
  40                         --'','','',42,,'','','')"
  41                         
  42                         set c to 1
  43                         
  44                         repeat until c = lastCol + 1
  45                                 --display dialog "Row: " & r & " Col: " & c
  46                                 
  47                                 set theValue to get value of cell c of row r of active sheet
  48                                 
  49                                 if (c = 1) then -- then we have a phone number
  50                                         -- Excel via Applescript is borking numbers like mad.
  51                                         -- There are Right Ways to handle this.  They
  52                                         -- don't see to be working well (see above).
  53                                         -- we're going to cheat like mad instead.
  54                                         -- TODO: KLUDGE-A-RIFFIC
  55                                         if (text 11 of (theValue as string) = "E") then
  56                                                 set strOut to text 1 of (theValue as string) ยฌ
  57                                                         & text 3 thru 10 of (theValue as string) & "0"
  58                                         else
  59                                                 set strOut to text 1 of (theValue as string) ยฌ
  60                                                         & text 3 thru 11 of (theValue as string)
  61                                         end if
  62                                         
  63                                         set strCmd to strCmd & daComma & strOut
  64                                         set daComma to ","
  65                                         
  66                                 else if (c = 6) then -- then we have a zip code
  67                                         -- can't use this branch for phone numbers
  68                                         -- as they evaluate to a number larger than
  69                                         -- our largest possible integer
  70                                         if (count of characters of (theValue as string)) = 5 then
  71                                                 set theValue to theValue as integer
  72                                         end if
  73                                         set strCmd to strCmd & daComma & (theValue as string)
  74                                         set daComma to ","
  75                                 else if (c = 5) then
  76                                         set strCmd to strCmd & daComma & "42"
  77                                         -- hard coding SC to save a lookup
  78                                         set daComma to ","
  79                                 else -- else it's a string
  80                                         if theValue = "" then
  81                                                 set theValue to "empty"
  82                                         end if
  83                                         set strCmd to strCmd & daComma & "'" & (theValue as string) & "'"
  84                                         set daComma to ","
  85                                 end if
  86                                 
  87                                 set c to (c + 1)
  88                         end repeat
  89                         set strCmd to strCmd & ")" & return -- return adds a MacOS newline
  90                         set r to (r + 1)
  91                         if r mod 50 = 0 then
  92                                 -- beep -- add some sort of feedback.  
  93                                 say r -- add cooler feedback.  This talks.
  94                                 -- Btw, Applescript for this in Excel is insanely slooooow 
  95                                 -- vs. my experience with vbs.  It takes several seconds to 
  96                                 -- do 10 rows of ten columns of customer address and phone, etc.
  97                                 -- information.
  98                         end if
  99                 end repeat
 100                 
 101                 set theFilePath to (path to desktop as string) & "test11.txt" as string
 102                 set theFileReference to open for access theFilePath with write permission
 103                 write strCmd to theFileReference
 104                 close access theFileReference
 105                 
 106         on error errMessage number errNum from errSource partial result errList ยฌ
 107                 to class_constant -- various variables store information about the error
 108                 set bigmessage to "The error is: " & errMessage & return ยฌ
 109                         & "The number is: " & errNum & return & "The source is: " & errSource & return ยฌ
 110                         & "r&c: " & r & "&" & c
 111                 error bigmessage -- error statement displays dialog box to user
 112                 say "error in the loop"
 113                 close access theFileReference
 114                 -- this close seems important.  I can't tell how to get 
 115                 -- back the handle to close this if I had an error cause it
 116                 -- to stay open from a past script invocation.
 117         end try
 118         
 119         say "Done!"
 120         --      display dialog "Done with write to file"
 121 end tell


(Edit: See this on tells. I had to tweak the above a good bit for it to work well, but it still serves as a pretty decent low-investment Applescript (vs. VBA) code snippet. It's an order of magnitude more complicated.)

Labels: , , , ,