1

I'm creating an Excel spreadsheet dynamically from information in my database using ColdFusion.

I discovered that I can insert Excel formulas into cells and they will automatically be executed. I was wondering if it's possible to utilise Excel's 'Format as table' feature through a formula or macro, or is there another way to do this?

blarg
  • 3,773
  • 11
  • 42
  • 71
  • What does format as table mean? – Dan Bracuk May 01 '13 at 09:55
  • Excel styles the table rows in alternating colours and adds a colour scheme. The table headers become clickable allowing you to sort ascending and descending by the column. – blarg May 01 '13 at 10:05
  • It's probably not possible. If it was, somebody would have told you how to do it the last time you asked. – Dan Bracuk May 01 '13 at 13:35
  • To be honest, I have no idea what you're talking about. – blarg May 01 '13 at 13:58
  • 2
    I think @DanBracuk was referring to your other question here: http://stackoverflow.com/questions/16237648/format-cfspreadsheet-as-table – Miguel-F May 01 '13 at 14:47
  • It is not possible with CF's native functions. [This thread](http://stackoverflow.com/a/6123904/104223) suggests POI 3.8 added support for that feature, but CF9/10 use an older version (3.6 I think). So that will not help. A macro is probably your best bet. – Leigh May 01 '13 at 21:08

1 Answers1

1

You can do this through a macro like follows

Private Sub Workbook_Open()

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$10"), , xlNo).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight9"

End Sub

This will format cells A1 through D10 As a Table using the TableStyleLight9 style which is blue by default.

Sam Plus Plus
  • 4,381
  • 2
  • 21
  • 43
  • How could I get this to execute on loading the file? Also is there way to specify 'headers already included' – blarg May 01 '13 at 14:21
  • I changed the code to show it on Open. If you put it in a sub routine under ThisWorkbook in your vba project with the name signature Private Sub Workbook_Open() then it will run when the workbook is open. This created the Table with headers for me, but I think that has to do with your table style – Sam Plus Plus May 01 '13 at 14:28
  • I also find using the Macro Recorder http://office.microsoft.com/en-us/excel-help/record-and-use-excel-macros-HA001054837.aspx is a good place to start, you can easily customize a generated macro to meet your needs. – Sam Plus Plus May 01 '13 at 14:34