Excel is controlled via a command dialect. Just call the EXCEL function with the dialected commands in a block.
excel [start show quit]
The dialect is, so far, just some basic ideas. I need feedback on how you would like it to work. I would like to stick with a small number of commands, because I think we can get a lot of
leverage that way, but I'm open to any and all suggestions.
You'll notice that I allow some alternative words in places (e.g. ADD versus INSERT). I was going to go with REBOLish words everywhere, but I decided to try this as an experiment. The
idea being that you should be able to write down the commands as if you were speaking them to a person.
alerts [on | off] Turns things like "Do you want to save?" dialogs on or off
autofilter [on | off] Turns on the autofilter dropdown for a range.
close workbook Closes the active workbook
print Print the value to the REBOL console. Mainly for debugging.
quit Shuts down Excel; cleans up COM connection. Must be the last command you send.
show This makes Excel visible. You may want to pump data to Excel before displaying it.
start Starts Excel; initializes COM connection. Must be the first command you send.
open "C:\dev\test.xls"
open %/c/dev/test.xls
open file "C:\dev\test.xls"
save
save as "new-file.xls"
save as %new-file.xls
save-as "new-file.xls"
save-as %new-file.xls
You can use the words INSERT or ADD, followed by the kind of item you want to insert (workbook or worksheet). Optionally, you can use the words A and/or NEW in between, for readability.
insert workbook
add worksheet
add a workbook
insert new worksheet
add a new worksheet
You can also include an optional name for a worksheet when you add it.
add worksheet "NewSheet"
remove worksheet
remove worksheet 2
remove worksheet "NewSheet"
To move to a new location, you can say GOTO or GO TO.
You can move to a cell by giving a row and column number
goto cell 2 3
go to 5 6
Or by giving the cell ID
goto cell "B4"
go to cell "E3"
You can change to a specific workbook or worksheet by name
or number (issue! values are treated as numbers).
goto workbook "test.xls"
go to worksheet 2
go to worksheet #1
The SELECT command tells Excel to select a range. The criteria
you pass it are just as you would enter them in Excel.
select "A3"
select "3:5"
select "D:F"
select "B2:F5"
url! and time! values are possible too, though there may be
limitations to their use, so consider them experimental.
select 3:5
select B2:F5
optionally, you can use one of the following words after the
word SELECT, to make your intent clearer.
cell cells range row rows col cols column columns
They have no effect on the functionality. e.g. if you say
"select rows", but pass a value of "D:F", those columns will
be selected.
select cell "A3"
select rows "3:5"
select columns "D:F"
select range "B2:F5"
Selecting named ranges should also work, but I haven't tested
it.
There are times where the selection commands seem to stop
working. Haven't figured out why yet.
To set a value into a cell or range, you can first select the cell
and then set the value:
select "B4"
set value to 100
select "B5"
set value 3
goto cell "B6"
change to "Testing"
select "B7"
set to "=B4 * B5"
select "A5:A9"
change to "=$B$5 * PI()"
Or you can select and set the value in one command:
set cell 10 2 to 222.22
set 11 2 to 333.33
set cells "C2:C6" to 123
set "D3:E4" "Yeah!"
change "A1" "=B10"
These simple words will act on the current selection:
cut
copy
paste
To use the PasteSpecial feature in Excel, you can specify
what you want to paste. e.g.:
paste value
paste only values
paste only formula
paste formulas
paste only format
paste comments
paste all except borders
paste no borders
The ONLY word is optional, and the other words you can use
are:
value values formula formulas
format formats comment comments
all-except-borders no-borders
The NO-BORDERS option can also be separate words:
paste all except borders
paste no borders
You can also cut and copy ranges to new locations:
(PasteSpecial isn't supported for this syntax yet)
cut "A1:A9" to "B1"
copy "A1:A9" to "B1"
To copy between workbooks, you can do this:
copy workbook "pbtest.xls" "A1:C5"
to workbook "test-b.xls" "B2"
goto cell "C6"
a: current cell
do [print ["current cell" a]]
a: current column
do [print ["current column" a]]
a: current row
do [print ["current row" a]]
do []
This is here for testing, and to decide if we want to
include it.
Gabriele's great compile-rules.r script is now used to
provide support for many REBOL constructs (foreach, if,
do, repeat, until, loop, use, while, and more). You should
be able to use those functions transparently in your
excel dialect code. e.g.
repeat y 5 [print y]
repeat i 5 [set i 8 to i]
===Download
You need a license key for Rebol to use this Excel dialect because you need to use the DLL. This project is still a prototype.
=url /download/reb-excel-proto-006.zip Version 0.0.6 Release Date: 19-Dec-2004
###
=== History
prototype-1 20-Oct-2004 DLL compiled against Excel 2000 TLB
prototype-1b 21-Oct-2004 Late bound DLL
prototype-2 25-Oct-2004 added ability to set values into cells
prototype-3 27-Oct-2004 added Cut, copy, and paste support
prototype-4 28-Oct-2004 added a few more dialect commands
prototype-5 9-Dec-2004 added autofilter
prototype-6 18-Dec-2004 uses %compile-rules.r
excel [start show
The dialect is, so far, just some basic ideas. I need feedback on how you would like it to work. I would like to stick with a small number of commands, because I think we can get a lot of
leverage that way, but I'm open to any and all suggestions.
You'll notice that I allow some alternative words in places (e.g. ADD versus INSERT). I was going to go with REBOLish words everywhere, but I decided to try this as an experiment. The
idea being that you should be able to write down the commands as if you were speaking them to a person.
Miscellaneous Commands
alerts [on | off] Turns things like "Do you want to save?" dialogs on or off
autofilter
close workbook Closes the active workbook
print Print the value to the REBOL console. Mainly for debugging.
quit Shuts down Excel; cleans up COM connection. Must be the last command you send.
show This makes Excel visible. You may want to pump data to Excel before displaying it.
start Starts Excel; initializes COM connection. Must be the first command you send.
Opening a file
open "C:\dev\test.xls"
open %/c/dev/test.xls
open file "C:\dev\test.xls"
Saving a file
save
save as "new-file.xls"
save as %new-file.xls
save-as "new-file.xls"
save-as %new-file.xls
Adding new workbooks or worksheets
You can use the words INSERT or ADD, followed by the kind of item you want to insert (workbook or worksheet). Optionally, you can use the words A and/or NEW in between, for readability.
insert workbook
add worksheet
add a workbook
insert new worksheet
add a new worksheet
You can also include an optional name for a worksheet when you add it.
add worksheet "NewSheet"
Removing the active worksheet
remove worksheet
Removing a specific worksheet
remove worksheet 2
remove worksheet "NewSheet"
Navigation
To move to a new location, you can say GOTO or GO TO.
You can move to a cell by giving a row and column number
goto cell 2 3
go to 5 6
Or by giving the cell ID
goto cell "B4"
go to cell "E3"
You can change to a specific workbook or worksheet by name
or number (issue! values are treated as numbers).
goto workbook "test.xls"
go to worksheet 2
go to worksheet #1
Selection
The SELECT command tells Excel to select a range. The criteria
you pass it are just as you would enter them in Excel.
select "A3"
select "3:5"
select "D:F"
select "B2:F5"
url! and time! values are possible too, though there may be
limitations to their use, so consider them experimental.
select 3:5
select B2:F5
optionally, you can use one of the following words after the
word SELECT, to make your intent clearer.
cell cells range row rows col cols column columns
They have no effect on the functionality. e.g. if you say
"select rows", but pass a value of "D:F", those columns will
be selected.
select cell "A3"
select rows "3:5"
select columns "D:F"
select range "B2:F5"
Selecting named ranges should also work, but I haven't tested
it.
There are times where the selection commands seem to stop
working. Haven't figured out why yet.
Setting Values
To set a value into a cell or range, you can first select the cell
and then set the value:
select "B4"
set value to 100
select "B5"
set value 3
goto cell "B6"
change to "Testing"
select "B7"
set to "=B4 * B5"
select "A5:A9"
change to "=$B$5 * PI()"
Or you can select and set the value in one command:
set cell 10 2 to 222.22
set 11 2 to 333.33
set cells "C2:C6" to 123
set "D3:E4" "Yeah!"
change "A1" "=B10"
Cut, Copy Paste
These simple words will act on the current selection:
cut
copy
paste
To use the PasteSpecial feature in Excel, you can specify
what you want to paste. e.g.:
paste value
paste only values
paste only formula
paste formulas
paste only format
paste comments
paste all except borders
paste no borders
The ONLY word is optional, and the other words you can use
are:
value values formula formulas
format formats comment comments
all-except-borders no-borders
The NO-BORDERS option can also be separate words:
paste all except borders
paste no borders
You can also cut and copy ranges to new locations:
(PasteSpecial isn't supported for this syntax yet)
cut "A1:A9" to "B1"
copy "A1:A9" to "B1"
To copy between workbooks, you can do this:
copy workbook "pbtest.xls" "A1:C5"
to workbook "test-b.xls" "B2"
Current cell, row, and column
goto cell "C6"
a: current cell
do [print ["current cell" a]]
a: current column
do [print ["current column" a]]
a: current row
do [print ["current row" a]]
DOing REBOL code
do [
This is here for testing, and to decide if we want to
include it.
Looping, Conditional Branching, and more
Gabriele's great compile-rules.r script is now used to
provide support for many REBOL constructs (foreach, if,
do, repeat, until, loop, use, while, and more). You should
be able to use those functions transparently in your
excel dialect code. e.g.
repeat y 5 [print y]
repeat i 5 [set i 8 to i]
===Download
You need a license key for Rebol to use this Excel dialect because you need to use the DLL. This project is still a prototype.
=url /download/reb-excel-proto-006.zip Version 0.0.6 Release Date: 19-Dec-2004
###
=== History
prototype-1 20-Oct-2004 DLL compiled against Excel 2000 TLB
prototype-1b 21-Oct-2004 Late bound DLL
prototype-2 25-Oct-2004 added ability to set values into cells
prototype-3 27-Oct-2004 added Cut, copy, and paste support
prototype-4 28-Oct-2004 added a few more dialect commands
prototype-5 9-Dec-2004 added autofilter
prototype-6 18-Dec-2004 uses %compile-rules.r