Put the knife down and take a green herb, dude. (c) Ruffin Bailey 2001-2021

<?php
if (isset($_POST['test'])) {
foreach($_POST['test'] as $key => $x) {
echo $key . "::" . $x . "<br>\n";
}
} else {
echo "Enter values.<br>\n";
}
?>

<form method="POST">
<input type="text" value="dude" name="test[1]"><br>
<input type="text" value="dude" name="test[3]"><br>
<input type="text" value="dude" name="test[5]"><br>
<input type="text" value="dude" name="test[8]"><br>
<input type="text" value="dude" name="test[9]"><br>
<input type="text" value="dude" name="test[2]"><br>

<input type="submit"><br>
</form>

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

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

I have Excel 2008 on my Mac. About the only complicated thing I ever want to do with Excel files is to manipulate them line by line and spit the output into a cut and pasteable source for my clipboard. I have a vbscript version for Windows, but when I'm in 2008 land, I'm sunk. No vbs. Only Applescript. Stinks.

Frustrated with having to wait until I'm back on my Windows box, not yet willing to shell out more cash and buy Office 2011 (and fed up enough with Office 2004 that I threw it away the second 2008 hit my box), I'm finally going to figure out how to port the code to Applescript.

So first let's essentially run Hello, World, and put a value into a specific Excel cell.

tell application "Microsoft Excel" tell worksheet "Sheet1" of active workbook set value of cell "A1" to 35 end tell end tell

Going to Sheet2 is exactly like you'd expect.

tell application "Microsoft Excel" tell worksheet "Sheet2" of active workbook set value of cell "A1" to 35 end tell end tell

So once we start playing around, we'll probably want some way to keep track of what we've found. In my case, I want to turn line after line from an Excel file to SQL statements. Instead of a giant dialog box, I'll just slap that into a file.

set theText to "some text" set theFilePath to (path to desktop as string) & "test.txt" as string set theFileReference to open for access theFilePath with write permission write theText to theFileReference close access theFileReference

So now all we've got to do is to throw the contents of each cell into theText. So now we need to find out how to iterate through all Excel cells?

This took a little longer that I would have expected. The key is this line:
get value of cell c of row r of active sheet

That takes the place of .Cells(x,y)

So here's the end result.

tell application "Microsoft Excel" say "Saving at the end" set lastCol to 10 -- yes, I'm cheating. -- you have to know the last col and row for this to work set lastRow to 1000 -- I suppose I could add dialog boxes to ask max nums set c to 1 set r to 2 -- that's the first row that's got info it in; skip the first row's col headers set strCells to "" try repeat until r = lastRow set c to 2 set strCells to strCells & (r as text) & " -- " repeat until c = lastCol --display dialog "Row: " & r & " Col: " & c set theValue to get value of cell c of row r of active sheet set strCells to strCells & theValue & " :: " as text -- just use & to append; cast to text with "as text" set c to (c + 1) end repeat set strCells to strCells & return -- return adds a MacOS newline set r to (r + 1) if r mod 20 = 0 then -- beep -- add some sort of feedback. say r -- add cooler feedback. This talks. -- Btw, Applescript for this in Excel is insanely slooooow -- vs. my experience with vbs. It takes several seconds to -- do 10 rows of ten columns of customer address and phone, etc. -- information. end if end repeat close access theFileReference -- write strCells to theFileReference on error say "error in the loop" end try try set theFilePath to (path to desktop as string) & "test2.txt" as string set theFileReference to open for access theFilePath with write permission write strCells to theFileReference close access theFileReference on error say "Error in writing to file" close access theFileReference -- this close seems important. I can't tell how to get -- back the handle to close this if I had an error cause it -- to stay open from a past script invocation. end try say "Done!" end tell

This is PAINFULLY slow, however. I'm used to it screaming. I tried rearranging the code to write out the jive to the file with each line, and that's not noticeably quicker.

