Excel Office Script voorbeelden
Inhoud
Inleiding
Sinds Juli 2020 kunnen Microsoft 365 gebruikers hun acties in Excel
laten opnemen als Office Script macro's. Eerder plaatste ik al
een artikel dat laat zien hoe
je hiermee start. Op deze pagina toon ik wat voorbeelden van Office
scripts die je niet kunt opnemen met de Script recorder, maar die wel nuttig
zijn.
Sommige van deze scripts bevatten letterlijke waarden, (de waarden tussen
aanhalingstekens). Dit betekent, dat zaken als namen van werkbladen hard-coded
in de voorbeelden staan: "ToC". Wil je dus een andere naam gebruiken, dan
zal je het script moeten bewerken. Omdat Microsoft ons nog geen mogelijkheden
heeft gegeven om een waarde op te vragen bij de gebruiker, zullen we voor
nu dit soort invoerwaarden zelf in onze scripts moeten opnemen helaas.
Download
de volledige code (zip bestand met alle scripts) hier.
Lege cellen vullen met een waarde
Stel je wil alle lege cellen in de selectie vullen met het cijfer 0:
Deze code doet dat voor je
function main(workbook: ExcelScript.Workbook) {
// Your code here
let emptyAreas = workbook.getSelectedRange().getSpecialCells(ExcelScript.SpecialCellType.blanks);
emptyAreas.getAreas().forEach(r => {
r.setValue(0);
});
}
Laatst gebruikte rij
De functie getLastRow die hieronder staat, geeft je de laatst gebruikte
rij van een kolom (ervan uitgaande dat de cel op rij 1048576 leeg is)
function main(workbook: ExcelScript.Workbook) {
const sht = workbook.getWorksheet("Sheet2");
const lastRow = getLastRow(sht, sht.getRange("H:H"));
console.log(lastRow);
sht.getRange("H2:H" + lastRow).select();
}
function getLastRow(sht: ExcelScript.Worksheet, rng: ExcelScript.Range) {
let lastCell = sht.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
return lastCell.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
}
Een inhoudsopgave toevoegen
aan je werkmap
Werk je in Excel Online en heb je snel een inhoudsopgave nodig in
je werkmap? Hier is een Office Script waarmee je er een genereert. Dit script
maakt een lijst van alle werkbladen en van alle grafieken, met snelkoppelingen
naar hun locaties.
Eerst toon ik hier de "main" functie en geef ik een stapsgewijze uitleg:
function main(workbook: ExcelScript.Workbook) {
if (workbook.getWorksheet("ToC")) {
let temp = workbook.getWorksheet("ToC")
temp.delete();
}
let allSheets = workbook.getWorksheets();
let tocSheet = workbook.addWorksheet("ToC");
let reportCell = tocSheet.getRange("B2");
let newTable = addTocTable("Worksheets", workbook, reportCell);
reportCell = reportCell.getOffsetRange(2, 0);
reportCell.getResizedRange(0, 1).setValues([["Worksheet", "Link"]]);
for (let i = 0; i < allSheets.length; i++) {
reportCell.getOffsetRange(i + 1, 0).setValue(allSheets[i].getName());
}
reportCell.getOffsetRange(1, 1).setFormulaR1C1("=HYPERLINK(\"#'\"&RC[-1]&\"'!A1\",RC[-1])");
reportCell = reportCell.getOffsetRange(newTable.getRowCount() + 2, 0)
newTable = addTocTable("Charts", workbook, reportCell);
reportCell = reportCell.getOffsetRange(2, 0)
reportCell.getResizedRange(0,3).setValues([["Worksheet", "Name", "Location", "Link"]]);
let k = 0;
for (let i = 0; i < allSheets.length; i++) {
let allCharts = allSheets[i].getCharts();
for (let j = 0; j < allCharts.length; j++) {
k++;
let theSheet = allSheets[i];
reportCell.getOffsetRange(k, 0).setValue(allSheets[i].getName());
reportCell.getOffsetRange(k, 1).setValue(allCharts[j].getName());
let topLeftCell = getCellUnderChart(allCharts[j], allSheets[i]);
let topLeftAddress = topLeftCell.getAddress();
if( topLeftAddress.charAt(0)=="'"){
topLeftAddress="'" + topLeftAddress;
}
reportCell.getOffsetRange(k, 2).setFormula(topLeftAddress);
}
}
reportCell.getOffsetRange(1, 3).setFormulaR1C1("=HYPERLINK(\"#\"&RC[-1],\"Link\")");
tocSheet.activate();
}
Dit zijn de stappen die deze code uitvoert::
- Controleer of er een werkblad genaamd ToC is, zo ja, verwijder het
werkblad;
- Haal een lijst met alle werkbladen open stop de lijst in de variabele
allSheets;
- Voeg een werkblad to met de naam "ToC" en ken dit blad toe aan de
variabele tocSheet;
- Ken cel B2 toe aan de variabele reportCell;
- Voeg een tabel toe aan de inhoudsopgave middels de functie addTocTable
(zie hieronder);
- Om ervoor te zorgen dat we beginnen met het toevoegen van bladnamen
op de juiste rij, verschuiven we reportCell twee rijen zodat deze ny
wijst naar cel B4;
- We voegen de koppen toe aan de nieuwe tabel (Worksheet en Link);
- Nu is het tijd om door alle werkbladen in de allSheets collectie
te loopen en iedere naam in te vullen in de eerste kolom van onze tabel;
- Nadat we alle werkbladnamen in de eerste kolom hebben gezet gebruiken
we de HYPERLINK functie in de tweede kolom om, gebruik makend van de
bladnamen uit de eerste kolom, een snelkoppeling naar ieder tabblad
te maken;
- We verschuiven de reportCell positie met het aantal rijen in onze
tabel (plus twee extra rijen om ruimte te maken);
- Nu voegen we de koptekst voor de grafieken toe en een lege tabel,
wederom gebruik makend van de functie addTocTable;
- De Grafieken tabel kent vier kolommen: Worksheet, Name, Location
en Link;
- We lopen alle werkbladen af en voor ieder werkblad:
- loopen we door alle aanwezige grafieken;
- vullen we de grafieken tabel met de juiste informatie: Werkblad
naam, Garfiek naam en Locatie (om de locatie van een grafiek te
krijgen moest ik een nieuwe functie schrijven genaamd getCellUnderChart
omdat Office Script geen TopLeftCell eigenschap kent, zoals het
grafiek in VBA wel heeft);
- Er bleek een probleempje bij grafieken op tabbladen met een
spatie in de naam; het juiste adres van de topleftCell in werkbladen
die apostrofjes om hun naam krijgen is: 'Blad 2'!F1. Echter, als
je 'Blad 2'!F1 in een cel zet, dan zal Excel die eerste apostrof
verwijderen. Vandaar het if statement dat in zulke gevallen een
extra apostrof toevoegt.
- Nadat we alle grafieken op alle werkblden hebben gehad plaatsen
we wederom middels de HYPERLINK functie een snelkoppeling naar de plaats
van de grafiek, gebruik makend van de adresinformatie in de derde kolom.
De main functie gebruikt twee hulpfuncties, hier is de addTocTable functie:
function addTocTable(caption: String, workbook: ExcelScript.Workbook, location: ExcelScript.Range): ExcelScript.Table {
//1. adds a caption in reportCell and formats that as Heading 1
//2. Adds a table below reportCell to hold ToC entries
//Returns: the table
location.setValue(caption);
location.getResizedRange(0, 1).setPredefinedCellStyle(ExcelScript.BuiltInStyle.heading1);
location = location.getOffsetRange(2, 0);
let newTable = workbook.addTable(location, true);
return newTable;
}
Het enige dat deze functie doet is het invullen van een koptekst in de
cel 'location', deze cel de stijl "Heading 1" geven en een neiuwe tabel
toevoegen. De functie geeft de tabel als object door aan de aanroepende
functie zodat we dat object daarin verder kunnen gebruiken.
Je kunt een stijl ook toevoegen met haar naam zoals je deze
in de interface ziet. Doe dit alleen voor stylen die je zelf hebt gemaakt,
anders krijg je mogelijk een foutmelding als iemand een andere taalversie
van Excel gebruikt.
En hier is de getCellUnderChart functie:
function getCellUnderChart(cht: ExcelScript.Chart, ws: ExcelScript.Worksheet): ExcelScript.Range {
let topLeftCell = ws.getRange("A1");
do {
topLeftCell = topLeftCell.getOffsetRange(0, 1)
}
while (topLeftCell.getLeft() < cht.getLeft());
do {
topLeftCell = topLeftCell.getOffsetRange(1, 0)
}
while (topLeftCell.getTop() < cht.getTop());
return topLeftCell.getOffsetRange(-1, -1);
}
Het basisprincipe is simpel: de functie doorloopt eerst alle cellen van
het werkblad in horizontale righting, beginnend bij cel A1, totdat de Left
waarde van die cel groter is dan de Left positie van de grafiek. Vervolgens
wordt hetzelfde gedaan, maar dan in vertikale richting om cel met dezelfde
de Top positie als de grafiek te vinden. Omdat deze methode ons eigenlijk
een cel teveel naar rechts en naar omlaag geeft, verschuiven we de gevonden
cel één cel omhoog en naar links (-1, -1) zodat we de juiste cel als linkerbovenhoek
teruggeven als functieresultaat.
Een lijst met bereiknamen genereren
Desktop Excel heeft een eenvoudige optie om een lijst met alle bereiknamen
te krijgen (Formules tab, Gebruik in formule vervolgkeuzelijst, Lijst plakken).
Maar Excel on-line kent deze functionaliteit niet. Hier is een Office script
dat een werkblad genaamd "Names List" toevoegt aan je bestand waarin een
lijst wordt geplaatst met alle bereiknamen en enkele eigenschappen.
function main(workbook: ExcelScript.Workbook) {
var RowCt = 1;
var sheetName = "";
let MyNames = workbook.getNames();
let selectedSheet = workbook.getWorksheet("Names list");
if (selectedSheet) {
selectedSheet.delete();
}
let worksheets = workbook.getWorksheets();
selectedSheet = workbook.addWorksheet("Names list");
selectedSheet.getRange("B2:E2").setValues([["Scope", "Name", "RefersTo", "Visible"]]);
for (let i = 0; i < MyNames.length; i++) {
RowCt++
selectedSheet.getCell(RowCt, 1).getResizedRange(0, 3).setValues([
["Workbook", MyNames[i].getName(), "'" + MyNames[i].getFormula(), "'" + MyNames[i].getVisible()]]);
}
for (let i = 0; i < worksheets.length; i++) {
sheetName = worksheets[i].getName();
var myNames = worksheets[i].getNames();
for (let j = 0; j < myNames.length; j++) {
RowCt++;
selectedSheet.getCell(RowCt, 1).getResizedRange(0, 3).setValues([
[sheetName, myNames[j].getName(), "'" + myNames[j].getFormula(), "'" + myNames[j].getVisible()]
]);
}
}
let newTable = workbook.addTable(selectedSheet.getRange("B2").getSurroundingRegion(), true);
newTable.getRange().getFormat().autofitColumns();
}
De resulterende tabel ziet er ongeveer zo uit:
Een tabel filteren op datum
Stel dat je een tabel hebt met een datumkolom en je wilt die tabel filteren
op data die ouder zijn dan vandaag.
Om dit mogelijk te maken, moet je het juiste datum formaat weten dat
Excel momenteel gebruikt, zodat je de systeemdatum kunt omzetten in een
tekstwaarde die er hetzelfde uitziet als in Excel. Daarvoor heb je de datumopmaakcode
nodig die Excel momenteel gebruikt; is dat mm/dd/yyyy of dd-mm-jjjj? Hiervoor
maak je gebruik van de zogeheten cultureInfo van de Excel applicatie:
workbook.getApplication().getCultureInfo().getName()
Dit geeft je een tekstwaarde in de vorm zoals en-US of (in mijn geval)
nl-NL. Deze tekstwaarde kan vervolgens gebruikt worden om de systeemdatum
om te zetten in een tekstwaarde in het juiste formaat.
Om de tabel te filteren op datums voor vandaag, gebruik je code zoals
deze:
function main(workbook: ExcelScript.Workbook) {
let table1 = workbook.getTable("Tabel1");
table1.getAutoFilter().clearCriteria();
//Get date in local format
const date = new Date().toLocaleDateString(workbook.getApplication().getCultureInfo().getName());
//Apply filter to 'Table1' column 'datum'
table1.getColumnByName("datum").getFilter().applyCustomFilter("<" + date);
}
Hoewel het ook werkt als je gebruik maakt van het IDO datum format:
function main(workbook: ExcelScript.Workbook) {
let table1 = workbook.getTable("Tabel1");
table1.getAutoFilter().clearCriteria();
//Get date in ISO format
const date = new Date().toISOString().slice(0,10);
//Apply filter to 'Table1' column 'datum'
table1.getColumnByName("datum").getFilter().applyCustomFilter("<" + date);
}
Download
Download
de volledige code (zip bestand met alle scripts) hier.