Office: Seit Win 11 Zugriffsprobleme bei Excel über Makros

'n Aamnd Timo

so, nach 'nem Kaffee und 'nem Hörnchen, dann mal an's Eingemachte: Im folgenden Code (Deine #2) habe ich mal ein paar Anmerkungen hinterlassen, die uns Deinem Ziel vielleicht ein Schrittchen näherbringen. Mein Code gehört in Deine Cockpit-Datei, die dann die gewünschten Dateien öffnet.

Code:

Option Explicit
Sub GetCargo()

Dim i As Integer, r As Integer
Dim Bewertung As Workbook, P As Workbook

Workbooks.Open Filename:="C:\Dropbox\1 - Geldanlage\1 - Aktienanalyse\000_BLANKOS\Bewertung.xlsm"
Set Bewertung = ActiveWorkbook

ThisWorkbook.Activate 'unnötig, da das geöffnete wb automatisch aktiv ist
r = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To r
   Workbooks.Open Filename:=Cells(i, 1) 'huch jetzt wird ja schon wieder ein wb geöffnet
   Set P = ActiveWorkbook
   MsgBox ActiveWorkbook.ActiveSheet.Cells(1, 1) & " " & Now()
   P.Close SaveChanges:=True
Next i

End Sub

Hier müssen wir noch mal die verschiedenen Ebenen des Ablaufs klären.

Als erste Hilfe schlage ich mal vor, in Deiner Iteration einen banalen Errorhandler einzubauen: Mit On Error Resume Next kannst du den Fehler schon mal besser eingrenzen. Läuft Deine Routine dann durch, ist also nicht der gesamte Code schuld, sondern nur ein bestimmter Aufruf. Welche Datei dann klemmt, siehst Du am Ergebnis.

Als zweite Hilfe überlege ich mir mal völlig unabhängig von Deinem bisherigen Code ein Konzept, wie ich das auf der grünen Wiese aufziehen würde. Das mache ich allerdings nicht hier, sondern auf meinen VBAsteleien, müsste ja sonst crossposten ...

lg

 

Habe erste Schritte erledigt !

 

Moin Bastler!

Wieso verwendest du .xlsb-Dateien? Macht das einen Unterschied, dass ich .xlsm-Dateien verwende? Ich höre zum ersten Mal von diesem Dateiformat und habe es noch nie verwendet. Laut Schnell-Recherche wird das Format nur Excel-intern erkannt, und die Makro-Funktionalität ist ebenfalls gegeben, aber bei insgesamt erhöhter Geschwindigkeit im Vergleich zu .xlsx und .xlsm. Gibt es noch andere wichtige Unterschiede/Gründe für die Wahl von .xlsb?

Mein Code hat im Übrigen noch einige weitere Feinheiten:

(1)
In der Cockpit-Datei gibt es ein Tabellenblatt, auf dem die Dateipfade geschrieben werden, nachdem das Makro einen Schleifendurchgang erledigt hat, und bevor es in den nächsten Schleifendurchgang springt:

Cockpit.Activate
Worksheets("Kontrolle").Activate

Cells(i, 1).Value = p