tell application "Microsoft Excel" say "Saving with every row" set lastCol to 10 -- yes, I'm cheating. -- you have to know the last col and row for this to work set lastRow to 100 -- I suppose I could add dialog boxes to ask max nums set c to 1 set r to 2 -- that's the first row that's got info it in; skip the first row's col headers set strCells to "" set theFilePath to (path to desktop as string) & "test5.txt" as string set theFileReference to open for access theFilePath with write permission try repeat until r = lastRow + 1 set c to 2 repeat until c = lastCol + 1 --display dialog "Row: " & r & " Col: " & c set theValue to get value of cell c of row r of active sheet set strCells to strCells & theValue & " :: " as text -- just use & to append; cast to text with "as text" set c to (c + 1) end repeat set strCells to (r as text) & " -- " & strCells & return -- return adds a MacOS newline write strCells to theFileReference set strCells to "" set r to (r + 1) if r mod 50 = 0 then -- beep -- add some sort of feedback. say r -- add cooler feedback. This talks. -- Btw, Applescript for this in Excel is insanely slooooow -- vs. my experience with vbs. It takes several seconds to -- do 10 rows of ten columns of customer address and phone, etc. -- information. end if end repeat close access theFileReference -- write strCells to theFileReference on error say "error in the loop" close access theFileReference -- this close seems important. I can't tell how to get -- back the handle to close this if I had an error cause it -- to stay open from a past script invocation. end try say "Done!" display dialog "Done with write to file" end tell

So it works, in a sort of brute force, inefficient way. The biggest issue I've had is if it's cancelled. Though it says "error in the loops", the file ref seems not to be closed, and then the file I'm writing to remains open with no opportunity to close it. Once the script's done, it just remains open. I tried deleting on the command line with Terminal and the file was still "there" as far as the script was concerned. Perhaps when I reboot it'll be gone, but I can't Google a way to close it. Most answers seem to talk about finding the app that opened it, but when you're the app that did it, I'm not sure what's up.

In any event, it does marginally spider through cell by cell and print things out to a text file, which is what I wanted. You just have to be really patient. No wonder they added

Labels: applescript, code, excel, noteToSelf, problem solved

Christian Sandvig: Is Google's Spy-Fi About Privacy, or Something More?:

Open your smartphone and you may be pleased to find that its mapping application now fixes your location quite accurately. Just a short year or two ago the GPS signals used by these devices were painfully inaccurate -- particularly in the canyons of cities where we tend to want them most. Then came the remarkable insight (from companies like Loki) that when people buy Wi-Fi access points at Radio Shack and install them in their houses, they don't move them around very often, or turn them off. Each Wi-Fi access point, it turns out, has a unique number that it broadcasts continuously in something called a beacon. The beacon, aptly named, flashes out with the regularity of a lighthouse.

Google wants to trawl the streets for Wi-Fi signals because if it can find these beacons and organize them, then the next time you drive by that same beacon it can guess where you are...

1 <?xml version="1.0"?>
2 <?xml-stylesheet href="chrome://global/skin/" type"text/css" ?>
3 <!DOCTYPE page SYSTEM "chrome://zoteroplay/locale/overlay.dtd">
4 <page id="zoteroplaySidebar" title="&zoteroplaySidebar.label;"
5 xmlns="http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul">
6
7
8
9 <script src="ff-sidebar.js"/>
10 <script>
11 <![CDATA[
12 function hello() {
13 alert('Hello from ff-sidebar.xul!!');
14 }
15 function OnTreeDoubleClick(event) {
16 alert('doubleclick');
17 var objTree = document.getElementById('myTree');
18 var iIndex =
19 objTree.treeBoxObject.getRowAt(event.clientX,event.clientY);
20 alert(iIndex);
21 }
22
23 function addSource(bIsNewSource) {
24 // note that this is hard-coded to grab from the tree we make,
25 // using XUL, not javascript, below. The only reason I'm doing
26 // this (instead of having it passed in as a function
27 // parameter) is so that we don't have to navigate some DOM
28 // from another file to call this function. Just call from
29 // overlay.js or whatever and forget about sidebar refs.
30 var rootChild = null;
31 if (bIsNewSource) {
32 rootChild = document.getElementById("newRoot");
33 } else {
34 rootChild = document.getElementById("dupeRoot");
35 }
36
37
38
39 //========================================
40 // Create the parent row.
41 //========================================
42 // Now we're going to create a new TreeItem, which will also
43 // be a container for nested items.
44 // itemSourceHeader will have two items, one the field we're
45 // using to distinguish it (like Title, if it's got one) and
46 // the second is the value ("Moby-Dick; or, The Whale")
47
48 var itemSourceParent = document.createElement("treeitem");
49 itemSourceParent.setAttribute("container","true");
50 // create a new row with two cells for the new
51 // item, which will be attached to the rootChild.
52 var rowSourceHeader = document.createElement("treerow");
53
54 var cellField = document.createElement("treecell");
55 cellField.setAttribute("label","Title:");
56
57 var cellFieldValue = document.createElement("treecell");
58 cellFieldValue
59 .setAttribute("label","Moby-Dick; or, The Whale");
60
61 rowSourceHeader.appendChild(cellField);
62 rowSourceHeader.appendChild(cellFieldValue);
63 // attach the header values for this item (now in a treerow) to
64 // the item that'll hold it. That is, the source (like a
65 // book) parent has some title information to display.
66 itemSourceParent.appendChild(rowSourceHeader);
67
68
69
70 //========================================
71 // Now we create the nested row.
72 //========================================
73 var parentNested = document.createElement("treechildren");
74
75 // soon we'll put in actual value from an array
76 // rather than this dummy loop; this is just to
77 // show how to have more than one "child".
78 for (var j=1;j<=10;j++) {
79 var itemNested = document.createElement("treeitem");
80 var rowNested = document.createElement("treerow");
81 var cellNest1 = document.createElement("treecell");
82 cellNest1.setAttribute("label","Author:");
83
84 var cellNest2 = document.createElement("treecell");
85 cellNest2.setAttribute("label","Herman Melville");
86
87 rowNested.appendChild(cellNest1);
88 rowNested.appendChild(cellNest2);
89
90
91 itemNested.appendChild(rowNested);
92 parentNested.appendChild(itemNested);
93 }
94 // attach the detailed source information to
95 // the parent item.
96 itemSourceParent.appendChild(parentNested);
97
98
99 //========================================
100 // Now we add the parent item of all the
101 // source's detailed information to the
102 // root child of the tree.
103 //========================================
104 rootChild.appendChild(itemSourceParent);
105 }
106
107
108 ]]>
109 </script>
110
111
112
113 <vbox flex="1">
114 <label value="&zoteroplaySidebar.label;" />
115 
116
117
118 <tree ondblclick="OnTreeDoubleClick(event);" id="myTree" flex="1">
119 <treecols>
120 <treecol id="firstname" label="Field" flex="5"
121 primary="true"/>
122 <treecol id="lastname" label="Field Value" flex="5"/>
123 </treecols>
124
125 <treechildren>
126 <treeitem container="true" open="true">
127 <treerow>
128 <treecell label="Possible New Sources"/>
129 </treerow>
130 <treechildren id="newRoot">
131 </treechildren>
132
133 </treeitem>
134 <treeitem container="true" open="true">
135 <treerow>
136 <treecell
137 label="Likely Duplicate Sources"/>
138 </treerow>
139 <treechildren id="dupeRoot">
140 <treeitem>
141 <treerow>
142 <treecell
143 label="Title:"/>
144 <treecell
145 label="Already Got It"/>
146 </treerow>
147 </treeitem>
148
149 </treechildren>
150
151 </treeitem>
152 </treechildren>
153 </tree>
154 </vbox>
155 </page>
156

1 var mainWindow = null;
2
3 function startup() {
4 mainWindow = window.QueryInterface(Components.interfaces.nsIInterfaceRequestor)
5 .getInterface(Components.interfaces.nsIWebNavigation)
6 .QueryInterface(Components.interfaces.nsIDocShellTreeItem)
7 .rootTreeItem
8 .QueryInterface(Components.interfaces.nsIInterfaceRequestor)
9 .getInterface(Components.interfaces.nsIDOMWindow);
10
11 // this spacebar needs a lot of space to display the info
12 // we're going to put there. Give it at least 400 pixels out of the box.
13 // TODO: I'm setting a check to see if it's already 400, but it seems there's
14 // an absolute max width for sidebars in Firefox by default which is less than
15 // (or about) 400.
16 if (mainWindow.document.getElementById("sidebar-box").width < 400) {
17 mainWindow.document.getElementById("sidebar-box").width = 400;
18 }
19 // Sidebar is loaded and mainwindow is ready
20 }
21
22 function shutdown() {
23 // Sidebar is unloading
24 }
25
26 window.addEventListener("load", startup, false);
27 window.addEventListener("unload", shutdown, false);
28

