Shalm Posted July 7, 2009 Share Posted July 7, 2009 (edited) 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 Link to comment Share on other sites More sharing options...
dani Posted February 18, 2010 Share Posted February 18, 2010 (edited) 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 Link to comment Share on other sites More sharing options...
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now