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:

Bereik met lege cellen

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());
  if (lastCell.getValue().toString().length > 0) {
    return "Last row on sheet filled"
  } else {
  return lastCell.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
  }
}

Merk op dat dit script tekst geeft in plaats van een getal als de laatste cel in de kolom een waarde bevat. Dit zal een foutmelding veroorzaken in de routine die het script aanroept waar je je op moet voorbereiden

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:
List of range names

De gebruiker om invoer vragen

OfficeScript geeft momenteel geen methode om een gebruikers-interface te maken om om invoer te vragen. Recentelijk heeft Microsoft wel een manier gemaakt om dat probleem op te lossen. Als je je script voorziet van parameters, dan zal Excel een klein invoervenster tonen waarin om deze waarden wordt gevraagd. Als je de parameter namen voorziet van hoofd- en kleine letters, dan zal Excel voor iedere hoofletter een spatie voorzien. Neem dit script als voorbeeld:

function main(workbook: ExcelScript.Workbook, StartDate:string, EndDate:string) {
    console.log(StartDate);
    console.log(EndDate);
}

Het eerste argument van de functie is altijd de werkmap waar het script op wordt uitgevoerd. De overige parameters zal naar worden gevraagd. Dus als je dit script uitvoert, dan wordt dit venster getoond:

A prompt for input

Er zit enige intelligentie in de gegevenstypen die je instelt. Voor een boolean parameter toont Excel bijvoorbeeld automatisch een keuzelijst met true en false. Ik heb nog geprobeerd om StartDate en EndDate parameters als "Date" in te stellen, maar kennelijk is dat geen valide gegevenstype voor TypeScript. Ik hoopte dat Excel dit zou herkennen en dan een datumkiezer zou bieden, maar dit gaat dus kennelijk niet. Nu ja, in ieder geval hebben we een manier om om invoer te vragen.

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.

Een datum kolom in een tabel

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.

Vragen, suggesties en opmerkingen

Heeft u vragen, suggesties of opmerkingen? Gebruik dan dit formulier.

Mocht uw vraag niet direct relevant zijn voor deze pagina, maar een algemene Excel vraag betreffen, dan adviseer ik om deze hier te stellen: excelexperts.nl/forum/index.php.




Als u VBA code in uw commentaar plaatst, gebruik dan [VB] tags: [VB]Uw code[/VB].