title: Put the knife down and take a green herb, dude.	descrip: One feller's views on the state of everyday computer science & its application (and now, OTHER STUFF) who isn't rich enough to shell out for www.myfreakinfirst-andlast-name.com Using 89% of the same design the blog had in 2001.
FOR ENTERTAINMENT PURPOSES ONLY!!! Back-up your data and, when you bike, always wear white. As an Amazon Associate, I earn from qualifying purchases. Affiliate links in green.
x MarkUpDown is the best Markdown editor for professionals on Windows 10. It includes two-pane live preview, in-app uploads to imgur for image hosting, and MultiMarkdown table support. Features you won't find anywhere else include... MarkUpDown Multiline Table & Bootstrap Grid support. Beautiful Easy Actions that keep the Markdown flowing. HTML paste to paste HTML source into your documents. You've wasted more than $15 of your time looking for a great Markdown editor. Stop looking. MarkUpDown is the app you're looking for. Learn more or head over to the 'Store now!

Friday, April 29, 2011
php with html forms: Assign to a specific array member Kinda a neat trick that works with php (in my limited, MAMP-only testing): <?php if (isset($_POST['test'])) { foreach($_POST['test'] as $key => $x) { echo $key . "::" . $x . "<br>\n"; } } else { echo "Enter values.<br>\n"; } ?> <form method="POST"> <input type="text" value="dude" name="test[1]"><br> <input type="text" value="dude" name="test[3]"><br> <input type="text" value="dude" name="test[5]"><br> <input type="text" value="dude" name="test[8]"><br> <input type="text" value="dude" name="test[9]"><br> <input type="text" value="dude" name="test[2]"><br> <input type="submit"><br> </form> Output is... 1::dude 3::dude 5::dude 8::dude 9::dude 2::dude That gives you a lot more control than just having `test[]` for your form element names. So in one page, I want to know the id numbers of the elements that are selected. Now I can put that information in the index of the array and pass a value (in this case, a user-inputed ordinal reflecting the display order of the elements) as well. Labels: code, php, problem solved posted by ruffin at 4/29/2011 09:21:00 AM

Wednesday, April 20, 2011
Global Variables Are Bad Global Variables Are Bad: Why Global Variables Should Be Avoided When Unnecessary (with a list that follows) Where were you guys in the sixties when I needed you? (Harry Chapin) posted by ruffin at 4/20/2011 04:15:00 PM

Minimize your tells! More Applescript+Excel 2008 fun 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: applescript, code, excel, problem solved posted by ruffin at 4/20/2011 02:00:00 PM

Tuesday, April 19, 2011
What "real" Applescript code looks like 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: applescript, code, excel, problem solved, vba posted by ruffin at 4/19/2011 04:07:00 PM

