Format Excel 2007/2010 properties from script

[shornw]

Does anyone know of a way to format cell/column/row properties of a worksheet from within a script, using the current _Excel.UDF. In the good old days of 2003, you could use Alt+ <keys> to access menus if necessary but the ribbon isn't so friendly.

What I want to do is:

Set column width (several columns, different widths)

Set one column to align to top (_ExcelHorizontalAlignSet() but no vertical equivalent)

Other functionality knowledge would be nice to have.

Is LocoDarwin's original UDF a viable way to achieve this.

Thanks

Edited February 2, 2012 by shornw

[water]

Yes, the ExcelCOM UDF by LocoDarwin has a lot of additional useful functions. Or at least you get the idea how to code it yourself.

Use function _ExcelColWidthSet to set the column width

Use function _ExcelVerticalAlignSet to align at the top

For formatting options you don't find in the UDF please have a look at MSDN.

I can offer some details if needed.

[shornw]

it's always you Water!! Soon you will take the job as my personal mentor

As always, thank you.

Just a couple of quick questions:

Does it work OK with 2007/2010

Does it contain all the functionality of current AutoIT Excel.udf

Rob

[water]

Hi shornw,

personal mentor, fine

I think ExcelCOM should work with Excel 2007 and 2010. If not, it should be easy to adapt.

I haven't compared both UDFs but I got the impression that the UDF that comes with AutoIt is based on the ExcelCOM UDF.

As function names overlap you can't use both UDFs at the same time.

[shornw]

Perfect.

I replaced the existing Excel.au3 and tried all the functions that my script uses - and everything works fine.

Formatting functions work with Excel 2010, and make a big difference to my life.

Does anyone know why only a cut-down version of this UDF is included as standard (why have horizontal alignment but not vertical alignment)? Whatever it is, I'm sure the reasoning was well thought out tho.

Thank you....again, Water

[water]

As we are at the subject: What do you think of an extended Excel UDF? Something like a combination of the Excel and ExcelCOM UDF plus additonal functions. Plus help file, plus examples, plus SciTE ingtegration ...

[shornw]

Personally, I think it would be a very useful addition. Excel has to be one of the most widely used pieces of software these days, and its broad capabilities lend itself extremely well to many tasks within IT support, not least of all reporting.

In the past, I have created worksheets from scripts then manually manipulated them, to make them more 'user friendly' or appear more professional, before sending them to other people .

What you're proposing would get better use and helpfiles and SciTe support would make it easier for us simpleton end users to use.

Bring it on

[water]

Let's see what we get started when we have finished our ExcelChart UDF