Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1680to1684
1680to1684
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

VBA für einmalige Aktualisierung am Tag

Forumthread: VBA für einmalige Aktualisierung am Tag

VBA für einmalige Aktualisierung am Tag
26.03.2019 21:05:11
Alessio
Guten Abend,
ich bin mit VBA nur seit einer Woche beschäfigt und suche seit Tagen einen VBA-Code der
a) alle die SQL Tabellen aktualisiert (also RefreshAll)
b) den ganzen Arbeitsblatt berechnet (also Berechnung soll auf manuell sein)
c) die Pivottabellen aktualisiert
Wichtig ist aber, dass der Code nur einmal am Tag läuft, denn ich schaue die Ergebnisse in einem späteren Zeitpunkt.
Durch Windows Einstellungen hab ich erstmal die Uhrzeit zum Öffnen des Excel Files um 7:20 eingestellt.
Beim Öffnen soll der VBA-Code prüfen dass er an dem Tag nicht ausgeführt wurde und evtl. in der oben genannten Reihenfolge laufen.
Ich dachte ich könnte den Makrorekorder nutzen aber dann weiß ich nicht wie in den Code eingeben, dass zwischen a) und b) mind. 7-9 Min abwarten soll. Generell bräuchte ich aber eure Hilfe, besonders um zu vermeiden dass der Code beim Öffnen immer wieder läuft.
Ich bedanke mich im Voraus und hoffe auf eure Hilfe. :-)
Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: VBA für einmalige Aktualisierung am Tag
27.03.2019 10:04:54
Nepumuk
Hallo Alessio,
in das Modul "DieseArbeitsmappe":
Option Explicit
Private Sub Workbook_Open()
Call Start
End Sub

In ein Standardmodul (Menüleiste im VBA-Editor - Einfügen - Modul):
Option Explicit
Option Private Module
Public Sub Start()
Const RUN_DATE As String = "Gelaufen"
Dim objName As Name
Dim dtmRunDate As Date
For Each objName In ThisWorkbook.Names
If objName.Name = RUN_DATE Then Exit For
Next
If objName Is Nothing Then
Call ThisWorkbook.Names.Add(Name:=RUN_DATE, _
RefersTo:=Array(Date - 1), Visible:=False)
Else
Set objName = Nothing
End If
dtmRunDate = CDate(Evaluate(RUN_DATE)(1))
If dtmRunDate

Gruß
Nepumuk
Anzeige
AW: VBA für einmalige Aktualisierung am Tag
27.03.2019 15:50:09
Alessio
Hi Nepumuk,
erstmal vielen Dank für deine Hilfe. Ich habe es so eingestellt, wie du beschrieben hast, dann gespeichert, geschlossen und wieder geöffnet.
VBA wurde beim Öffnen automatisch ausgeführt.
SQL Abfragen wurden aktualisiert.
Berechnung hat aber nicht funktioniert.
Einige Pivottabellen wurden nicht aktualisiert.
EDIT: vielleicht habe ich die 9 Minuten nicht abgewartet und früher getestest...werde morgen testen und nochmal das Ergebnis hier schreiben. :)
Anzeige
AW: VBA für einmalige Aktualisierung am Tag
28.03.2019 09:59:25
Alessio
Guten Morgen Nepumuk,
a) erfolgreich ausgeführt
b) nach der 9 Min Wartezeit fängt die Berechnung an. Die Daten werden in der Tabelle aktualisiert aber jetzt liest Excel die Daten weiterhin (immer wieder 'Daten werden abgerufen' und 'Daten werden gelesen' seit 10 Min jetzt).
Ich habe die Berechnung abgebrochen und den Laufzeitfehler '1004' bekommen.
'Excel konnte die Tabelle xxx nicht über die Verbindung "SqlServerxxx" aktualisieren. Fehlermeldung der externen Datenquelle: The operation failed because the source database does not exist, the source table does not exist, or because you do not have access to the data source."
Alle drei Optionen nicht möglich. Ich nehme an, ich lese das weil der Vorgang von mir abgebrochen wurde.
c) hab wegen b) nicht getestet.
Anzeige
AW: VBA für einmalige Aktualisierung am Tag
28.03.2019 10:07:06
Nepumuk
Hallo Alessio,
da kann ich dir nicht weiterhelfe da ich weder deine Mappe noch einen SQL-Server zur Verfügung habe.
SORRY !!!
Gruß
Nepumuk
AW: VBA für einmalige Aktualisierung am Tag
28.03.2019 10:17:55
Alessio
Hi Nepumuk,
ja kein Problem. Ich versuche den Fehler zu finden damit dein Makro bis zum Ende läuft und melde mich wieder. :-)
Hab einen schönen Tag.
LG,
Alessio
Anzeige
AW: VBA für einmalige Aktualisierung am Tag
28.03.2019 16:14:56
Hajo_Zi
Hallo Alessio,
offen bedeutet es soll noch eine Antwort kommen.
Warum ist dein Beitrag Offen.
Du willst doch was machen. Soll jemand vorbei kommen?