Applescripting cell by cell in Excel 2008 I have Excel 2008 on my Mac. About the only complicated thing I ever want to do with Excel files is to manipulate them line by line and spit the output into a cut and pasteable source for my clipboard. I have a vbscript version for Windows, but when I'm in 2008 land, I'm sunk. No vbs. Only Applescript. Stinks. Frustrated with having to wait until I'm back on my Windows box, not yet willing to shell out more cash and buy Office 2011 (and fed up enough with Office 2004 that I threw it away the second 2008 hit my box), I'm finally going to figure out how to port the code to Applescript. So first let's essentially run Hello, World, and put a value into a specific Excel cell. `tell application "Microsoft Excel" tell worksheet "Sheet1" of active workbook set value of cell "A1" to 35 end tell end tell` Going to Sheet2 is exactly like you'd expect. `tell application "Microsoft Excel" tell worksheet "Sheet2" of active workbook set value of cell "A1" to 35 end tell end tell` So once we start playing around, we'll probably want some way to keep track of what we've found. In my case, I want to turn line after line from an Excel file to SQL statements. Instead of a giant dialog box, I'll just slap that into a file. `set theText to "some text" set theFilePath to (path to desktop as string) & "test.txt" as string set theFileReference to open for access theFilePath with write permission write theText to theFileReference close access theFileReference` So now all we've got to do is to throw the contents of each cell into theText. So now we need to find out how to iterate through all Excel cells? This took a little longer that I would have expected. The key is this line: `get value of cell c of row r of active sheet` That takes the place of .Cells(x,y) So here's the end result. tell application "Microsoft Excel" say "Saving at the end" set lastCol to 10 -- yes, I'm cheating. -- you have to know the last col and row for this to work set lastRow to 1000 -- I suppose I could add dialog boxes to ask max nums set c to 1 set r to 2 -- that's the first row that's got info it in; skip the first row's col headers set strCells to "" try repeat until r = lastRow set c to 2 set strCells to strCells & (r as text) & " -- " repeat until c = lastCol --display dialog "Row: " & r & " Col: " & c set theValue to get value of cell c of row r of active sheet set strCells to strCells & theValue & " :: " as text -- just use & to append; cast to text with "as text" set c to (c + 1) end repeat set strCells to strCells & return -- return adds a MacOS newline set r to (r + 1) if r mod 20 = 0 then -- beep -- add some sort of feedback. say r -- add cooler feedback. This talks. -- Btw, Applescript for this in Excel is insanely slooooow -- vs. my experience with vbs. It takes several seconds to -- do 10 rows of ten columns of customer address and phone, etc. -- information. end if end repeat close access theFileReference -- write strCells to theFileReference on error say "error in the loop" end try try set theFilePath to (path to desktop as string) & "test2.txt" as string set theFileReference to open for access theFilePath with write permission write strCells to theFileReference close access theFileReference on error say "Error in writing to file" close access theFileReference -- this close seems important. I can't tell how to get -- back the handle to close this if I had an error cause it -- to stay open from a past script invocation. end try say "Done!" end tell This is PAINFULLY slow, however. I'm used to it screaming. I tried rearranging the code to write out the jive to the file with each line, and that's not noticeably quicker. tell application "Microsoft Excel" say "Saving with every row" set lastCol to 10 -- yes, I'm cheating. -- you have to know the last col and row for this to work set lastRow to 100 -- I suppose I could add dialog boxes to ask max nums set c to 1 set r to 2 -- that's the first row that's got info it in; skip the first row's col headers set strCells to "" set theFilePath to (path to desktop as string) & "test5.txt" as string set theFileReference to open for access theFilePath with write permission try repeat until r = lastRow + 1 set c to 2 repeat until c = lastCol + 1 --display dialog "Row: " & r & " Col: " & c set theValue to get value of cell c of row r of active sheet set strCells to strCells & theValue & " :: " as text -- just use & to append; cast to text with "as text" set c to (c + 1) end repeat set strCells to (r as text) & " -- " & strCells & return -- return adds a MacOS newline write strCells to theFileReference set strCells to "" set r to (r + 1) if r mod 50 = 0 then -- beep -- add some sort of feedback. say r -- add cooler feedback. This talks. -- Btw, Applescript for this in Excel is insanely slooooow -- vs. my experience with vbs. It takes several seconds to -- do 10 rows of ten columns of customer address and phone, etc. -- information. end if end repeat close access theFileReference -- write strCells to theFileReference on error say "error in the loop" close access theFileReference -- this close seems important. I can't tell how to get -- back the handle to close this if I had an error cause it -- to stay open from a past script invocation. end try say "Done!" display dialog "Done with write to file" end tell So it works, in a sort of brute force, inefficient way. The biggest issue I've had is if it's cancelled. Though it says "error in the loops", the file ref seems not to be closed, and then the file I'm writing to remains open with no opportunity to close it. Once the script's done, it just remains open. I tried deleting on the command line with Terminal and the file was still "there" as far as the script was concerned. Perhaps when I reboot it'll be gone, but I can't Google a way to close it. Most answers seem to talk about finding the app that opened it, but when you're the app that did it, I'm not sure what's up. In any event, it does marginally spider through cell by cell and print things out to a text file, which is what I wanted. You just have to be really patient. No wonder they added Labels: applescript, code, excel, noteToSelf, problem solved posted by ruffin at 4/19/2011 02:16:00 PM