Erklärung: "i" ist die Zählvariable für die Anzahl der gefundenen Dateipfade, "p" ist der Pfad der Datei.
Ich möchte auf diesem Wege tracken, welche Datei schon bearbeitet wurde (früher stürzte das Makro manchmal ab, vermutlich aufgrund der großen Datenmengen. Ich wollte prüfen können, ob die Schleife immer bei derselben Datei abstürzt, falls sich das häufen sollte. Vor dem ersten Schleifendurchlauf wird das Tabellenblatt "Kontrolle" komplett geleert, das hast du auf deiner vba-Basteleien-Seite auch verwendet.

(2)
Damit die Schleife im Falle eines Absturzes (wie in (1) beschrieben) nicht die kompletten Dateien erneut durchlaufen muss, gibt es eine "Versionsnummer" für das Makro. Innerhalb der durchzulaufenden Dateien gibt es einen benannten Bereich "Versionsnummer", den ich als letzten Schritt vor dem Speichern der Datei per Makro auf einen vorher festgelegten Wert setze, z.b. "50". Alle Dateien senden per Power Query den Wert dieses benannten Bereichs an die Tabelle des Cockpits, in dem auch der Dateipfad steht. Bevor das Makro die Schleife startet, werden diese Daten im Makro aktualisiert. Die Zählung der Anzahl der benötigten Schleifendurchläufe wird berechnet, indem der Dateipfad nur dann in eine Variable geschrieben wird, wenn die Bedingung gilt:

Versionsnummer < Versionsnummer_neu

Dabei wird "Versionsnummer" aus der Datei direkt gezogen (und landet dann in der Tabelle), und "Versionsnummer_neu" muss ich vor dem Start des Makros jeweils manuell eingeben. Wenn also eine Datei bereits einen Schleifendurchlauf "mitgemacht hat" und alle gewünschten Änderungen durchgeführt wurden, wird ihre Versionsnummer damit auf den "neuen Wert" gesetzt. Stürzt das Makro also mittendrin ab, werden beim nächsten Start des Makros (sofern die Versionsnummer gleichgeblieben ist) nur die Dateien angesteuert, die "noch nicht dran waren".

(3) Ich arbeite absichtlich nur mit den Dateien, deren Dateipfade ich in die Liste der Cockpit-Datei geschrieben habe, weil es vorkommen kann, dass Dateien in den Ordnern sind, die ich nicht ansteuern möchte (z.B. unfertige, neue Dateien, bei denen die erforderliche Datenstruktur noch nicht vorhanden ist, und die eine Fehlermeldung erzeugen würden. Auch kann ich so "Problemdateien" sozusagen "deaktivieren", und trotzdem das Makro auf alle anderen Dateien anwenden, ohne Dateien aus einem Ordner zu schieben).


-----

Ich verwende übrigens schon das "On Error Resume Next", weil die Dateien nicht alle komplett identisch sind (das ist ein Relikt aus früheren Zeiten uns sozusagen "historisch gewachsen", weil ich früher die Dateien manuell verändert habe und erst seit kurzem auf "Veränderung per Makro" umgestiegen bin. Ich brauchte damals nicht alle Änderungen in allen Dateien, und habe aus Zeitgründen in den Dateien nur das verändert, was nötig war. Daher kann es passieren, dass das Makro z.B. eine intelligente Tabelle ansteuern will, die in einer Datei nicht vorhanden ist. Damit das Makro dann aber trotzdem weiterläuft, muss ich zwingend mit dem Befehl "On Error Resume Next" arbeiten).

Meine Arbeitswoche ist leider unglaublich voll, und ich komme erst am Samstag/Sonntag so richtig mit Verstand dazu, meinen Code auseinanderzudividieren.

Ich schreibe trotzdem gerne mal meinen Code hier sein, vielleicht siehst du Fehler/Probleme. Da ich das mit den Makros auch erst seit 2 - 3 Monaten mache und mir alles in Eigenregie mit ChatGPT aneigne, ist sicherlich noch nicht alles perfekt programmiert, und es gibt sicherlich eine Menge Verbesserungspotenzial.

Code:

Option Explicit

' Ruft sich alle Dateien aus der Liste bei "Schätzungen" auf und führt nacheinander Befehl1, Befehl2, Befehl3 aus
Sub DateienAusbessern()
    
    ' Liste aktualisieren, um korrekte Makroversion zu sehen
    Application.Run "SchätzungenImportAktualisieren"
    
        
    ' Variablennamen aus der Datei "URL Abfrage"
    Dim ws_URL As Worksheet
    Dim ws_Ausbessern As Worksheet
    
    Set ws_URL = ThisWorkbook.Sheets("Schätzungen")
    Set ws_Ausbessern = ThisWorkbook.Sheets("Ausbessern")

    ' Variablennamen für die nacheinander geöffneten Dateien
    Dim aws As Worksheet
    Dim wb As Workbook
    
    Dim i As Integer
    
    ' Letzte Zeile in Spalte A bestimmen
    Dim Anzahl As Integer
    Dim letzteZeile As Integer
    Anzahl = ws_URL.Range("A2").Value
    letzteZeile = Anzahl + 5
    
    ' Bereich definieren, in dem die Dateipfade stehen
    Dim rng As Range
    Set rng = ws_URL.Range("L6:L" & letzteZeile)
    
    ' Schleife durch die Zellen, um die Dateipfade in die Collection zu laden
    Dim Zelle As Range
    Dim Dateipfade As New Collection
    Dim Versionsnummer As Integer
    Dim Mindestwert As Integer
    
    
    Mindestwert = 49
    Dim Counter As Integer
    Counter = 1
    
    For Each Zelle In rng
        Versionsnummer = ws_URL.Cells(Zelle.Row, "U").Value
        If Versionsnummer <= Mindestwert Then
          Dateipfade.Add Zelle.Value
        End If
    Next Zelle
    
    ' "Ausbessern" definieren,  markieren, Seiteninhalt löschen; "A1" markieren
    ws_Ausbessern.Activate
    Cells.Select
    Selection.ClearContents
    Range("A1").Activate
    
  
       ' Schleife starten mit ÖFFNEN DER ZIELDATEI
   For i = 1 To Dateipfade.Count
          Set wb = Workbooks.Open(Dateipfade(i))
          Set aws = wb.Sheets("Schätzungen")
        
        ' Fehlerbehandlung aktivieren
          On Error Resume Next

    ' Definition der Quelldatei - üblicherweise "Bewertung.xlsm"
    Dim wbQuelle As Workbook
    Dim wsQuelle As Worksheet
    Set wbQuelle = Workbooks("Bewertung.xlsm")  ' Name der Quell-Arbeitsmappe
    Set wsQuelle = wbQuelle.Sheets("Bewertung") ' Blatt "Bewertung" in der Quell-Arbeitsmappe


    
Dim VersionsnummerNeu As Integer
VersionsnummerNeu = Mindestwert + 1



' ======= AB HIER DIE VERÄNDERUNGS-BEFEHLE EINFÜGEN ======


Application.ScreenUpdating = False




' ======= BIS HIER DIE VERÄNDERUNGS-BEFEHLE EINFÜGEN ======
Range("Versionsnummer_Makro") = VersionsnummerNeu


         Application.ScreenUpdating = True
    
        ' Fehlerbehandlung deaktivieren
          On Error GoTo 0
        
        ' Datei schließen und speichern
          wb.Close SaveChanges:=True

  
        ws_Ausbessern.Activate
        Cells(i, 1).Value = Dateipfade(i)
Next i


    
    MsgBox ("Makro fertig")
    
End Sub


Sub Abfrage_Dateien_Ausbessern()
    ' Abfrage für OK oder Abbrechen
    Dim Antwort As VbMsgBoxResult
    Antwort = MsgBox("Makro starten?", vbOKCancel + vbQuestion, "Bestätigung")

    ' Prüfen, ob der Benutzer auf OK geklickt hat
    If Antwort = vbOK Then
        MsgBox "Das Makro wird ausgeführt."
        Call DateienAusbessern
    Else

        MsgBox "Das Makro wurde abgebrochen."
        Exit Sub
    End If
End Sub

Wie du siehst, bleiben der Start und das Ende des Makros immer identisch; es gibt einen Teil mitten im Code, an dem die eignetlichen Änderungen geschrieben werden. Da ich für jeden neuen Makrodurchlauf/neue Versionsnummer nur diesen Teil ändere, bleibt das Aufrufen der Dateien unangetastet.

 

Moin Timo,

Nö - nicht wirklich. außer dass .xlsb-Dateien geringfügig! besser gegen Blattschutz-Hacker geschützt sind.

Tümmt! Was mich nicht daran hindert, Deine Ideen mal in meine Grüne-Wiesen-Version zu integrieren . Versionsverwaltung z.B. schreit nach einer Hilfstabelle im Cockpit

Nachdem Du uns so soviel Zeit lässt, bekommst Du auch nicht gleich ein komplette Lösung, erst nach dem nächsten Set Kaffee/Hörnchen

Eine Frage noch: Vom Kompatibilitätsproblem Win10/11 sind wir nun weg?

Schöne Grüße

 

Genau das mit dem Kompatibilitätsproblem kann ich erst Samstag prüfen... Ich brauche da Ruhe für, die hab ich unter der Woche nicht...

 

Es scheint alles zu klappen, wenn ich meine Quelldatei an einen anderen Dateispeicherort außerhalb der Dropbox verschiebe. Ich verstehe es zwar nicht, denn sämtliche Dateien liegen in der Dropbox, aber ich habe auf jeden Fall eine Möglichkeit gefunden, das Problem zu lösen, und daher bin ich sehr dankbar. Ich danke auch dir Bastler, dass du dir so viel Mühe gegeben hast mir zu helfen.

Ich werde heute und morgen noch weitere Macros schreiben und schauen, ob sich das Problem auch weiterhin als gelöst darstellt.

 

Viel Erfolg bei den Tests!

lg

 

Bisher klappt das mit den Makros. Allerdings brauchen meine Power-Query-Abfragen ca. 5-6x so lange wie noch unter Windows 10 (meine Cockpit-Datei zieht sich per PQ Daten wie z.B. die Versionsnummer aus den Einzeldateien). Auch tritt es immer wieder mal auf, dass Excel als Ganzes nicht mehr reagiert, bzw. ich zwar in den Dateien auf beispielsweise ein anderes Tabellenblatt oder eine andere Zelle klicke, aber diese Änderung optisch nicht dargestellt wird - das Bild friert sozusagen ein, auch wenn die Aktionen durchaus in den Dateien ankommen (wenn ich mit STRG+S speichere, nachdem ich etwas testweise verändert habe, kann ich beim erneuten Öffnen sehen, dass die Eingaben ankamen). Auch ist nach dem Ausführen der Makros Excel unglaublich langsam, sodass ich meist alle Dateien schließen und Excel erneut starten muss, um wieder normal arbeiten zu können. Es wirkt, als wenn Excel den Speicher vollknallt, aber nicht wieder ordentlich freigibt.

 

Hallo
Du schreibst nach VBA Einsatz sind PQ-Abfragen langsamer!
Ich frage mich warum bleibst du nicht nur bei PQ ?

 

Moin Timo,

das mein (immer wieder mal angebotener) Grüne-Wiese-Vorschlag nicht sofort lautes Hurra hervorruft kann ich bei komplexen Projekten erst einmal gut verstehen. Und ganz sicher wird mein kleines Beispiel Deine Anforderungen nicht im Ansatz erfüllen.

Aber: Um Versionsnrn. aus einer Datei zu ziehen, brauche ich mit VBA mal auf jeden Fall kein PQ.

Ich kann nur vermuten: Ein großes (aber vermeidbares) Problem Deines Projekts könnte der Mix aus PQ und VBA sein. PIVPQ fragt, warum dann nicht nur PQ? Er übersieht dabei, dass PQ als komplexes System daherkommt, das außer XL und dem integrierten VBA noch auf .NET und andere Laufzeit-Elemente angewiesen ist. Hier bleiben Probleme mit Software-Ständen nicht aus. Ein Grund, warum ich auf PQ konsequent verzichte. Das, ohne in Frage zu stellen, dass PQ in XL gute Dienste leisten kann.

XL kann extrem viel schon ganz alleine. XL + VBA können aber noch sehr, sehr viel mehr. z.B. Speicher ganz gezielt wieder freigeben.

Lass doch mal einen PQ-Ablauf sehen und ich versuche das im VBA-Beispiel anschaulich zu machen.

Schönen Abend noch!

 

Ich brauche beides, und zwar für verschiedene Dinge:

PQ wird benötigt, um aus allen Einzeldatein mehrere verschiedene (identisch strukturierte) Tabellen an verschiedene Dateien zu senden. Z.b. an verschiedene Watchlists, oder eben an meine Cockpit-Datei. Und mittels PQ greife ich auch für jede Einzeldatei auf eine bestimmte Webseite zu, um Informationen dort zu ziehen. Das passiert über ein anderes Makro, das auch aus der Cockpitdatei ausgeführt wird. Hier werden die Einzeldateien nacheinander geöffnet und die jeweiligen PQ-Abfragen gestartet. Das Problem ist nämlich, dass wenn die Abfragen passieren, brauche ich aktuelle Daten aus den Dateien, die dafür kurz geöffnet werden müssen (es gibt Funktionen, die auf Tagesbasis Dinge berechnen, und da reicht kein Datenstand von vor einigen Tagen. Diese Funktion müssen Makros übernehmen, eben mit der Öffnen und Schließen-Funktion. Einen "normalen" Verweis auf eine ZElle einer geschlossenen Datei ist dabei nicht zielführend, weil die Daten nicht aktuell sind.

VBA brauche ich, wenn ich neue Funktionen in alle Dateien einfügen möchte. Es erspart mir, alle Dateien einzeln öffnen und manuell verändern zu müssen. Es handelt sich wie gesagt um mehrere hundert Einzeldateien und um die immer gleichen To-Dos, da bietet sich VBA einfach an. Auch sind alle Dateien in einer unterschiedlichen "Dateiversion", die eben die aktuellste war, als die Datei erstellt war - es handelt sich wie gesagt um ein Projekt, das seit mehreren Jahren läuft, und die "Grunddatei" wurde im Laufe der Zeit angepasst, aber eben nicht zwingenderweise alle da existenten Dateien mit. Ich kann in einem bestimmten Rahmen mittels VBA die Dateien aneinander angleichen. Und ich nutze diese "Praxisbeispiele", um mich in Punkto VBA weiterzubilden.

Ich weiß nicht, wie ich meine Probleme anders lösen kann als mit einer Kombination aus PQ und VBA... Und ich wiederhole: Unter Windows 10 klappte alles, es ist also grundsätzlich möglich..