Hi All,
I'm having a bash with the automate features on the online version of Excel.
I have a table from a report where I'm trying to sort column K from oldest to newest. This is nice and easy to do, however, I would like to use the same script (it also has a few extra features like hiding unnecessary columns but there's no need to show that here) on other reports. Each report has the same format and the table is the same too.
I initially thought I could simply include a section of the code that changes the table name before sorting, but this, as shown, references the original name to do so. Is there a way of dynamically acquiring this table name so I can change it/not need to change it in the first place?
Code for changing the name:
Code for sorting the column with original name:
I'm also not adverse to a VBA solution - the automate function, however, does seem to be easier to share between multiple reports as and when they roll in
Thanks!
I'm having a bash with the automate features on the online version of Excel.
I have a table from a report where I'm trying to sort column K from oldest to newest. This is nice and easy to do, however, I would like to use the same script (it also has a few extra features like hiding unnecessary columns but there's no need to show that here) on other reports. Each report has the same format and the table is the same too.
I initially thought I could simply include a section of the code that changes the table name before sorting, but this, as shown, references the original name to do so. Is there a way of dynamically acquiring this table name so I can change it/not need to change it in the first place?
Code for changing the name:
JavaScript:
function main(workbook: ExcelScript.Workbook) {
let all_Act_19_11 = workbook.getTable("All_Act_19_11");
// Rename table all_Act_19_11 to Table
all_Act_19_11.setName("Table");
}
Code for sorting the column with original name:
JavaScript:
function main(workbook: ExcelScript.Workbook) {
let table = workbook.getTable("Table");
// Sort on table: table column index: '10'
table.getSort().apply([{key: 10, ascending: true}]);
}
I'm also not adverse to a VBA solution - the automate function, however, does seem to be easier to share between multiple reports as and when they roll in
Thanks!