Thursday, April 14, 2011
Note to self: Professor on Google WiFi sniffing Christian Sandvig: Is Google's Spy-Fi About Privacy, or Something More?: Open your smartphone and you may be pleased to find that its mapping application now fixes your location quite accurately. Just a short year or two ago the GPS signals used by these devices were painfully inaccurate -- particularly in the canyons of cities where we tend to want them most. Then came the remarkable insight (from companies like Loki) that when people buy Wi-Fi access points at Radio Shack and install them in their houses, they don't move them around very often, or turn them off. Each Wi-Fi access point, it turns out, has a unique number that it broadcasts continuously in something called a beacon. The beacon, aptly named, flashes out with the regularity of a lighthouse. Google wants to trawl the streets for Wi-Fi signals because if it can find these beacons and organize them, then the next time you drive by that same beacon it can guess where you are... posted by ruffin at 4/14/2011 09:47:00 AM

Friday, April 08, 2011
My inbox is 1337, yo. Oh yeah. Labels: Randmo posted by ruffin at 4/08/2011 12:28:00 AM

Thursday, April 07, 2011
Firefox Extensions: But my description isn't "description"! If you use the Add-On Manager to create the foundation to your Firefox extension, you might see that your description is literally "description" even though you've got a different value in <em:description> in your install.rdf file. You've probably got this line in overlay.properties: `extensions.zoteroplay@genreacrossborders.org.description=description` (where `zoteroplay@genreacrossborders.org` will be the id of your own extension) That'll do it. /sigh The property "covers up" your longer description in install.rdf. Erase that line and your other description will show up. Labels: extension, firefox, problem solved, XUL posted by ruffin at 4/07/2011 07:13:00 PM