AW: VBA für einmalige Aktualisierung am Tag
28.03.2019 16:19:50
Alessio
Hi Hajo_Zi,
ah! Witzig.
Ja, ich arbeite heute noch an diesem Fehler und morgen lasse ich den Nepumuks Makro laufen. Wenn es klappt, wie schon in meiner letzten Nachricht geschrieben, markiere ich den Beitrag als erledigt. Ist das ein Problem wenn der Beitrag für die nächste 24 St so bleibt oder gibt's einen Service Level zu erreichen?
LG,
Alessio
Anzeige
AW: VBA für einmalige Aktualisierung am Tag
29.03.2019 09:55:14
Alessio
Hi Nepumuk,
Fehler wurde behoben aber Makro hängt immer noch. Vielleicht versuche ich erstmal mit dem Skript ohne die Berechnung (würde manuell machen). Kannst du mir sagen ob es so passt, wenn ich nur die RefreshAll Teil drin lassen möchte?
Option Explicit
Option Private Module

Public Sub Start()
Const RUN_DATE As String = "Gelaufen"
Dim objName As Name
Dim dtmRunDate As Date
For Each objName In ThisWorkbook.Names
If objName.Name = RUN_DATE Then Exit For
Next
If objName Is Nothing Then
Call ThisWorkbook.Names.Add(Name:=RUN_DATE, _
RefersTo:=Array(Date - 1), Visible:=False)
Else
Set objName = Nothing
End If
dtmRunDate = CDate(Evaluate(RUN_DATE)(1))
If dtmRunDate

Anzeige
AW: VBA für einmalige Aktualisierung am Tag
29.03.2019 10:01:38
Nepumuk
Hallo Alessio,
nach der Zeile:
Call ThisWorkbook.RefreshAll

fehlen noch diese Zeilen:
ThisWorkbook.Names(RUN_DATE).RefersTo = Array(Date)
Call ThisWorkbook.Save

Damit wird das Datum des aktuellen Tages gespeichert, ansonsten würde das Makro bei jedem öffnen laufen.
Gruß
Nepumuk
Anzeige
AW: VBA für einmalige Aktualisierung am Tag
29.03.2019 10:18:38
Alessio
Hi Nepumuk,
vielen lieben Dank!
Wenn du irgendwann mal in Berlin bist, hast du ein paar Bierchen versprochen. :-)
LG,
Alessio
AW: VBA für einmalige Aktualisierung am Tag
27.03.2019 10:52:32
fcs
Hallo Alessio,
die Neuberechnung und die Aktualisierung der Pivotberichte muss man mit einem 2. Makro durchführen, dass per OnTime-Methode zeitverzögert gestartet wird.
Was ich nicht 100% weiss: Wird das Ereignis-Makro "Workbook_Open" auch gestartet, wenn die Datei via Windows-Einstellunen zu einer bestimmten Uhrzeit geöffnet wird.
Gruß
Franz
'Code unter "DieseArbeitsmappe" der Datei
Private Sub Workbook_Open()
Call prcUpdateDatei
End Sub


