VBA aanpassen naar script voor Excel Online

[vrouw]

Hallo,

Ik kon geen aparte sectie op het forum vinden voor excel online vragen dus ik hoop dat het hier mag.

Ik heb een klein stukje VBA code die ik gebruik om regels die een datum van vandaag of ouder hebben te verplaatsen naar een ander tabblad,
Omdat het bestand nu naar de Excel Online omgeving moet werkt die macro niet en moet het omgezet worden naar een 'script'

kan iemand mij daar mee helpen?

Private Sub Workbook_Open()

With Sheets("2024").Cells(1).CurrentRegion
    .AutoFilter 1, "<" & CLng(Date)
        .Offset(1).Copy Sheets("Verleden 2024").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            .Offset(1).EntireRow.Delete
            .AutoFilter
End With

End Sub

 

[jkpieterse]

Je kunt in Excel on-line ook script opnemen, begin daar eens mee.

 

[vrouw]

Oke, dat wist ik niet, maar ik snap er niks van om de voorwaarde van de datum daarin te gebruiken, sorry

 

[vrouw]

Ik heb dit opgenomen maar dat is dan zonder de voorwaarde van de datum (kolom A) dat die vandaag of verder in het veleden is.

function main(workbook: ExcelScript.Workbook) {

    let verleden_2024 = workbook.getWorksheet("Verleden 2024");

    let selectedSheet = workbook.getActiveWorksheet();

    // Paste to range 38:38 on verleden_2024 from range 2:2 on selectedSheet

    verleden_2024.getRange("38:38").copyFrom(selectedSheet.getRange("2:2"), ExcelScript.RangeCopyType.all, false, false);

}

 

[vrouw]

Als ik me eerst focus om de autofilter op "kleiner dan vandaag" te zetten krijg ik de optie niet om een "date filter" in te stellen. (slechts alleen een "Text filter' optie)

 

[JEC.]

Het is dus tekst. Zorg ervoor dat het echte datums zijn

 

[vrouw]

Top, nu zijn het datums.

Maar dit werkt niet zo?

 

[jkpieterse]

Je zal een "echte" datum in moeten vullen tijdens de opname van het script en dan in het script ervoor moeten zorgen dat deze datum dynamisch wordt. Het bleek nog niet eenvoudig dit script te schrijven. Ik kwam hier op uit:

function main(workbook: ExcelScript.Workbook) {
  let table1 = workbook.getTable("Table1");
  // Apply custom filter on table table1 column "datum"
  const year = new Date().getFullYear();
  const month = new Date().getMonth();
  const day = new Date().getDate();
 
  table1.getColumnByName("datum").getFilter().applyCustomFilter("<" + day + "-" + month + "-" + year);
  let verleden_2024 = workbook.getWorksheet("Verleden 2024");
  const dataRange = table1.getRangeBetweenHeaderAndTotal();
  const visibleAreas = dataRange.getSpecialCells(ExcelScript.SpecialCellType.visible);
  verleden_2024.getRange("A" + verleden_2024.getRange().getRowCount()).getRangeEdge(ExcelScript.KeyboardDirection.up).getOffsetRange(1,0).copyFrom(visibleAreas)
 
  let areas=visibleAreas.getAreas();
  for (let i = areas.length - 1; i >= 0; i = i - 1)
  {
    areas[i].getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
  }
  table1.getAutoFilter().clearCriteria();
}

 

[jkpieterse]

Hier is een verbeterde versie die ook in internationale setting zou moeten werken:

function main(workbook: ExcelScript.Workbook) {
  let table1 = workbook.getTable("Table1");
  
  //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);
  let verleden_2024 = workbook.getWorksheet("Verleden 2024");
  const dataRange = table1.getRangeBetweenHeaderAndTotal();
  const visibleAreas = dataRange.getSpecialCells(ExcelScript.SpecialCellType.visible);
  verleden_2024.getRange("A" + verleden_2024.getRange().getRowCount()).getRangeEdge(ExcelScript.KeyboardDirection.up).getOffsetRange(1,0).copyFrom(visibleAreas)
  
  let areas=visibleAreas.getAreas();
  for (let i = areas.length - 1; i >= 0; i = i - 1)
  {
    areas[i].getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
  }
  table1.getAutoFilter().clearCriteria();
}

 

[vrouw]

@jkpieterse
WAUW ik ben zo blij met je hulp want ik ga hier echt niet zelf uitkomen vrees ik.

Ik krijg echter wel een foutmelding.

 

[vrouw]

Oke, de } achter regel 17 gezet en dan verdwijnt die error.

Echter met het runnen komt dan deze error:

 

[jkpieterse]

Mogelijk gaat er iets mis met het kopieren van de code naar jouw script?
Probeer eens alle regel-eindes 1 voor 1 te verwijderen en dan op die plekken op Enter te drukken. Daarna rechts-klikken en "Format document" kiezen om het inspringen te fixen.

 

[vrouw]

