Er zijn verschillende manieren om Excel automatisch gegevensverbindingen te laten vernieuwen en een werkblad opnieuw te laten berekenen. Deze trucs zijn belangrijker met het gegevenstype Voorraad in Excel voor Microsoft 365-klanten.
Normaal gesproken zal Excel zichzelf bijwerken wanneer u wijzigt een celwaarde. Tegenwoordig zijn er situaties waarin cellen van waarde veranderen, maar Excel het werkblad NIET bijwerkt. Met andere woorden, het moderne Excel is veranderd op manieren waaraan Microsoft nog niet volledig is aangepast.
Idealiter Excel zou een algemene instelling hebben om het werkblad elke n seconden of minuten te verversen. Zoals het er nu uitziet, hebben we tijdelijke oplossingen nodig om dit mogelijk te maken.
Hier zijn enkele situaties waarin het nodig kan zijn om gegevens te vernieuwen of opnieuw te berekenen, of voorzichtig.
Aandelengegevenstype
Met het Aandelengegevenstype is de mogelijkheid om automatisch bij te werken belangrijker. Gebruikers willen dat hun werkbladen de lat beste prijzen automatisch, iets wat de huidige preview-releases niet kunnen. In plaats van een mooie automatische ‘ticker’ te hebben, wordt van ons verwacht dat we op ‘Vernieuwen’ klikken om de laatste prijzen te zien.
De gegevenstypen Aandelen en Geo zijn merkwaardige beesten. Het zijn gegevensverbindingen met externe bronnen, maar verschijnen NIET als Excel-gegevensverbindingen. Dat betekent dat u geen automatische gegevensvernieuwing kunt instellen, zoals bij normale gegevensverbindingen. In feite zijn er geen blootgestelde controles voor de gegevenstypen Stock of Geo.
NOW () en andere vluchtige functies
De functie NOW () wordt bijgewerkt naar de laatste datum en tijd wanneer Excel herberekent het werkblad. Maar als er niets is om dat te laten gebeuren, verandert Now () de waarde niet. Er is een externe factor nodig om Excel Nu () en de rest van het werkblad te laten bijwerken. Met andere woorden, je zou in staat moeten zijn om een blik op een werkblad te werpen en te weten dat het tot op de seconde is, maar dat is niet mogelijk met Excel out of the box.
Aangepaste VBA-functies kunnen ook als vluchtig worden getagd deze regel in de functiecode:
Application.Volatile
Die regel zorgt ervoor dat de functie wordt uitgevoerd wanneer Excel het werkblad bijwerkt / herberekent.
Extra voorzichtigheid
Misschien wilt u dat uw werkblad uit voorzorg automatisch wordt bijgewerkt? Veel oude Excel-handen herinneren zich situaties waarin Excel niet goed is bijgewerkt, dus houden ze van de riem en beugel-benadering (althans af en toe).
Code
De standaardmethode voor automatisch forceren update van Excel is een kort fragment van VBA-code. Dit is wat we gebruiken, er zijn veel variaties op hetzelfde thema. De volledige code staat onderaan het artikel.
Er zijn drie functies.
RefreshAllDataConn
doet het daadwerkelijke vernieuwen van gegevensverbindingen (Workbooks (ThisWorkbook.Name) .RefreshAll) en we hebben twee, optionele, regels toegevoegd om de laatste keer dat vernieuwd is op de onderste statusbalk weer te geven.
Als u extra voorzichtig wilt zijn, voegt u een regel toe om expliciet herberekening af te dwingen. Ofwel ActiveSheet.Calculate of de extreme Application.CalculateFull (dit zou een groot werkblad vertragen, spaarzaam gebruiken).
AutoRefresh
voer de RefreshAllDataConn-sub elke minuut uit of welke waarde je ook instelt de regel Application.OnTime Now + TimeValue (“00:01:00”), “AutoRefresh”
Workbook_Open
een ingebouwde Excel-functie die automatisch wordt uitgevoerd wanneer het werkblad wordt geopend . In dit geval wordt AutoRefresh gestart.
Data Connection workaround
Het nadeel van de VBA-benadering is dat een .xlsm-werkblad nodig is (Excel-werkblad met macro ingeschakeld). Er kunnen problemen zijn met het delen van macro-ingeschakelde bestanden vanwege veiligheidsproblemen.
De komst van PowerQuery / Get en Transform betekent dat er een andere manier is om een herberekening van een werkblad af te dwingen. Het is een tijdelijke oplossing en niet perfect, maar het is mogelijk en heeft geen macro-ingeschakeld werkblad nodig.
Kortom, zorg ervoor dat er een gegevensquery is ingesteld met automatisch vernieuwen. Als er geen gegevensverbinding is, voegt u een kleine toe aan het werkblad.
Zodra u een automatisch verfrissende zoekopdracht heeft, moet het werkblad inclusief vluchtige functies ook worden vernieuwd.
Idealiter zouden de gegevenstypen Stock en Geo ook moeten worden vernieuwd.
Elke Excel-gegevensquery wordt geleverd met enkele vernieuwingsopties in de Query-eigenschappen. De meeste zijn standaard UIT.
Vernieuw elke nnn minuten – standaard uitgeschakeld met 60 minuten voorgesteld.
Gegevens vernieuwen bij het openen van het bestand
Vernieuwen op de achtergrond inschakelen
Vernieuw deze verbinding bij Alles vernieuwen
De oplossing voor automatisch vernieuwen is om een kleine en praktisch onbeduidende dataverbinding. Configureer vervolgens die gegevensverbinding om elke minuut of op elk gewenst moment bij te werken. Dat zou het werkblad moeten dwingen om bij te werken, inclusief de vluchtige functies die hierboven zijn genoemd.
Sommige versies van deze tijdelijke oplossing voegen een link toe naar een klein csv-bestand op dezelfde computer. We hebben hetzelfde resultaat verkregen met een gegevensverbinding van een tabel in het werkblad.
Maak een kleine tabel met een enkele cel. De cel kan van alles bevatten, maar we maken een cel met NOW () erin, om redenen die we later zullen uitleggen.
Selecteer de tabel en kies Gegevens | Gegevens ophalen | Uit andere bronnen | uit Table / Range. Het exacte menu-item kan verschillen, afhankelijk van uw versie van Excel.
Wanneer de Query-editor wordt geopend, hoeft u alleen maar te sluiten en te laden . In het gegevensverbindingen-paneel ziet u een query.
Klik met de rechtermuisknop op de query, kies eigenschappen om de instellingen die we nodig hebben.
Stel de vernieuwingsfrequentie in die bij u past.
Om netjes te zijn, verplaats de brontabel (rechts) naar hetzelfde blad bij de geladen query (links). Omdat we NOW () in de brontabelcel hebben gebruikt, is het gemakkelijk om te zien wanneer / of het werkblad is vernieuwd.
VBA-codevoorbeeld
Sub RefreshAllDataConn() " Refresh all Data Connections. " This should include Stock and Geo data types, even though they aren"t listed. Workbooks(ThisWorkbook.Name).RefreshAll " Show update time on status bar to confirm. " comment these lines out if not needed. Application.DisplayStatusBar = True Application.StatusBar = "Refreshed at: " & Now()End SubSub AutoRefresh()" to run a Refresh All on the workbook every n minutes RefreshAllDataConn " Repeat every minute or change to whatever value you prefer. Application.OnTime Now + TimeValue("00:01:00"), "AutoRefresh"" this is a simple example. There"s no coded way to exit this function.End SubPrivate Sub Workbook_Open()" Starts the automatic refresh when the workbook is opened," commented out as a precaution. " AutoRefreshEnd Sub
Maak automatische Excel-werkbladlijst of inhoudsopgave
Complete Excel NetworkDays () -oplossing met feestdagen & vakanties