Es gibt verschiedene Möglichkeiten, Excel dazu zu bringen, Datenverbindungen automatisch zu aktualisieren und ein Arbeitsblatt neu zu berechnen. Diese Tricks sind beim Datentyp „Stock“ in Excel für Microsoft 365-Kunden wichtiger.
Normalerweise aktualisiert sich Excel beim Ändern selbst Ein Zellenwert. Heutzutage gibt es Situationen, in denen Zellen den Wert ändern, Excel das Arbeitsblatt jedoch NICHT aktualisiert. Mit anderen Worten, das moderne Excel hat sich auf eine Weise geändert, an die sich Microsoft noch nicht vollständig angepasst hat.
Idealerweise Excel verfügt über eine Gesamteinstellung, um das Arbeitsblatt alle n Sekunden oder Minuten zu aktualisieren. Derzeit benötigen wir Problemumgehungen, um dies zu erreichen.
In einigen Situationen kann eine Datenaktualisierung oder -neuberechnung erforderlich sein oder umsichtig.
Bestandsdatentyp
Beim Bestandsdatentyp ist die Fähigkeit zur automatischen Aktualisierung wichtiger. Benutzer möchten, dass ihre Arbeitsblätter den Lat erfassen Die Preise werden automatisch ermittelt, was mit den aktuellen Vorschauversionen nicht möglich ist. Anstatt einen netten automatischen „Ticker“ zu haben, wird erwartet, dass wir auf „Aktualisieren“ klicken, um die neuesten Preise zu erhalten.
Die Datentypen „Stock“ und „Geo“ sind seltsame Tiere. Sie sind Datenverbindungen zu externen Quellen, werden jedoch NICHT als Excel-Datenverbindungen angezeigt. Das heißt, Sie können keine automatische Datenaktualisierung wie bei normalen Datenverbindungen einrichten. Tatsächlich gibt es keine exponierten Steuerelemente für die Datentypen Stock oder Geo.
NOW () und andere flüchtige Funktionen
Die Funktion NOW () wird bei jedem Excel auf das neueste Datum und die neueste Uhrzeit aktualisiert berechnet das Arbeitsblatt neu. Wenn dies jedoch nicht möglich ist, ändert Now () den Wert nicht. Einige externe Faktoren sind erforderlich, damit Excel Now () und den Rest des Arbeitsblatts aktualisiert. Mit anderen Worten, Sie sollten in der Lage sein, einen Blick auf ein Arbeitsblatt zu werfen und zu wissen, dass es auf die Sekunde genau ist, aber dies ist mit Excel nicht sofort möglich.
Benutzerdefinierte VBA-Funktionen können auch mit als flüchtig gekennzeichnet werden Diese Zeile im Funktionscode:
Application.Volatile
In dieser Zeile wird die Funktion ausgeführt, wenn Excel das Arbeitsblatt aktualisiert / neu berechnet.
Zusätzliche Vorsicht
Vielleicht möchten Sie, dass Ihr Arbeitsblatt vorsorglich automatisch aktualisiert wird? Viele alte Excel-Benutzer erinnern sich an Situationen, in denen Excel nicht ordnungsgemäß aktualisiert wurde, sodass sie den Ansatz „Gürtel und Klammern“ (zumindest gelegentlich) mögen.
Code
Die Standardmethode zum Erzwingen der automatischen Erzwingung Das Update von Excel ist ein kurzer Ausschnitt aus VBA-Code. Wir verwenden Folgendes: Es gibt viele Variationen desselben Themas. Der vollständige Code befindet sich am Ende des Artikels.
Es gibt drei Funktionen.
RefreshAllDataConn
führt die eigentliche Aktualisierung von Datenverbindungen durch (Arbeitsmappen (ThisWorkbook.Name) .RefreshAll), und wir haben zwei optionale Zeilen hinzugefügt, um die letzte Aktualisierung in der unteren Statusleiste anzuzeigen.
Wenn Sie besonders vorsichtig sein möchten, fügen Sie eine Zeile hinzu, um die Neuberechnung explizit zu erzwingen. Entweder ActiveSheet.Calculate oder die extreme Application.CalculateFull (dies würde ein großes Arbeitsblatt verlangsamen, sparsam verwenden).
AutoRefresh
Führen Sie das RefreshAllDataConn-Sub jede Minute oder einen beliebigen Wert aus, den Sie festlegen Die Zeile Application.OnTime Now + TimeValue („00:01:00“), „AutoRefresh“
Workbook_Open
ist eine integrierte Excel-Funktion, die beim Öffnen des Arbeitsblatts automatisch ausgeführt wird . In diesem Fall wird die automatische Aktualisierung gestartet.
Datenverbindungsumgehung
Der Nachteil des VBA-Ansatzes besteht darin, dass ein XLSM-Arbeitsblatt erforderlich ist (makrofähiges Excel-Arbeitsblatt). Aufgrund von Sicherheitsbedenken kann es zu Problemen beim Teilen von makrofähigen Dateien kommen.
Mit PowerQuery / Get and Transform gibt es eine andere Möglichkeit, eine Neuberechnung des Arbeitsblatts zu erzwingen. Es ist eine Problemumgehung und nicht perfekt, aber es ist möglich und erfordert kein makrofähiges Arbeitsblatt.
Kurz gesagt, stellen Sie sicher, dass eine Datenabfrage mit automatischer Aktualisierung eingerichtet ist. Wenn keine Datenverbindung besteht, fügen Sie dem Arbeitsblatt eine kleine hinzu.
Sobald Sie eine Abfrage zur automatischen Aktualisierung haben, sollte auch das Arbeitsblatt mit allen flüchtigen Funktionen aktualisiert werden.
Im Idealfall sollten auch die Datentypen „Stock“ und „Geo“ aktualisiert werden.
Jede Excel-Datenabfrage enthält einige Aktualisierungsoptionen in den Abfrageeigenschaften. Die meisten von ihnen sind standardmäßig deaktiviert.
Alle nnn Minuten aktualisieren – standardmäßig 60 Minuten empfohlen.
Daten beim Öffnen der Datei aktualisieren
Hintergrundaktualisierung aktivieren
Diese Verbindung bei Alle aktualisieren aktualisieren
Die Problemumgehung für die automatische Aktualisierung besteht darin, eine kleine und praktisch unbedeutende Lösung zu erstellen Datenverbindung. Konfigurieren Sie dann diese Datenverbindung so, dass sie jede Minute oder zu jeder gewünschten Zeit aktualisiert wird. Dies sollte die Aktualisierung des Arbeitsblatts einschließlich der oben genannten flüchtigen Funktionen erzwingen.
Einige Versionen dieser Problemumgehung fügen einen Link zu einer winzigen CSV-Datei auf demselben Computer hinzu. Das gleiche Ergebnis erzielen wir mit einer Datenverbindung aus einer Tabelle im Arbeitsblatt.
Erstellen Sie eine kleine Tabelle mit einer einzelnen Zelle. Die Zelle kann alles enthalten, aber wir erstellen eine Zelle mit NOW (), aus Gründen, die wir später erläutern werden.
Wählen Sie die Tabelle aus und wählen Sie dann Daten | Daten abrufen | Aus anderen Quellen aus Tabelle / Bereich. Der genaue Menüpunkt kann je nach Excel-Version unterschiedlich sein.
Wenn der Abfrage-Editor geöffnet wird, schließen Sie ihn einfach und laden Sie ihn . Im Bereich Datenverbindungen wird eine Abfrage angezeigt.
Klicken Sie mit der rechten Maustaste auf die Abfrage, und wählen Sie Eigenschaften aus, um die anzuzeigen Einstellungen, die wir benötigen.
Stellen Sie die Aktualisierungsrate ein, die zu Ihnen passt.
Um ordentlich zu sein, wir Verschieben Sie die Quelltabelle (rechts) bei der geladenen Abfrage (links) auf dasselbe Blatt. Da wir NOW () in der Zelle der Quelltabelle verwendet haben, ist es leicht zu erkennen, wann / ob das Arbeitsblatt aktualisiert wurde.
VBA-Codebeispiel
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
Automatische Excel-Arbeitsblattliste oder Inhaltsverzeichnis erstellen
Komplette Excel NetworkDays () -Lösung mit Feiertagen & Urlaub