'Code in einem allgemeinen Modul der Datei
Sub prcUpdateDatei()
' prcUpdateDatei Makro
' Aktualisieren der SQL-Abfragen,neu Berechnen der Arbeitsmappe, und Aktualisieren _
der Pivot-Berichte
Dim sDate As String
'letztes Aktualisierungsdatum aus Dokumenteigenschaft "Kommentar" einlesen.
sDate = ThisWorkbook.BuiltinDocumentProperties("Comments")
If IsDate(sDate) Then
If CDate(sDate) = Date Then
MsgBox "Abfragen der Datei wurden heute schon aktualisiert", vbOKOnly, _
"Makro: prcUpdateDatei" 'nur zum Testen
Exit Sub
End If
End If
Application.Calculation = xlManual
ThisWorkbook.RefreshAll
'in 9 Minuten Mappe neu berechnen und Pivotberichte aktualiseren
Application.OnTime Earliesttime:=Now + TimeSerial(Hour:=0, Minute:=9, Second:=0), _
Procedure:="prcCalculate_and_Pivot_Refresh"
End Sub
Sub prcCalculate_and_Pivot_Refresh()
Dim wks As Worksheet
Dim pvTab As PivotTable
MsgBox "Jetzt wird neu berechnet und Pivot-Berichte aktualisiert", vbOKOnly, _
"Makro: prcCalculate_and_Pivot_Refresh" 'nur zum Testen
Application.Calculate
For Each wks In ThisWorkbook.Worksheets
For Each pvTab In wks.PivotTables
pvTab.RefreshTable
Next
Next
'Aktualisierungsdatum in Dokumenteigenschaft "Kommentar" speichern.
ThisWorkbook.BuiltinDocumentProperties("Comments") = Format(Date, "YYYY-MM-DD")
End Sub

Anzeige

Forumthreads zu verwandten Themen

Per VBA Filter von Pivot-Tabelle aktualisieren
Pivot-Tabelle

Hallo,
ich habe eine Datentabelle mit bestimmte personenbezogenen Zeitabschnitten (Name, Startdatum, Enddatum).
In einer Pivottabelle möchte ich auf diese Daten zugreifen. Dort soll gefiltert werden, welche Personen zum aktuellen Datum anwesend sind.
Hierzu habe ich folgenden Code...
13.07.2015 10:06:23
Thread-ID: 1436021
3 Beiträge
VBA-Code: Tabellenblätter aktualisieren
AS2017

Hallo zusammen,
ich habe von der Arbeit aus den Auftrag bekommen mehrere Tabellenblätter in einem Tabellenblatt zusammenzuführen (es gibt hierbei fast ausschließlich Text in den Zellen). Änderungen in den einzelnen Tabellenblättern sollen zudem gleichzeitig in der Zusammenführung aktualisie...
06.10.2017 15:08:34
Thread-ID: 1584529
3 Beiträge
Anzeige
per VBA einzelne Pivot-Tabelle aktualisieren
luftgetrocknet

Hallo zusammen,
ich habe eine Datei mit mehreren Tabellenblättern und mehreren Pivot-Tabellen.
Nun möchte ich per VBA nur eine einzelne, ganz bestimmte Pivot-Tabelle aktualisieren. Dazu habe ich mir folgenden Code überlegt:
Sheets("Tabelle1").PivotTables("PivotTable5").RefreshTable<...
19.06.2019 10:38:15
Thread-ID: 1698460
5 Beiträge
VBA - Tabellen erst aktualisieren, wenn Datenabruf fertig
Thomas
Hallo zusammen,

Ich bräuchte eure Hilfe mit folgendem Sachverhalt.

Ich habe in meiner Excel-Datei eine Datenabfrage aus dem Internet.
Nun möchte ich erst nachdem der Datenabruf fertig ist, die Tabellenfilter aktualisieren. Wie lässt sich das bewerkstelligen??

Sub Date...
13.06.2024 19:02:26
Thread-ID: 1981178
7 Beiträge
Anzeige
Anzeige

Infobox / Tutorial

Einmalige Aktualisierung von Excel-Tabellen mit VBA

Schritt-für-Schritt-Anleitung

Um alle SQL-Tabellen in Excel mit VBA einmal täglich zu aktualisieren, folge diesen Schritten:

  1. Öffne den VBA-Editor:

    • Drücke ALT + F11, um den VBA-Editor zu öffnen.

  2. Modul für Workbook_Open erstellen:

    • Füge im Fenster „DieseArbeitsmappe“ folgenden Code ein: 
      Option Explicit
