Jump to content
Sign in to follow this  

Creating an Excel 2007 Table

Recommended Posts


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 = _

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 by Shalm

Share this post

Link to post
Share on other sites

Better late than never :mellow: 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 by d4ni

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  


Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.