Excel automation (adding a table style)

PowerShell Help
1

Hi All,

Does anyone know how to add a table style to a spreadsheet in Excel 2010?

Essentially, I want to select the used range, click ‘Format as table’ and then select ‘Table Style Medium 12’ (and my table has headers).

I’ve been tinkering with it for a good part of the weekend, but can only get it to appear on one column!

(Girlfriend’s about to thump me so if anyone can save me from a beating I’d be really grateful…)

Thanks.

TG

2

According to the Macro Recorder, something like this (this is in VB, of course; you’ll need to adjust the syntax for PowerShell):

Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$21"), , xlYes).Name = _
        "Table1"
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium12"
End Sub
3

Dave - you’re a gem! Got my script finished and it’s saving me soooooo much manual work!

Cheers buddy - someone needs to create an ‘Add-Beer’ cmdlet.