Excel Questions

[litlmike]

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

[GEOSoft]

I think for #2 it's _ExcelNumberFormat()

[seandisanti]

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"

[litlmike]

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?

[seandisanti]

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

[litlmike]

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

[seandisanti]

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.

[litlmike]

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 January 31, 2008 by litlmike

[litlmike]

Oh wait, I see now:

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

[seandisanti]

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"

[seandisanti]

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