Private Sub Workbook_Open()
Call Start
End Sub

  3. Standardmodul hinzufügen:

    • Klicke auf Einfügen > Modul, um ein neues Modul zu erstellen. Füge den folgenden Code ein: 
      
Option Explicit
Option Private Module

    Public Sub Start() Const RUN_DATE As String = "Gelaufen" Dim objName As Name Dim dtmRunDate As Date For Each objName In ThisWorkbook.Names If objName.Name = RUN_DATE Then Exit For Next If objName Is Nothing Then Call ThisWorkbook.Names.Add(Name:=RUNDATE, RefersTo:=Array(Date - 1), Visible:=False) Else Set objName = Nothing End If dtmRunDate = CDate(Evaluate(RUN_DATE)(1)) If dtmRunDate < Date Then Call ThisWorkbook.RefreshAll ThisWorkbook.Names(RUN_DATE).RefersTo = Array(Date) Call ThisWorkbook.Save End If End Sub

  4. Manuelle Berechnung aktivieren:

    • Stelle sicher, dass die Berechnung auf „Manuell“ gesetzt ist, um die Leistung zu optimieren.

  5. Wartezeit hinzufügen:

    • Verwende Application.OnTime, um eine Verzögerung zwischen dem Aktualisieren der Daten und der Berechnung der Pivottabellen einzufügen.

Häufige Fehler und Lösungen

Fehler: Laufzeitfehler '1004'
Dieser Fehler tritt auf, wenn ein SQL-Server nicht erreichbar ist oder die Datenquelle nicht existiert. Überprüfe die Verbindungseinstellungen und stelle sicher, dass du Zugriff auf die Daten hast.

Fehler: Daten werden nicht aktualisiert
Wenn Excel kontinuierlich „Daten werden abgerufen“ anzeigt, kann dies an einer fehlerhaften Verbindung oder an einem Timeout liegen. Stelle sicher, dass alle Verbindungen korrekt eingerichtet sind.

Alternative Methoden

Eine andere Methode zur Aktualisierung von Daten in Excel ist die Verwendung von Power Query. Dies ermöglicht dir, Daten aus verschiedenen Quellen zu importieren und zu transformieren, bevor du sie in deine Excel-Tabelle integrierst.

Um Daten aus SAP in Excel automatisch zu aktualisieren, kannst du spezielle Power Query-Verbindungen nutzen, um die Effizienz zu steigern.

Praktische Beispiele

Hier ist ein einfaches Beispiel für einen VBA-Code, der alle Daten aktualisiert und anschließend die Berechnung durchführt:

Sub UpdateAndCalculate()
    ThisWorkbook.RefreshAll
    Application.Calculate
End Sub

Dieses Skript kann in einem Standardmodul gespeichert werden und bei Bedarf ausgeführt werden.

Tipps für Profis

  • Verwende thisworkbook.refreshall: Dieser Befehl aktualisiert alle Datenverbindungen in der Arbeitsmappe.
  • Optimierung der Performance: Reduziere die Datenmenge, die aktualisiert wird, um die Leistung deines Excel-Dokuments zu verbessern.
  • Speicher regelmäßig: Füge Call ThisWorkbook.Save hinzu, um sicherzustellen, dass alle Änderungen gespeichert werden.

FAQ: Häufige Fragen

1. Wie kann ich sicherstellen, dass das Makro nur einmal pro Tag ausgeführt wird?
Durch die Implementierung eines Datumsprüfungsmechanismus im VBA-Code, der überprüft, ob das Makro bereits an diesem Tag ausgeführt wurde.

2. Kann ich auch Währungskurse automatisch aktualisieren?
Ja, du kannst externe Datenquellen verwenden, um Währungskurse zu importieren. Hierbei ist es hilfreich, die Datenverbindung in Excel zu konfigurieren, um regelmäßig Updates zu erhalten.

3. Was ist die OnTime-Methode?
Die OnTime-Methode erlaubt es dir, ein Makro zeitverzögert auszuführen, was besonders nützlich ist, um Prozesse zu steuern und Verzögerungen zwischen verschiedenen Operationen einzufügen.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige