Creating an Excel 2007 Table

[Shalm]

I am struggling to create an Excel Wookbook that I can dynamically build with complicated formulas based on constantly changing info. The way I am accomplishing this is by copying a blank template, filling in the needed formulas, then saving it to a different location/name. The snag I am running into is that I need to convert a range of fields to a sortable "Table," this is very important as some of the tables are huge and complicated to look through. It is very simple to do in a macro but security policies make it difficult to implement on the fly for non-tech savy people.

Here is what it looks like in the macro:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$D$4"), , xlYes).Name = _
        "Table1"

This is what I am using now to accomplish the task.. I would like to attach directly to the $oExcel so I can keep the file "hidden" until all the writing is done. There will be multiple "tables" built within all the pages and the process takes a while! It would be good not to have flickering screens and stuff... I suppose I could lock out the input and give them a pretty show at the end while I initialize all this, but...

$oExcel = _ExcelBookOpen(@ScriptDir&"\Test1.xlsx")
$oExcel.ActiveSheet.Range($oExcel.Cells(1,1), $oExcel.Cells(10,4)).Select

;After this is what I would like to re-write
ControlSend("Microsoft Excel - Test1.xlsx", "","","!n")
ControlSend("Microsoft Excel - Test1.xlsx", "","","t")
WinWait("Create Table")
ControlSend("Create Table", "","","!m")
ControlSend("Create Table", "","","{enter}")

Thanks.

Is this just futility and insanity to think this is possible? I think it could become a nice addition to the Excel UDF if we could figure this out.

Any help would be greatly appreciated. Thanks.

Edit: Combined, re-worded post.

Edited July 15, 2009 by Shalm

[dani]

Better late than never I figured out how to create an Excel table, it's actually pretty easy (like most things are once figured out):

#include <Excel.au3>

$e = _ExcelBookNew()

_ExcelWriteCell($e, 1, "A1")
_ExcelWriteCell($e, 2, "A2")
_ExcelWriteCell($e, 3, "A3")
_ExcelWriteCell($e, 4, "A4")

$r = $e.Range("A1:A4")
$e.ActiveSheet.ListObjects.Add(1, $r)
; or, without using an extra object to hold the Range: 
; $e.ActiveSheet.ListObjects.Add(1, $e.Range("A1:A4"))

As you can see I'm using the Excel UDF which is included with Autoit. I wonder why there is no _ExcelCreateTable() function though, as it is insanely easy to create one. See for full documentation on the ListObjects.Add method this page @ msdn

Edited February 18, 2010 by d4ni