Sign in to follow this  
Followers 0
shornw

Format Excel 2007/2010 properties from script

8 posts in this topic

#1 ·  Posted (edited)

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 by shornw

[font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font]

Share this post


Link to post
Share on other sites



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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


[font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font]

Share this post


Link to post
Share on other sites

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.


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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


[font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font]

Share this post


Link to post
Share on other sites

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 ...


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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 :)


[font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font]

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0