[jawsscripts] Re: An Excel script
- From: Marlon Brandão de Sousa <splyt.lists@xxxxxxxxx>
- To: jawsscripts@xxxxxxxxxxxxx
- Date: Tue, 15 Sep 2009 18:01:25 -0300
Hello,
Just for your information I was able to code the script.
It basically is composed of two scripts: one allows for
setting up to six columns to be monitored and the other
that reads the columns choosen.
I would like to thank pretty much everybody who helped
me to code it, specially Martin who stated some
interesting types.
I will past the code below and I would ask you guys to
point out better ways to achieving the objectives, if
you do know. Also, after the code, I will post some
questions about how to deploy these scripts.
file microsoft excel.jsb
const
DebugJAWSBackspace = False,
; next constant stores the columns letters (used with
stringSeguiment to obtain the letter of the current column
c_sColNames = "A|B|C|D|E|F|G|H|I|J|K|L|M|N|O|P|Q|R|S|T|U|V|W|X|Y|Z|A
A|A B|A C|A D|A E|A F|A G|A H|A I|A J|A K|A L|A M|A N|A O|A P|A Q|A
r|A S|A T|A U|A V|A W|A X|A Y|A Z"
globals
int mCol1, ;slot one
int mCol2, ;slot two
int mCol3, ; ;slot three
int mCol4, ;slot four
int mCol5, ;slot five
int mCol6 ;;slot six
<question> did someone already come with any workaround to represent arrays?
The stringSeguiment aproach may work but I think that it would cause
serious performance issues if used as an array simulation
; the following function gets the column index and retrieves and
returns its equivalent letter, in order to compose the address of the
cell to be read in the script readMonitorCol
string function getColName(int index, int bTrim)
; index = column index
; bTrim = if true will trim the blank spaces between the letters of
the c_sColNames
; it was necessary to add this space to make JAWs speak the selected
column more clearly. For translating the index into the column letter,
we do not meed these spaces
var
string sColName ; column letter
let sColName = StringSegment (c_sColNames, "|", index)
if(bTrim) then
let sColName = stringStripAllBlanks (sColName)
endif
return sColName
endFunction
; following script sets the current column to a slot.
Its keystroke assignment is set to ctrl+alt+shift in
microsoft excel.jkm
script SetMonitorCol()
var
int Col, ; column ndex
int slotCol ; Desired slote to put this column
; get the number pressed together with ctrl+alt+sift to activate the
script (this number (currently 1 to 6) will point the monitoring slote
to the current column
let slotCol=stringToInt(stringRight(getCurrentScriptKeyName(),1)) ;
get the current cell address (in indexes). The first parameter of
GetCellCoordinates will return the current column index
GetCellCoordinates (col, 0)
if(slotCol==1) then ; store the current column in slote one
let mCol1=col
elif(slotCol==2) then ; ; store the current column in slote two
let mCol2=col
elif(slotCol==3) then ;; store the current column in slote three
let mCol3=col
elif(slotCol==4) then ;; store the current column in slote four
let mCol4=col
elif(slotCol==5) then ; ; store the current column in slote five
let mCol5=col
elif(slotCol==6) then ;; store the current column in slote six
let mCol6=col
else ; point error
sayString("invalid slot")
endif
; report what have been done
sayString("collumn " + getColName(col, false) + " added to slot " +
intToString(slotCol))
EndScript
; following script reads the column of a given line, accordingly with
the choosen slot
; its keystroke assignment is ctrl+shift+ in the microsoft excel.jkm file.
Script readMonitorCol ()
var
int iLine, ; current line
int slotCol ;choosen slot
; get the current line
GetCellCoordinates (0, iLine)
; get the number pressed together with ctrl+alt+sift to activate the
script (this number (currently 1 to 6) will point the column to be
read
let slotCol=stringToInt(stringRight(getCurrentScriptKeyName(),1))
if(slotCol==1) then ; read the cell (line iLine, column with the
letter equivalent to the index stored in the slot one
readMonitorCell(getColName(mCol1, true) + intToString(iLine))
elif(slotCol==2) then ; read the cell (line iLine, column with the
letter equivalent to the index stored in the slot two
readMonitorCell(getColName(mCol2, true)+intToString(iLine))
elif(slotCol==3) then ; read the cell (line iLine, column with the
letter equivalent to the index stored in the slot three
readMonitorCell(getColName(mCol3, true)+intToString(iLine))
elif(slotCol==4) then ; read the cell (line iLine, column with the
letter equivalent to the index stored in the slot four
readMonitorCell(getColName(mCol4, true)+intToString(iLine))
elif(slotCol==5) then ; read the cell (line iLine, column with the
letter equivalent to the index stored in the slot five
readMonitorCell(getColName(mCol5, true)+intToString(iLine))
elif(slotCol==6) then ; read the cell (line iLine, column with the
letter equivalent to the index stored in the slot six
readMonitorCell(getColName(mCol6, true)+intToString(iLine))
else ; error
sayString("invalid slot")
endif
EndScript
My questions are the following:
1- How can I deploy these scripts in an easy way?
Currently I would have to ask that one opens its microsoft excel.jss,
past the const declaration in the right place, then past the scripts's
code somewhere in the file, then update their microsoft excel.jkm file
with the right keystrokes, then update their jsd file with the scripts
documentation. This could easily lead to errors causing either the
scripts to not work properly or the microsoft excel.jss file to not
compile if something goes wrong
I surely could write a inno setup installer with heavy custom pascal
code that would programmatically open the files, add information and
then compile everything. I do think, however, that creating a .jsb
file with this code and using a use statement might be the best
solution. One would still need to edit their jss file, but only one
line would be needed.
I fear however that my jsb file can not access the excel built in
functions such as for example readMonitorCel(). So what would be the
easier way to deploy this kind of script?
If I use a jsb then what about the definitions of the jkm and jsd files?
Thanks,
Marlon
2009/9/11, Martin Slack <m.g.slack@xxxxxxxxxxxx>:
> Hi Marlon,
>
> Your point 1- below is answered by my email below sent on 10 September.
> The code I gave showed only one line of the form:
>
> SayString (StringSegment (sLine, "|", 20))
>
> because in your original posting, you said you wanted to read the contents
> of column t. But you can have as many SayString statements as you like,
> because the string sLine contains the contents of all the cells in the
> current row. To speak the cells from the current row in columns a, f, m and
> t simply use four lines of code:
>
> SayString (StringSegment (sLine, "|", 1))
> SayString (StringSegment (sLine, "|", 6))
> SayString (StringSegment (sLine, "|", 13))
> SayString (StringSegment (sLine, "|", 20))
>
> If you want to follow a different route, as outlined in your point 2-
> below, you can use GetCellCoordinates to find the number of the column
> containing the current cell, and use that as an index into the StringSegment
> function to return the appropriate letter from a delimited string formed
> thus:
>
> let sExtendedAlphabet = "a|b|c|d|e|f|g| ... |uuu|vvv|www|xxx|yyy|zzz"
>
> where I am using the vertical bar character as the delimiter and only
> including three alphabet's worth of columns (= 78 columns). Extend the
> string further if you need to access more columns.
>
> hth
>
> Martin
>
>
> ----- Original Message -----
> From: "Marlon Brandão de Sousa" <splyt.lists@xxxxxxxxx>
> To: <jawsscripts@xxxxxxxxxxxxx>
> Sent: Friday, September 11, 2009 6:31 PM
> Subject: [jawsscripts] Re: An Excel script
>
>
>> Hello,
>> 1- The getRowTotals and such will work for one collumn only. I want to
>> set up to six collumns that will be read accordingly to the end number
>> of the keystroke pressed.
>> 2- I found that the getSelectionCoordinates() function will return me
>> a coordinate like b5 for example. I now need to get only the letters
>> of the string whichj will be the current collumn. Any suggestion as
>> how I can achieve this (like getting the "a62" string and end with the
>> "a" character only?
>> I cant simply get the first character because a collumn may have two
>> letters size like "ab75".
>> I just need to separate letters from numbers in a string.
>> Marlon
>>
>> 2009/9/11, Martin Slack <m.g.slack@xxxxxxxxxxxx>:
>>> GetCellCoordinates will return both the row and column as integers. See
>>> my
>>> replies to Reed for a method of converting column coordinates to the
>>> appropriate letters.
>>>
>>> Martin
>>>
>>>
>>> ----- Original Message -----
>>> From: "Marlon Brandão de Sousa" <splyt.lists@xxxxxxxxx>
>>> To: <jawsscripts@xxxxxxxxxxxxx>
>>> Sent: Friday, September 11, 2009 2:17 PM
>>> Subject: [jawsscripts] Re: An Excel script
>>>
>>>
>>>> Hello,
>>>> I will try it. BTW, there is a function called sayCell which takes a
>>>> string as parameter and that will say the cell contents. The problem
>>>> is, this parameter must be something like "a35" ... I just need to
>>>> know a way of getting the collumn one is placed into to set the
>>>> monitor thing.
>>>> Any other suggestion as how to get the current collumn letter when
>>>> pressing a keystroke?
>>>> Marlon
>>>>
>>>> 2009/9/10, Martin Slack <m.g.slack@xxxxxxxxxxxx>:
>>>>> Marlon,
>>>>>
>>>>> The JAWS function GetRowText can be called to produce a delimited
>>>>> string
>>>>> of all the text in the active row of the table. You can then specify
>>>>> which
>>>>> cell's contents to speak using the StringSegment function as below:
>>>>>
>>>>> Script testExcel ()
>>>>> Var string sLine
>>>>>
>>>>> let sLine = GetRowText ("|", "c%1r%2", "nothing found", 1, 26)
>>>>> ;let sLine = GetRowText ("|", "", "nothing found")
>>>>> SayString (StringSegment (sLine, "|", 20))
>>>>>
>>>>> EndScript
>>>>>
>>>>>
>>>>> You can use either form of the GetRowText function above, either
>>>>> specifying everything (include each cell's coordinates in the string as
>>>>> well
>>>>> as how much of the row to read), or accept the default options of no
>>>>> coordinates and read the whole row.
>>>>>
>>>>> If you need to specify the column by letter, you could compare your
>>>>> letter
>>>>> with a string of characters, say "a|b|c| ... |zz" using
>>>>> StringSegmentIndex
>>>>> until you found the match and then enter that number in the above code.
>>>>>
>>>>> hth
>>>>>
>>>>> Martin
>>>>>
>>>>>
>>>>> ----- Original Message -----
>>>>> From: "Marlon Brandão de Sousa" <splyt.lists@xxxxxxxxx>
>>>>> To: <jawsscripts@xxxxxxxxxxxxx>
>>>>> Sent: Thursday, September 10, 2009 9:46 PM
>>>>> Subject: [jawsscripts] Re: An Excel script
>>>>>
>>>>>
>>>>>> That's also what I would like to know.
>>>>>> I want to implement a kind of monitor cell that is relative to the
>>>>>> line one currently is placed, so that if they are whatching for
>>>>>> collumn t for example they can be anywhere in line 2 and pressing a
>>>>>> keystroke the t2 cell would be read and if they are in anywhere in
>>>>>> line 135 and press the same command the t135 cell will be read and so
>>>>>> on.
>>>>>> I can code a function like numberToLetter and use it, but cinse JAWS
>>>>>> can say the coordinates of a given cell then I also can use this
>>>>>> information in my scripts. We just need to figure out or be helped as
>>>>>> to how to get this information.
>>>>>> Marlon
>>>>>>
>>>>>> 2009/9/10, Reed Poynter <reed.poynter@xxxxxxxxx>:
>>>>>>> Hi Scripters,
>>>>>>>
>>>>>>> In Ms Excel 2003, there is a Go To dialog where you can select blocks
>>>>>>> of
>>>>>>> data based on a top left and a bottom right cell.
>>>>>>> For example, in a spreadsheet, you hit F5 and specify A1:G6 and hit
>>>>>>> enter.
>>>>>>> The block of data is selected.
>>>>>>>
>>>>>>> I want to be able to do the following.
>>>>>>> Go to the top left cell and hit a key to save its coordinates, go to
>>>>>>> the
>>>>>>> bottom right cell, hit a second key to save its coordinates, put the
>>>>>>> 2
>>>>>>> saved
>>>>>>> coordinates together in a string and put it into the go to dialog.
>>>>>>>
>>>>>>> I don't know of anything in Excel that will do this for me. So, I
>>>>>>> thought
>>>>>>> I'd write a JFW script to do it.
>>>>>>>
>>>>>>> I think my process would be to, with a hot key, save the coordinates
>>>>>>> of
>>>>>>> the
>>>>>>> top left cell to the clipboard,
>>>>>>> With a second hot key, capture the bottom cell coordinates, and put
>>>>>>> them
>>>>>>> together into a string with the first coordinates once retrieved from
>>>>>>> the
>>>>>>> clipboard.
>>>>>>> Save this new string to the clipboard,
>>>>>>> Open Excel's Go To dialog and paste in my coordinates.
>>>>>>>
>>>>>>> My first problem is having the script determine the cell coordinates.
>>>>>>> Looking through the functions in the Script Manager, the only guy I
>>>>>>> see
>>>>>>> is
>>>>>>> GetCellCoordinates.
>>>>>>> If I am sitting on cell A5, GetCellCoordinates (x, y) returns
>>>>>>> integers
>>>>>>>
>>>>>>> 1
>>>>>>> and 5.
>>>>>>> I need something that will give me a5.
>>>>>>> Any suggestions?
>>>>>>>
>>>>>>>
>>>>>>> Thanks.
>>>>>>>
>>>>>>> Reed
>>>>>>>
>>>>>>>
>>>>>>> __________
>>>>>>> Visit and contribute to The JAWS Script Repository
>>>>>>> http://jawsscripts.com
>>>>>>>
>>>>>>> View the list's information and change your settings at
>>>>>>> //www.freelists.org/list/jawsscripts
>>>>>>>
>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> When you say "I wrote a program that crashed Windows," people just
>>>>>> stare at you blankly and say "Hey, I got those with the system, for
>>>>>> free."
>>>>>> Linus Torvalds
>>>>>> __________
>>>>>> Visit and contribute to The JAWS Script Repository
>>>>>> http://jawsscripts.com
>>>>>>
>>>>>> View the list's information and change your settings at
>>>>>> //www.freelists.org/list/jawsscripts
>>>>>>
>>>>>
>>>>>
>>>>> --------------------------------------------------------------------------------
>>>>>
>>>>>
>>>>>
>>>>> No virus found in this incoming message.
>>>>> Checked by AVG - www.avg.com
>>>>> Version: 8.5.409 / Virus Database: 270.13.89/2360 - Release Date:
>>>>> 09/10/09
>>>>> 11:29:00
>>>>>
>>>>> __________
>>>>> Visit and contribute to The JAWS Script Repository
>>>>> http://jawsscripts.com
>>>>>
>>>>> View the list's information and change your settings at
>>>>> //www.freelists.org/list/jawsscripts
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> When you say "I wrote a program that crashed Windows," people just
>>>> stare at you blankly and say "Hey, I got those with the system, for
>>>> free."
>>>> Linus Torvalds
>>>> __________
>>>> Visit and contribute to The JAWS Script Repository
>>>> http://jawsscripts.com
>>>>
>>>> View the list's information and change your settings at
>>>> //www.freelists.org/list/jawsscripts
>>>>
>>>
>>>
>>> --------------------------------------------------------------------------------
>>>
>>>
>>>
>>> No virus found in this incoming message.
>>> Checked by AVG - www.avg.com
>>> Version: 8.5.409 / Virus Database: 270.13.91/2363 - Release Date:
>>> 09/11/09
>>> 09:15:00
>>>
>>> __________
>>> Visit and contribute to The JAWS Script Repository http://jawsscripts.com
>>>
>>> View the list's information and change your settings at
>>> //www.freelists.org/list/jawsscripts
>>>
>>>
>>
>>
>> --
>> When you say "I wrote a program that crashed Windows," people just
>> stare at you blankly and say "Hey, I got those with the system, for
>> free."
>> Linus Torvalds
>> __________
>> Visit and contribute to The JAWS Script Repository http://jawsscripts.com
>>
>> View the list's information and change your settings at
>> //www.freelists.org/list/jawsscripts
>>
>
>
> --------------------------------------------------------------------------------
>
>
>
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 8.5.409 / Virus Database: 270.13.91/2363 - Release Date: 09/11/09
> 09:15:00
>
> __________
> Visit and contribute to The JAWS Script Repository http://jawsscripts.com
>
> View the list's information and change your settings at
> //www.freelists.org/list/jawsscripts
>
>
--
When you say "I wrote a program that crashed Windows," people just
stare at you blankly and say "Hey, I got those with the system, for
free."
Linus Torvalds
__________
Visit and contribute to The JAWS Script Repository http://jawsscripts.com
View the list's information and change your settings at
//www.freelists.org/list/jawsscripts
Other related posts:
