[plt-scheme] Scripting Excel with MysterX

Hi Ben,
Here is a small workspace I was playing in not a long time ago before I lost
my patience and proceeded in javascript/cscript.exe :).

Maybe you can get some inspiration...

#lang scheme
(require mysterx)

(define IN "d:\\csv.txt")
(define XLS (cocreate-instance-from-progid "Excel.Application"))
(com-set-property! XLS "Visible" true)
(define Workbooks (com-get-property XLS "Workbooks"))
(com-invoke Workbooks "OpenText"
            IN
            65001 ;Origin UTF8(65001)
            1 ;StartRow 1
            1 ;DataType xlDelimited(1) xlFixedWidth(2)
            1 ;TextQualifier xlTextQualifierDoubleQuote(1), none(-4142),
single(2)
            #f ;ConsecutiveDelimiters as one (false)
            #f ;Tab
            #f ;Semicolon
            #t ;Comma
            #f ;Space
            #f ;Other
            com-omit ;OtherChar
            ;FieldInfo[colNum, type] XlColumnDataType: xlTextFormat(2)
            com-omit ;FieldInfo example: (vector (vector 1 2) (vector 2 2))
            ; rest skipped
            )

(define ActiveSheet (com-get-property XLS "ActiveSheet"))
(com-set-property! ActiveSheet "Name" "Print")

(define Cells (com-get-property XLS "Cells"))
(com-invoke Cells "Select")
(define Selection (com-get-property XLS "Selection"))
(define Font (com-get-property Selection "Font"))
(com-set-property! Font "Name" "Arial")
(com-set-property! Font "Size" 7)
(define EntireColumn (com-get-property Cells "EntireColumn"))
(com-invoke EntireColumn "AutoFit")

;; additional formatting

;(define R1 (com-invoke ActiveSheet "Rows" "2:2"))
;(com-invoke R1 "Select")
;(com-invoke Selection "Insert" -4121 0) ; xlDown xlFormatFromLeftOrAbove
;(define R2 (com-get-property XLS "Range" "A2"))
;(com-invoke R2 "Select")


(custodian-shutdown-all (current-custodian))
;(define ActiveWorkbook (com-get-property XLS "ActiveWorkbook"))
;(com-invoke ActiveWorkbook "SaveAs" OUT 56 com-omit com-omit #t) ;xlExcel8,
ReadOnlyRecommended
;(com-invoke ActiveWorkbook "Close")
;(com-invoke XLS "Quit")

Cheers,
Filip

2009/6/12 Ben Simon <benjisimon at gmail.com>

> Howdy all,
>
> I've got a project coming up that looks like it's going to require me to
> write some VB code in Excel. That is, unless I can put together a Scheme
> solution first :-).
>
> I've made some progress with MysterX and scripting excel by following the
> example here:
>    http://www.perlmonks.org/index.pl?node_id=153486
>
> Here's what I have so far:
>
> (require mysterx)
> (let* ([excel (com-get-active-object-from-coclass "Microsoft Office Excel
> Application")]
>         [book (com-invoke (com-get-property excel "Workbooks") "Add")])
>   (com-get-property book "Worksheets"))
>
> What I'm stuck on, is that I believe Worksheets is an associative array,
> and I'm trying to get a hold of Sheet1. Or, as they say in perl:
>
> my $Sheet = $Book->Worksheets("Sheet1");
>
> I can get the value of the Worksheets property, but I can't index into it.
>
> Essentially, I'm asking the same question raised here:
> http://tinyurl.com/mmuffa - as next I need to figure out how to access
> ranges and other goodies.
>
> Thanks for the help - and an especially big thanks if you get me out of
> coding in VB ;-)
>
> -Ben
>
> --
> Have an idea for software?  I can make it happen -
> http://www.ideas2executables.com
> My Blog: http://benjisimon.blogspot.com
>
> _________________________________________________
>  For list-related administrative tasks:
>  http://list.cs.brown.edu/mailman/listinfo/plt-scheme
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.racket-lang.org/users/archive/attachments/20090613/44972749/attachment.html>