shornw Posted February 2, 2012 Share Posted February 2, 2012 (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 February 2, 2012 by shornw [font='Comic Sans MS']Eagles may soar high but weasels dont get sucked into jet engines[/font] Link to comment Share on other sites More sharing options...
water Posted February 2, 2012 Share Posted February 2, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
shornw Posted February 2, 2012 Author Share Posted February 2, 2012 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] Link to comment Share on other sites More sharing options...
water Posted February 2, 2012 Share Posted February 2, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
shornw Posted February 2, 2012 Author Share Posted February 2, 2012 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] Link to comment Share on other sites More sharing options...
water Posted February 2, 2012 Share Posted February 2, 2012 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 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
shornw Posted February 2, 2012 Author Share Posted February 2, 2012 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] Link to comment Share on other sites More sharing options...
water Posted February 2, 2012 Share Posted February 2, 2012 Let's see what we get started when we have finished our ExcelChart UDF My UDFs and Tutorials: Spoiler UDFs:Active Directory (NEW 2022-02-19 - Version 1.6.1.0) - Download - General Help & Support - Example Scripts - WikiExcelChart (2017-07-21 - Version 0.4.0.1) - Download - General Help & Support - Example ScriptsOutlookEX (2021-11-16 - Version 1.7.0.0) - Download - General Help & Support - Example Scripts - WikiOutlookEX_GUI (2021-04-13 - Version 1.4.0.0) - DownloadOutlook Tools (2019-07-22 - Version 0.6.0.0) - Download - General Help & Support - WikiPowerPoint (2021-08-31 - Version 1.5.0.0) - Download - General Help & Support - Example Scripts - WikiTask Scheduler (NEW 2022-07-28 - Version 1.6.0.1) - Download - General Help & Support - Wiki Standard UDFs:Excel - Example Scripts - WikiWord - Wiki Tutorials:ADO - WikiWebDriver - Wiki Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now