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: , , , ,