Excel automation (adding a table style)

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



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 = _
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleMedium12"
End Sub

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.