Sign in to follow this  
Followers 0
litlmike

Excel Questions

11 posts in this topic

A few Excel based questions, that I can't find the answers to in the ExcelCOM.UDF

1) How do I format a cell to be in date format (MM/DD/YY)

2) How do I format a cell to be in number format, no decimals

TIA

Share this post


Link to post
Share on other sites



I think for #2 it's _ExcelNumberFormat()


George

Question about decompiling code? Read the decompiling FAQ and don't bother posting the question in the forums.

Be sure to read and follow the forum rules. -AKA the AutoIt Reading and Comprehension Skills test.***

The PCRE (Regular Expression) ToolKit for AutoIT - (Updated Oct 20, 2011 ver:3.0.1.13) - Please update your current version before filing any bug reports. The installer now includes both 32 and 64 bit versions. No change in version number.

Visit my Blog .. currently not active but it will soon be resplendent with news and views. Also please remove any links you may have to my website. it is soon to be closed and replaced with something else.

"Old age and treachery will always overcome youth and skill!"

Share this post


Link to post
Share on other sites

A few Excel based questions, that I can't find the answers to in the ExcelCOM.UDF

1) How do I format a cell to be in date format (MM/DD/YY)

2) How do I format a cell to be in number format, no decimals

TIA

I prefer COM

$myex = ObjGet("","excel.Application")
MsgBox(0,"Instructions","Select the cells you wish to be formated as dates, then click ok.")
$myex.selection.NumberFormat = "mm/dd/yy;@"
MsgBox(0,"Instructions","Select the cells you wish to be formated as integerss, then click ok.")
$myex.selection.NumberFormat = "0"

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

I prefer COM

$myex = ObjGet("","excel.Application")
MsgBox(0,"Instructions","Select the cells you wish to be formated as dates, then click ok.")
$myex.selection.NumberFormat = "mm/dd/yy;@"
MsgBox(0,"Instructions","Select the cells you wish to be formated as integerss, then click ok.")
$myex.selection.NumberFormat = "0"
Hmmm, this might work, how do I do this for 1 cell, as opposed to highlighting a selection first?

Share this post


Link to post
Share on other sites

Hmmm, this might work, how do I do this for 1 cell, as opposed to highlighting a selection first?

you can do it for any range, but the range is a member of the worksheet object instead of the application object, so it requires a little more code, this does the same for cell a1 on the active sheet, pausing w/ message box after each transition

$myex = ObjGet("","excel.Application")
$mywb = $myex.activeworkbook
$myws = $mywb.activesheet
$myws.range("a1").NumberFormat = "mm/dd/yy;@"
MsgBox(0,"Instructions","formated as dates")
$myws.range("a1").NumberFormat = "0"
MsgBox(0,"Instructions","formated as integers")

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

you can do it for any range, but the range is a member of the worksheet object instead of the application object, so it requires a little more code, this does the same for cell a1 on the active sheet, pausing w/ message box after each transition

$myex = ObjGet("","excel.Application")
$mywb = $myex.activeworkbook
$myws = $mywb.activesheet
$myws.range("a1").NumberFormat = "mm/dd/yy;@"
MsgBox(0,"Instructions","formated as dates")
$myws.range("a1").NumberFormat = "0"
MsgBox(0,"Instructions","formated as integers")
Excellent... this works wonderfully.

1) How do I display the number with 2 decimal places.

$myws.range("a1").NumberFormat = "0"; returns 0 decimals

$myws.range("a1").NumberFormat = "1"; returns the number 1 ?!?

$myws.range("a1").NumberFormat = "1"; returns the number 2 !?!

2) How do I find out the COM info you provided? I assume you must have a reference that you use.

Thanks

Share this post


Link to post
Share on other sites

Excellent... this works wonderfully.

1) How do I display the number with 2 decimal places.

$myws.range("a1").NumberFormat = "0"; returns 0 decimals

$myws.range("a1").NumberFormat = "1"; returns the number 1 ?!?

$myws.range("a1").NumberFormat = "1"; returns the number 2 !?!

2) How do I find out the COM info you provided? I assume you must have a reference that you use.

Thanks

instead of "0" use "0.00" the biggest reference that i use is the object browser in the vba interface (press alt+f11 to get to vba, then F2 to bring up object browser) and when all else fails and you can't find an object or how to interact with it, record a macro of doing what you want to do, then go in and pull the macro apart, that will give you the objects you're affecting, and the properties being set and the methods called to do the work.

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

#8 ·  Posted (edited)

instead of "0" use "0.00" the biggest reference that i use is the object browser in the vba interface (press alt+f11 to get to vba, then F2 to bring up object browser) and when all else fails and you can't find an object or how to interact with it, record a macro of doing what you want to do, then go in and pull the macro apart, that will give you the objects you're affecting, and the properties being set and the methods called to do the work.

Thanks again! Your little macro/object browser method is very cool. How do I modify this, from the Macro Editor, to work with AutoIt syntax?

Columns("F:F").Select

Selection.NumberFormat = "#,##0"

Thanks

Edited by litlmike

Share this post


Link to post
Share on other sites

Thanks again! Your little macro/object browser method is very cool. How do I modify this, from the Macro Editor, to work with AutoIt syntax?

Columns("F:F").Select

Selection.NumberFormat = "#,##0"

Thanks

first, the object browser will show you that the columns collection is a member of the worksheet object, and the selection object is under application; so using the code above where i set objects for the application, workbook, and worksheet objects as a start, your code for these two lines would be:

$myws.columns("F:F").select
$myex.selection.numberformat = "#,##0"

1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

Share this post


Link to post
Share on other sites

Oh wait, I see now:

$oExcel.range("V:V").NumberFormat = "#,##"

that works also, but as a convention i try to reference as far down the object model as i can, opting to reference ranges from worksheets instead of the application to reduce the risk of misreferencing. One 'fun' thing about using macros or COM to control excel is that you cannot undo scripted actions, so making a change to the wrong object can really mess up your day if you haven't saved recently. a really quick reference to the objects i use most would be

Application -> Workbooks (collection of open workbooks referenced by index or literal string name; ActiveWorkbook; Selection(range object) -> Worksheets (collection of sheets in selected workbook, referenced by index or literal string name); ActiveSheet -> Range

i know that's kind of messy looking but i hope it helps. a few quick tips to remember:

1) selecting cells - for multiple cells, use range().select for a single cell use range().activate

2) the easiest way to specify a value for a cell is to drop it into the range().formula property, while reading from the same cell may require you to use .formula(to read a text value, or a literal string containing a formula),value(the grab the result of a formula),or value2 or text (to grab the displayed value after formatting)

3) If you have an exit condition in your script, make sure it is proceeded by a $oExcel.quit when you've used ObjCreate() because with excel loaded in the background, exiting the script does not always (usually doesn't in fact) exit the excel application, and you can end up with several instances running invisible in the background tying up files etc that you have to close manually

4) If you create you own excel object instead of grabbing one, you can use the Application.Visible property to make it visible

Those are the basics that you will definitely run into trying to automate excel. Outlook has a lot more little quirks


1100111 00001011101111 00011101101111 00010111100100 00001111110100 00110111110010 00101101111001 0011100i didn't make up this form of encryption, but i like it.credit to the lvl 6 challenge on arcanum.co.nz

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