Hi Ben,<div><br></div><div>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 :).</div><div><br></div><div>Maybe you can get some inspiration...</div>
<div><br></div><div><div>#lang scheme</div><div>(require mysterx)</div><div><br></div><div>(define IN "d:\\csv.txt")</div><div>(define XLS (cocreate-instance-from-progid "Excel.Application"))</div><div>
(com-set-property! XLS "Visible" true)</div><div>(define Workbooks (com-get-property XLS "Workbooks"))</div><div>(com-invoke Workbooks "OpenText" </div><div> IN</div><div> 65001 ;Origin UTF8(65001)</div>
<div> 1 ;StartRow 1</div><div> 1 ;DataType xlDelimited(1) xlFixedWidth(2)</div><div> 1 ;TextQualifier xlTextQualifierDoubleQuote(1), none(-4142), single(2)</div><div> #f ;ConsecutiveDelimiters as one (false)</div>
<div> #f ;Tab </div><div> #f ;Semicolon</div><div> #t ;Comma</div><div> #f ;Space</div><div> #f ;Other</div><div> com-omit ;OtherChar</div><div> ;FieldInfo[colNum, type] XlColumnDataType: xlTextFormat(2)</div>
<div> com-omit ;FieldInfo example: (vector (vector 1 2) (vector 2 2))</div><div> ; rest skipped</div><div> )</div><div><br></div><div>(define ActiveSheet (com-get-property XLS "ActiveSheet"))</div>
<div>(com-set-property! ActiveSheet "Name" "Print")</div><div><br></div><div>(define Cells (com-get-property XLS "Cells"))</div><div>(com-invoke Cells "Select")</div><div>(define Selection (com-get-property XLS "Selection"))</div>
<div>(define Font (com-get-property Selection "Font"))</div><div>(com-set-property! Font "Name" "Arial")</div><div>(com-set-property! Font "Size" 7)</div><div>(define EntireColumn (com-get-property Cells "EntireColumn"))</div>
<div>(com-invoke EntireColumn "AutoFit")</div><div><br></div><div>;; additional formatting</div><div><br></div><div>;(define R1 (com-invoke ActiveSheet "Rows" "2:2"))</div><div>;(com-invoke R1 "Select")</div>
<div>;(com-invoke Selection "Insert" -4121 0) ; xlDown xlFormatFromLeftOrAbove</div><div>;(define R2 (com-get-property XLS "Range" "A2"))</div><div>;(com-invoke R2 "Select")</div><div>
</div><div><br></div><div>(custodian-shutdown-all (current-custodian))</div><div>;(define ActiveWorkbook (com-get-property XLS "ActiveWorkbook"))</div><div>;(com-invoke ActiveWorkbook "SaveAs" OUT 56 com-omit com-omit #t) ;xlExcel8, ReadOnlyRecommended</div>
<div>;(com-invoke ActiveWorkbook "Close")</div><div>;(com-invoke XLS "Quit")</div><div><br></div><div>Cheers,</div><div>Filip</div></div><div><br><div class="gmail_quote">2009/6/12 Ben Simon <span dir="ltr"><<a href="mailto:benjisimon@gmail.com">benjisimon@gmail.com</a>></span><br>
<blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex;">Howdy all,<br><br>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 :-).<br>
<br>I've made some progress with MysterX and scripting excel by following the example here:<br>
<a href="http://www.perlmonks.org/index.pl?node_id=153486" target="_blank">http://www.perlmonks.org/index.pl?node_id=153486</a><br><br>Here's what I have so far:<br><br>(require mysterx)<br>(let* ([excel (com-get-active-object-from-coclass "Microsoft Office Excel Application")]<br>
[book (com-invoke (com-get-property excel "Workbooks") "Add")])<br> (com-get-property book "Worksheets"))<br><br>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:<br>
<br><div style="margin-left:40px"> my $Sheet = $Book->Worksheets("Sheet1");<br></div><br>I can get the value of the Worksheets property, but I can't index into it.<br><br>Essentially, I'm asking the same question raised here: <a href="http://tinyurl.com/mmuffa" target="_blank">http://tinyurl.com/mmuffa</a> - as next I need to figure out how to access ranges and other goodies.<br>
<br>Thanks for the help - and an especially big thanks if you get me out of coding in VB ;-)<br><br>-Ben<br><font color="#888888"><br>--<br>Have an idea for software? I can make it happen - <a href="http://www.ideas2executables.com" target="_blank">http://www.ideas2executables.com</a><br>
My Blog: <a href="http://benjisimon.blogspot.com" target="_blank">http://benjisimon.blogspot.com</a><br>
</font><br>_________________________________________________<br>
For list-related administrative tasks:<br>
<a href="http://list.cs.brown.edu/mailman/listinfo/plt-scheme" target="_blank">http://list.cs.brown.edu/mailman/listinfo/plt-scheme</a><br>
<br></blockquote></div><br></div>