Ik had dus een andere Tablename, dus die aangepast in het script. Dat er nu zo uitziet.

function main(workbook: ExcelScript.Workbook) {
    let table1 = workbook.getTable("Table1438")

    //Get date in local format
    const date = new Date().toLocaleDateString(workbook.getApplication().getCultureInfo().getName())
    //Apply filter to 'Table1' column 'datum'
    table1.getColumnByName("Start evenement").getFilter().applyCustomFilter("<" + date)
    let verleden_2024 = workbook.getWorksheet("Verleden 2024")
    const dataRange = table1.getRangeBetweenHeaderAndTotal()
    const visibleAreas = dataRange.getSpecialCells(ExcelScript.SpecialCellType.visible)
    verleden_2024.getRange("A" + verleden_2024.getRange().getRowCount()).getRangeEdge(ExcelScript.KeyboardDirection.up).getOffsetRange(1, 0).copyFrom(visibleAreas)
    let areas = visibleAreas.getAreas()
    for (let i = areas.length - 1; i >= 0; i = i - 1) {
        areas[i].getEntireRow().delete(ExcelScript.DeleteShiftDirection.up)
    }
    table1.getAutoFilter().clearCriteria()}

Na het runnen verdwijnen alle regels van de sheet, De regels hebben allemaal een datum in de toekomst die zouden dus moeten blijven staam. 1 regel is in het verleden maar ook die is weg.

Deze error is er nog wel steeds,
Line 12: Cannot read properties of undefined (reading 'getAreas')

 

[jkpieterse]

De uitdaging zit hem in het juist filteren van de tabel denk ik. Als je alles na het filteren eens voorziet van commentaar (// vooraan in de regel zetten), welke datum (in welk exact formaat) staat er dan in het filter ingesteld als je de code uitvoert? En welke datum in het filter (in welk formaat precies) geeft wel het resultaat dat je wil hebben als je het filter handmatig instelt?
Die foutmelding komt omdat er geen zichtbare rijen zijn na het huidige filteren, daar zouden we voor kunnen testen.
NB: de gegevens in de tabel, zijn dat "echte" datumwaarden? Of denkt Excel wellicht dat het tekst is?

 

[JEC.]

Open in desktop app, om daar even je macro te runnen, is niet meer mogelijk?
Office scripts zijn gewoon (nog) niet goed genoeg...

 

[vrouw]

Op de een of andere manier waren de datums toch weer als tekst formaat in de sheet gekomen.
Nadat ik het aangepast had werkt het script wel

Echter had ik het in de VBA macro dat die ging runnen met het openen (dus je hoefde de macro niet handmatig te starten)

is dat ook mogelijk?

 

[jkpieterse]

Dat kan helaas (nog) niet. Wel kan je een knop toevoegen op je tabblad die het script uitvoert:

Excel Office Script De Basis Uitgelegd

jkp-ads.com: Excel Office Script Basics

jkp-ads.com

 

[vrouw]

Ik heb het idd gevonden om een knop te maken @jkpieterse.

Ik loop alleen nog tegen het probleem aan dat wanneer er GEEN datums zijn die in het verleden zijn het script een error geeft.
Alles op de sheet is dan ook weg gefilterd.

Line 12: Cannot read properties of undefined (reading 'getAreas')

 

[jkpieterse]

Dat is als volgt op te lossen:

function main(workbook: ExcelScript.Workbook) {
  let table1 = workbook.getTable("Table1");
  // Apply custom filter on table table1 column "datum"
  const year = new Date().getFullYear();
  const month = new Date().getMonth();
  const day = new Date().getDate();
  table1.getColumnByName("datum").getFilter().applyCustomFilter("<" + day + "-" + month + "-" + year);
  let selectedSheet = workbook.getActiveWorksheet();
  let verleden_2024 = workbook.getWorksheet("Verleden 2024");
  // Paste to range A2 on verleden_2024 from range A2:A58 on selectedSheet
  const dataRange = table1.getRangeBetweenHeaderAndTotal();
  const visibleAreas = dataRange.getSpecialCells(ExcelScript.SpecialCellType.visible);
  
  if (visibleAreas !== undefined) {
    verleden_2024.getRange("A" + verleden_2024.getRange().getRowCount()).getRangeEdge(ExcelScript.KeyboardDirection.up).getOffsetRange(1, 0).copyFrom(visibleAreas)
    let areas = visibleAreas.getAreas();
    for (let i = areas.length - 1; i >= 0; i = i - 1) {
      areas[i].getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
    }
  } else {
    console.log("Niets te kopieren");
  }
  table1.getAutoFilter().clearCriteria();
}

 

[vrouw]

Top, nu geeft ie geen foutmelding meer maar blijft wel weer de datums uit het verleden staan.
het lijkt er dus op dat het filter niet werkt?

Nu staat mijn datum in deze format, weet niet of dat ermee heeft te maken omdat in de code staat:

applyCustomFilter("<" + day + "-" + month + "-" + year);