Marginally more nested programmatically created XUL tree in a Firefox extension More fun code! Note today's new lessons -- the OM for tree items, rows, children, and cells; the use of CDATA for encapsulating javascript in XUL files, which are picky XML files, especially when you use a <; and slightly more situation-specific code. 1 <?xml version="1.0"?> 2 <?xml-stylesheet href="chrome://global/skin/" type"text/css" ?> 3 <!DOCTYPE page SYSTEM "chrome://zoteroplay/locale/overlay.dtd"> 4 <page id="zoteroplaySidebar" title="&zoteroplaySidebar.label;" 5 xmlns="http://www.mozilla.org/keymaster/gatekeeper/there.is.only.xul"> 6 7 8 9 <script src="ff-sidebar.js"/> 10 <script> 11 <![CDATA[ 12 function hello() { 13 alert('Hello from ff-sidebar.xul!!'); 14 } 15 function OnTreeDoubleClick(event) { 16 alert('doubleclick'); 17 var objTree = document.getElementById('myTree'); 18 var iIndex = 19 objTree.treeBoxObject.getRowAt(event.clientX,event.clientY); 20 alert(iIndex); 21 } 22 23 function addSource(bIsNewSource) { 24 // note that this is hard-coded to grab from the tree we make, 25 // using XUL, not javascript, below. The only reason I'm doing 26 // this (instead of having it passed in as a function 27 // parameter) is so that we don't have to navigate some DOM 28 // from another file to call this function. Just call from 29 // overlay.js or whatever and forget about sidebar refs. 30 var rootChild = null; 31 if (bIsNewSource) { 32 rootChild = document.getElementById("newRoot"); 33 } else { 34 rootChild = document.getElementById("dupeRoot"); 35 } 36 37 38 39 //======================================== 40 // Create the parent row. 41 //======================================== 42 // Now we're going to create a new TreeItem, which will also 43 // be a container for nested items. 44 // itemSourceHeader will have two items, one the field we're 45 // using to distinguish it (like Title, if it's got one) and 46 // the second is the value ("Moby-Dick; or, The Whale") 47 48 var itemSourceParent = document.createElement("treeitem"); 49 itemSourceParent.setAttribute("container","true"); 50 // create a new row with two cells for the new 51 // item, which will be attached to the rootChild. 52 var rowSourceHeader = document.createElement("treerow"); 53 54 var cellField = document.createElement("treecell"); 55 cellField.setAttribute("label","Title:"); 56 57 var cellFieldValue = document.createElement("treecell"); 58 cellFieldValue 59 .setAttribute("label","Moby-Dick; or, The Whale"); 60 61 rowSourceHeader.appendChild(cellField); 62 rowSourceHeader.appendChild(cellFieldValue); 63 // attach the header values for this item (now in a treerow) to 64 // the item that'll hold it. That is, the source (like a 65 // book) parent has some title information to display. 66 itemSourceParent.appendChild(rowSourceHeader); 67 68 69 70 //======================================== 71 // Now we create the nested row. 72 //======================================== 73 var parentNested = document.createElement("treechildren"); 74 75 // soon we'll put in actual value from an array 76 // rather than this dummy loop; this is just to 77 // show how to have more than one "child". 78 for (var j=1;j<=10;j++) { 79 var itemNested = document.createElement("treeitem"); 80 var rowNested = document.createElement("treerow"); 81 var cellNest1 = document.createElement("treecell"); 82 cellNest1.setAttribute("label","Author:"); 83 84 var cellNest2 = document.createElement("treecell"); 85 cellNest2.setAttribute("label","Herman Melville"); 86 87 rowNested.appendChild(cellNest1); 88 rowNested.appendChild(cellNest2); 89 90 91 itemNested.appendChild(rowNested); 92 parentNested.appendChild(itemNested); 93 } 94 // attach the detailed source information to 95 // the parent item. 96 itemSourceParent.appendChild(parentNested); 97 98 99 //======================================== 100 // Now we add the parent item of all the 101 // source's detailed information to the 102 // root child of the tree. 103 //======================================== 104 rootChild.appendChild(itemSourceParent); 105 } 106 107 108 ]]> 109 </script> 110 111 112 113 <vbox flex="1"> 114 <label value="&zoteroplaySidebar.label;" /> 115 <!-- put your GUI here --> 116 117 118 <tree ondblclick="OnTreeDoubleClick(event);" id="myTree" flex="1"> 119 <treecols> 120 <treecol id="firstname" label="Field" flex="5" 121 primary="true"/> 122 <treecol id="lastname" label="Field Value" flex="5"/> 123 </treecols> 124 125 <treechildren> 126 <treeitem container="true" open="true"> 127 <treerow> 128 <treecell label="Possible New Sources"/> 129 </treerow> 130 <treechildren id="newRoot"> 131 </treechildren> 132 133 </treeitem> 134 <treeitem container="true" open="true"> 135 <treerow> 136 <treecell 137 label="Likely Duplicate Sources"/> 138 </treerow> 139 <treechildren id="dupeRoot"> 140 <treeitem> 141 <treerow> 142 <treecell 143 label="Title:"/> 144 <treecell 145 label="Already Got It"/> 146 </treerow> 147 </treeitem> 148 149 </treechildren> 150 151 </treeitem> 152 </treechildren> 153 </tree> 154 </vbox> 155 </page> 156 So this ends up looking like the following (after rows have been added to new and dupe sources several times): It may also be useful to note that I made the sidebar open up apparently about as wide as Firefox is going to let it with this code in ff-sidebar.js: 1 var mainWindow = null; 2 3 function startup() { 4 mainWindow = window.QueryInterface(Components.interfaces.nsIInterfaceRequestor) 5 .getInterface(Components.interfaces.nsIWebNavigation) 6 .QueryInterface(Components.interfaces.nsIDocShellTreeItem) 7 .rootTreeItem 8 .QueryInterface(Components.interfaces.nsIInterfaceRequestor) 9 .getInterface(Components.interfaces.nsIDOMWindow); 10 11 // this spacebar needs a lot of space to display the info 12 // we're going to put there. Give it at least 400 pixels out of the box. 13 // TODO: I'm setting a check to see if it's already 400, but it seems there's 14 // an absolute max width for sidebars in Firefox by default which is less than 15 // (or about) 400. 16 if (mainWindow.document.getElementById("sidebar-box").width < 400) { 17 mainWindow.document.getElementById("sidebar-box").width = 400; 18 } 19 // Sidebar is loaded and mainwindow is ready 20 } 21 22 function shutdown() { 23 // Sidebar is unloading 24 } 25 26 window.addEventListener("load", startup, false); 27 window.addEventListener("unload", shutdown, false); 28 Labels: extension, firefox, problem solved, XUL posted by ruffin at 4/07/2011 01:18:00 PM

<< Older \| Newer >>