>excel = WIN32OLE::new('excel.Application')
>workbook = excel.Workbooks.Open("../PC/" + Time.now.strftime("%d-%b-%y") +
".csv")
>worksheet = workbook.Worksheets(1) #get hold of the first worksheet
>#Format workbook columns
>worksheet.range("b1:b4").Interior['ColorIndex'] = 36 #pale yellow
># How do I widen the 2nd column?
excel.range('B:B').EntireColumn.AutoFit
Now, rather than merely giving you the fish, I'd like to teach you to fish.
This is how I did it.
1) Open up Excel / Tools / Macro / Visual Basic Editor
2) Press F2 to bring up the VBE Object Browser.
3) Search for "AutoFit".
4) Observe that AutoFit is a Method on the Excel.Range object.
5) Go back to IRB, and try excel.range("B1:B4").AutoFit
6) Get the Win32OLE Runtime Error
WIN32OLERuntimeError: autofit
OLE error code:800A03EC in Microsoft Office Excel
Unable to get the AutoFit property of the Range class
HRESULT error code:0x80020009
7) Curse.
8) Go back to Excel.
9) Look for AutoFit in the UI.
10) Remember vaguely that double-clicking on the line separating two columns
will cause the leftmost to autofit (there's a way to do it via the menus,
too: Format / Column / AutoFit).
11) (And this is the key one) record an Excel macro, in which the only
action is to perform the AutoFit action for a column.
12) Go to Tools / Macro / Edit, and observe the code that Excel has just
written as it recorded the macro.
13) Recognize that the Range property took this extra weird property
EntireColumn.
14) Go back to IRB and try something of the same syntax.
15) Go back to Excel and observe that, hot damn, it actually happened.
It's convoluted, and it's a pain. But exploratory testing (that's /exactly/
what this is, by the way) is one of the most powerful weapons against the
confusion afforded by Microsoft's bizarro object models and their painfully
inadequate documentation.
---Michael B.
_______________________________________________
Wtr-general mailing list
[email protected]
http://rubyforge.org/mailman/listinfo/wtr-general
