Excel Column Format via PowerShell

[RobertK.]

I have searched the entire Interwebs and cannot find an answer for this
question...

How can I change the format of a column to either date or currency from
powershell?

I assume it is something like this:

$worksheet.Column(X,Y).property

I would alos like to know where to locate any documentation on how to
integrate PS and Excel.

Thanks in advance!

[Larry__Weiss]

Start out with this article...

http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept06/hey0908.mspx

- Larry

[RobertK.]

Larry, thanks for the reply. I'm looking for actual documentation, though.
I'm well beyond the simple tutorial.

For example in my current script I am setting the column width using:
$worksheet.columns.item('b').columnWidth = 15

So there must be a property that I can set to change the format to
"currency" or "date" but I just don't know what it is. When I open a new
workbook via the command line I can type $worksheet.columns.item('b'). and
hit tab but I get nothing. So I don't even know where to look.

[RobertK.]

$worksheet.columns.item('d').NumberFormat = "$#,##0.00"
$worksheet.columns.item('c').NumberFormat = "m/d/yyyy"

I just recorded a macro and hacked the code from that. I was not aware that
you could do that.

[Larry__Weiss]

Something like these?

http://msdn.microsoft.com/en-us/library/wss56bz7%28VS.80%29.aspx
http://msdn.microsoft.com/en-us/library/aa188489%28office.10%29.aspx

I don't know if even those are comprehensive.

- Larry

[OldDog]

On Aug 24, 5:08 pm, Larry__Weiss <l...@airmail.net> wrote:
> Something like these?
>
> http://msdn.microsoft.com/en-us/library/wss56bz7%28VS.80%29.aspxhttp://msdn.microsoft.com/en-us/library/aa188489%28office.10%29.aspx

>
> I don't know if even those are comprehensive.
>
>   - Larry
>
>
>
> RobertK. wrote:
> > Larry, thanks for the reply. I'm looking for actual documentation, though.
> > I'm well beyond the simple tutorial.
>
> > For example in my current script I am setting the column width using:
> > $worksheet.columns.item('b').columnWidth = 15
>
> > So there must be a property that I can set to change the format to
> > "currency" or "date" but I just don't know what it is. When I open a new
> > workbook via the command line I can type $worksheet.columns.item('b'). and
> > hit tab but I get nothing. So I don't even know where to look.
>
> > "Larry__Weiss" wrote:
>
> >> Start out with this article...
>

> >>http://www.microsoft.com/technet/scriptcenter/resources/qanda/sept06/...

>
> >>   - Larry
>
> >> RobertK. wrote:
> >>> I have searched the entire Interwebs and cannot find an answer for this
> >>> question...
>
> >>> How can I change the format of a column to either date or currency from
> >>> powershell?
>
> >>> I assume it is something like this:
>
> >>> $worksheet.Column(X,Y).property
>
> >>> I would alos like to know where to locate any documentation on how to
> >>> integrate PS and Excel.
>
> >>> Thanks in advance!

You can find some basic stuff at my Blog.

http://olddogsblog.spaces.live.com/default.aspx?wa=wsignin1.0&sa=808626389

Go all the way back to August of last year for my Excel Cookbook.

Mike