So how do you get from the list to setting up the table collection? Well, you open the AOT and add each and every table name manually to the table collection. I went through this tedious process today with a customer and when I got home I wrote the following small job to automate this task next time.
This is a very simple job, reading the first row of the spreadsheet, adding what is assumed to be table names to a table collection. As you can see, the job is in "proof on concept" state, so you'll have to add all the bells and whistles yourself.
static void ImportTableCollectionsFromExcel(Args _args)
{
#AOT
// Import from Excel
SysExcelApplication excelApp;
SysExcelWorkbooks workBooks;
SysExcelWorkSheets workSheets;
SysExcelWorkSheet workSheet;
SysExcelCells cells;
SysExcelCell cell;
FileName fileName;
Counter row;
// Create the new treenodes
TreeNodeName tableCollectionName = 'ImportedCollection2';
TreeNode treeNode;
TreeNodePath treeNodePath = #TableCollectionsPath+'\\'+tableCollectionName;
TreeNodeName treeNodeName;
;
setPrefix(treeNodePath);
// Create the table collection if it doesn't exist
treeNode = TreeNode::findNode(treeNodePath);
if (!treeNode)
{
treeNode = TreeNode::findNode(#TableCollectionsPath).AOTadd(tableCollectionName);
treeNode.AOTsave();
}
excelApp = SysExcelApplication::construct();
fileName = excelApp.getOpenFileName();
workBooks = excelApp.workbooks();
workBooks.open(filename);
workSheets = excelApp.worksheets();
workSheet = workSheets.itemFromNum(1);
cells = workSheet.cells();
row = 1;
cell = cells.item(1,1);
while (cell.value().bStr() != '')
{
treeNodeName = cell.value().bStr();
treeNode = TreeNode::findNode(treeNodePath+'\\'+treeNodeName);
if (!treeNode)
{
if (treeNode::findNode(#TablesPath+'\\'+treeNodeName))
{
info (treeNodeName);
treeNode = TreeNode::findNode(treeNodePath).AOTadd(treeNodeName);
treeNode.AOTsave();
}
}
row++;
cell = cells.item(row,1);
}
excelApp.quit();
treeNode = TreeNode::findNode(treeNodePath);
treeNode.AOTsave();
}