Excel coding from Powershell.

[TimParker]

I am working on some reports that I am generating from my AD and
taking the output to Excel. I would like to "pretty up" the output a
bit and am trying to figure out the correct syntax. Anyone got any
good pointers? I haven't found anything yet.....

I have taken a sample sheet and created a macro that has my code
changes into it that I would like to add in while generating the file.
For example :

Range("A1:D1").Select
with selection
.horizontalalignment = xlcenter

[TimParker]

I need to slow down I guess some how I posted that and didn't even
realize it, and I wasn't finished! Sorry about that.

Range("A1:D1").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With

This selects 4 cells and merges them together so that my heading will
wrap across the top of the report. No matter what I try either the
code errors telling me that it is wrong, method or property or it will
continue on and not make any changes to the output.

This one is when I just wanted to put a border around a data cell
after selecting it. Seems like a lot of code to me....

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThin
End With
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

any pointers, directions? TIA.

Tim

[Marco Shaw [MVP]]

> any pointers, directions? TIA.
>
> Tim

I'm thinking this won't be pretty in PowerShell: To have to type this
line-by-line in the console or in a script!

What version of Office? If 2007, might be interesting to see how one
might accomplish this via OpenXML.

Marco

--
Microsoft MVP - Windows PowerShell
http://www.microsoft.com/mvp

PowerGadgets MVP
http://www.powergadgets.com/mvp

Blog:
http://marcoshaw.blogspot.com

[TimParker]

Thanks Marco, it hasn't been easy. I am slowly going through and using
GET-MEMBER a lot but its a slow process.

So far, I can now change the Font Styles on a cell or range of cells.
I only have about 3 or 4 things that I want/need to do to the data to
make it better looking so I am taking them now one at a time and
hammering through it. I will post the results once I am done, incase
anyone else finds the need later for it.

Tim

On Jun 25, 9:31 am, "Marco Shaw [MVP]" <marco.shaw@_NO_SPAM_gmail.com>
wrote:

[Marco Shaw [MVP]]

TimParker wrote:
> Thanks Marco, it hasn't been easy. I am slowly going through and using
> GET-MEMBER a lot but its a slow process.
>
> So far, I can now change the Font Styles on a cell or range of cells.
> I only have about 3 or 4 things that I want/need to do to the data to
> make it better looking so I am taking them now one at a time and
> hammering through it. I will post the results once I am done, incase
> anyone else finds the need later for it.

Ouch, you're more courageous than I... ;-)

Yes, please post your results after. Maybe there's a better way.

Marco

[Shay Levi]

Hi TimParker,

This is what I came up with so far. The Borders part is tricky since PowerShell
complains that the object doesn't contain a method named 'Borders', I don't
have much time to
play with this right now but there is another method you can check: BorderAround(),
type $selection.BorderAround to find more info:

$xlAutomatic=-4105
$xlAutomatic=-4105
$xlBottom = -4107
$xlCenter = -4108
$xlContext = -5002
$xlContinuous=1
$xlContinuous=1
$xlDiagonalDown=5
$xlDiagonalUp=6
$xlEdgeBottom=9
$xlEdgeLeft=7
$xlEdgeRight=10
$xlEdgeTop=8
$xlInsideHorizontal=12
$xlInsideVertical=11
$xlNone=-4142
$xlThin=2

$xl = new-object -com excel.application
$xl.visible=$true
$workbook = $xl.workbooks.open("d:\book1.xls")
$sheet = $workbook.worksheets | where {$_.name -eq "sheet1"}
$selection = $sheet.range("A1:D1")
$selection.select()

$selection.HorizontalAlignment = $xlCenter
$selection.VerticalAlignment = $xlBottom
$selection.WrapText = $false
$selection.Orientation = 0
$selection.AddIndent = $false
$selection.IndentLevel = 0
$selection.ShrinkToFit = $false
$selection.ReadingOrder = $xlContext
$selection.MergeCells = $false

---
Shay Levi
$cript Fanatic
http://scriptolog.blogspot.com

T> I need to slow down I guess some how I posted that and didn't even
T> realize it, and I wasn't finished! Sorry about that.
T>
T> Range("A1:D1").Select
T> With Selection
T> .HorizontalAlignment = xlCenter
T> .VerticalAlignment = xlBottom
T> .WrapText = False
T> .Orientation = 0
T> .AddIndent = False
T> .IndentLevel = 0
T> .ShrinkToFit = False
T> .ReadingOrder = xlContext
T> .MergeCells = False
T> End With
T> This selects 4 cells and merges them together so that my heading will
T> wrap across the top of the report. No matter what I try either the
T> code errors telling me that it is wrong, method or property or it
T> will continue on and not make any changes to the output.
T>
T> This one is when I just wanted to put a border around a data cell
T> after selecting it. Seems like a lot of code to me....
T>
T> Selection.Borders(xlDiagonalDown).LineStyle = xlNone
T> Selection.Borders(xlDiagonalUp).LineStyle = xlNone
T> With Selection.Borders(xlEdgeLeft)
T> .LineStyle = xlContinuous
T> .ColorIndex = xlAutomatic
T> .TintAndShade = 0
T> .Weight = xlThin
T> End With
T> With Selection.Borders(xlEdgeTop)
T> .LineStyle = xlContinuous
T> .ColorIndex = xlAutomatic
T> .TintAndShade = 0
T> .Weight = xlThin
T> End With
T> With Selection.Borders(xlEdgeBottom)
T> .LineStyle = xlContinuous
T> .ColorIndex = xlAutomatic
T> .TintAndShade = 0
T> .Weight = xlThin
T> End With
T> With Selection.Borders(xlEdgeRight)
T> .LineStyle = xlContinuous
T> .ColorIndex = xlAutomatic
T> .TintAndShade = 0
T> .Weight = xlThin
T> End With
T> Selection.Borders(xlInsideVertical).LineStyle = xlNone
T> Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
T> any pointers, directions? TIA.
T>
T> Tim
T>

[TimParker]

Thanks Shay.

I will add this into my testing also. How come all the variables up
front before you even create the sheet and do any work? Where did
those values come from?

I appreciate the time and effort you have put into this!

Tim

[TimParker]

Thanks Shay, this definately helped!

I only had to make one change to what you had, $selection.MergeCells =
$false needed changed to $true to merge the cells. In my VBA output it
was set to $false and there was another call to .merge and that
apparently is a difference in Powershell.

I had noticed the borderaround option and was going to mess with it
but got called away to work on a copier (less glamorous part of my
job). Back to the digging now!

Tim

> > $cript Fanatichttp://scriptolog.blogspot.com- Hide quoted text -
>
> - Show quoted text -

[urkec]

"TimParker" wrote:

You don't need to select a range to work with it's properties. Setting
individual borders worked, but I needed to use Borders.Item:

$sheet = $workbook.worksheets | where {$_.name -eq "sheet1"}

$range = $sheet.range("A1:A10")

$range.HorizontalAlignment = $xlCenter
$range.VerticalAlignment = $xlBottom
$range.Borders.Item($xlInsideHorizontal).Weight = $xlThin

--
urkec