Microsoft Excel

Herbers Excel/VBA-Archiv

Datei öffnen & schließen Fehler im Code?

Betrifft: Datei öffnen & schließen Fehler im Code? von: Norman
Geschrieben am: 01.11.2020 21:17:21

Hallo weises Forum,

hier wurde mir sehr von "Werner" und "ralp_b" sehr geholfen: https://www.herber.de/forum/archiv/1788to1792/1789317_nur_bestimmte_Dateien_aus_Liste_oeffnen.html

Stehe nun aber vor dem Problem das nur die erste zu öffnende Datei wieder geschlossen wird, alle anderen bleiben geöffnet.
Egal wo ich "objWb.Close" einfüge, um in der SChleife die Datei wieder zu schließen, bekomme ich eine Fehlermeldung "Laufzeitfehler 91: Objektvariable oder With-Blockvariable nicht festgelegt"

Einen Umweg funktioniert mittels:

For Each wb In Workbooks
If wb.Name <> ThisWorkbook.Name Then
wb.Close savechanges:=False
End If
Next wb

Dann würden aber auch evtl Excel-Dateien geschlossen die vor der Makroabarbeitung schon offen waren.
Auch weis ich nicht was passiert wenn Excel mehr als 100 Dateien per SChleife öffnet - hängt sich Excel irgendwann auf bzw überfordert dies die Hardware?

Setzte ich direkt unter " Set objWb = Workbooks.Open(strVerzeichnis & .Cells(i, "A")) "

" Calculate "
" objWb.Close 'SaveChanges:=False "

werden Dateien scheinbar im Hintergrund geöffnet aber die daraufzugreifenden INDIREKT-Formeln werden in der Hauptdatei (wo der Code steht) nicht berechnet.

Daher die Bitte ans Forum über den untenstehenden Code zu schauen, ob er umgebaut werden müsste oder wo ich den Befehl "objWb.Close" einfügen muss bzw wo er in der Schleife deklariert werden muss, um die Datei die in der Schleife ermittelt und geöffnet wird nach dem "Calculate" sofort wieder schließen zu können, um den Berechnungsaufwand gering bzw zu verhindern, das sich Excel nach mehr als 100 Dateien aufhängt.
Die erste zu öffnende Datei wird ja komischerweise wieder geschlossen bzw nicht angezeigt (es sind 9 Dateien zu öffnen, die auch geöffnet werden, aber 8 Dateien bleiben offen bestehen).

Code:

Sub nur_vorhandene_Dateien_oeffnen_und_schließen()


Dim strVerzeichnis As String, i As Long, objWb As Workbook
Dim wb As Workbook

With Application
   .EnableEvents = False
   .ScreenUpdating = False
   .DisplayAlerts = False
End With

strVerzeichnis = ThisWorkbook.Path & "\"

With Worksheets("Dateien_Ist")

    For i = 1 To .Cells(.Rows.Count, "B").End(xlUp).Row
         If UCase(.Cells(i, "B")) = "X" Then
                     
            Set objWb = Workbooks.Open(strVerzeichnis & .Cells(i, "A"))

         End If

       Calculate
       objWb.Close 'SaveChanges:=False

    Next i
End With

Set objWb = Nothing

Application.EnableEvents = True
ScreenUpdating = True
DisplayAlerts = True

End Sub


Vielen Dank im vorraus eines VBA- und SChleifenanfängers.

Norman S.

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: ralf_b
Geschrieben am: 01.11.2020 21:39:06

vielleicht dann über das end if
       Calculate
       objWb.Close 'SaveChanges:=False
 End If



Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Norman
Geschrieben am: 01.11.2020 21:44:33

Hallo ralf_b,

da hatte ich es auch schon probiert - hab quasi alle Positionen durchgespielt.
Wenn dort hin wo du meinst wird wie oben schon beschrieben etwas abgearbeitet (scheinbar die Dateien geöffnet evtl auch wieder geschlossen) aber die INDIRKT-Formeln, die auf die zu öffnenden Dateien zugreifen müssen, werden nicht berechnet (#BEZUG! bleibt erhalten).

Trotzdem Danke für´s rüber schauen.

Norman S.

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Werner
Geschrieben am: 01.11.2020 21:40:28

Hallo,

in welchem Blatt deiner "Hauptdatei" sind den deine Indirekt Formeln? Im Blatt "Dateien_Ist" ?

Dann versuchs mal mit einem Punkt vor dem Calculate. Also statt Calculate .Calculate. Damit wird auf das im With angegebene Blatt referenziert.

Hast du das ohne den Punkt, dann bezieht sich das Calculate auf das gerade aktive Blatt. Und nach dem Öffnen einer Datei (auch im Hintergrund) ist die geöffnete Datei die aktive Datei.

Gruß Werner

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: ralf_b
Geschrieben am: 01.11.2020 21:49:28

@ werner

im dem Worksheet stehen doch nur die Namen der zu öffnenden Dateien. Meinste das das Calculate dann so funktioniert? Wirkt das Calculate nicht Application weit?

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Norman
Geschrieben am: 01.11.2020 21:58:36

Hallo Werner,


die INDIREKT-Formeln stehen in mehreren Blättern (3) der Hauptdatei (wo der Code steht), die die zu öffnenden Dateien benötigen.
wenn ich den Code so belasse (also nach "End If" einfüge) und auf ".Calculate" ändere bleibt die Fehlermeldung bestehen: "Laufzeitfehler 91: Objektvariable oder With-Blockvariable nicht festgelegt"


Ändere ich den Code so wie ralp_b helfend anbietet (also vor "End If") und ändere auch hier auf: ".Calculate" werden die INDIREKT-Formeln trotzdem/auch nicht berechnet. Komisch lass ich das "objWb.Close" raus werden sie ja berechnet.

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: ralf_b
Geschrieben am: 01.11.2020 22:46:26

du könntest den calculationstatus abfragen und erst wenn der xldone ist die datei closen.

die Frage steht aber noch im Raum wann die Calculation angestoßen wird wenn du close rausnimmst. Werden vielleicht mehrere dieser Dateien für die Calculation benötigt?

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Daniel
Geschrieben am: 02.11.2020 10:53:56

Hast du da nicht sowieso einen großen Denkfehler drin?
Da du ja immer nur eine Datei öffnnest, müssen die Indirekt-Bezüge auf die anderen Dateien ja wieder auf den Bezugfehler laufen, so dass du "mit dem Arsch umschmeisst, was du mit den Händen aufgebaut hast".

Du dürfest also immer nur die Zellen neu berechnen, die einen Bezug auf die aktuell geöffnete Datei haben und müsstest die anderen Formeln davon ausschließen.

Da du sowieso ein Makro zum aktualisieren brauchst, könntest du es so erweitern dass es die benötigten Formeln erzeugt, einfügt und durch die Werte ersetzt, das würde dann auch deine Datei verschlangen.

Gruß Daniel

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Norman
Geschrieben am: 02.11.2020 20:00:49

Danke für den Hinweis Daniel!


Dachte das öffnen der verschiedenen Dateien sei kein Problem, habs jetzt aber selbst über den händischen Weg probiert und gesehen, das da wie gut geschrieben ein großer Denkfehler drin ist. (Wenn die Bäume nicht immer den Wald verdecken würden ;-)



Wenn ich ein Makro bauen müsste das die Formeln erzeugt, werde ich es wohl nicht schaffen, da Formeln zu vielfältig/komplex und meine Makro-Fähigkeiten deutlich zu sehr auf Anfängerniveau.



Alternativ köntte man auch das Öffnen der Dateien limitieren (das was die Hardware problemlos schaffen kann) und teilt das Öffnen in mehrere Schritte auf.



Ist den bekannt wieviele Exceldateien gleichzeitig geöffnet werden können - also in Praxis, nicht laut theoretischem Wert den Microsoft angibt (abhängig von Hardware)???

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Norman
Geschrieben am: 02.11.2020 20:04:37

.

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Daniel
Geschrieben am: 02.11.2020 20:17:38

"Alternativ köntte man auch das Öffnen der Dateien limitieren (das was die Hardware problemlos schaffen kann) und teilt das Öffnen in mehrere Schritte auf."

das ist nicht alternativ, das hast du bisher getan.
sobald du in mehreren Schichten arbeitest, werden die Ergebnisse der ersten Schicht gelöscht, wenn du die Dateien der ersten Schicht geschlossen hast und für die zweite Schicht die Berechnung aktualisierst.
dabei ist es egal, ob das wenige Schichten mit vielen Dateien oder viele Schichten mit wenig Dateien sind.
entweder du schaffst es, alle Dateien gleichzeitig zu öffnen und dann zu aktualisieren, oder du lässt es.
Die Anzahl der Dateien, die du gleichzeitig öffnen kannst, hängt vermutlich vom für Excel reservierten Speicherplatz ab, dh je größer die Dateien, um so weniger, je kleiner um so mehr.

Gruß Daniel

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Norman
Geschrieben am: 02.11.2020 20:39:05

Danke für deine geduldige Antwort.

Sorry, hab vergessen zu erwähnen, dass bei dem "geschichtetem Öffnen" (soviel Dateien öffnen wie die Hardware verträgt) - ich dann zwischendurch einfach die Werte in ein anderes Tabellenblatt kopieren könnte und zwischendurch den Bereich der zu öffnenden Dateien verschiebe. So könnte man schrittweise (im Blockverfahren) die nötigen INDIREKT-Formeln abarbeiten lassen und die Werte zusammensammeln.

Werde mich mal mit dem Berechnen von nur einzelnen Zellen beschäftigen.

Gibts einen Weg die Abhängigkeit einer Zelle und deren INDIREKT-Formel von der zu öffnenden Datei abzufragen (also gibts evtl einen speziellen Befehl)??

Betrifft: AW: Datei öffnen & schließen Fehler im Code?
von: Daniel
Geschrieben am: 02.11.2020 20:54:14

Hi
auch das Prinzip kannst du wahrscheinlich einfacher programmieren, wenn du es für jede Datei separat machst.

wenn die Bereiche mit diesen Formeln bekannt sind, kannst du dir zuzutze machen, dass Excel bzw VBA in der Lage ist, eine Zellauswahl über den Datentyp des Formelergebnisses zu machen (Zahl, Text ,Boolean, Fehler), dh es ist möglich, aus einem Bereich nur die Zellen mit Formel, die auch ein brauchbares Ergebnis liefern, auszuwählen.
das nennt sich SpecialCells und du kannst das von Hand im Menü Start - Bearbeiten - Suchen und Auswählen - Inhalte - Formeln nachvollziehen.

dh du kannst also eine Datei öffnen, die Werte neu berechnen und dann die Formeln, die keine Fehlerwerte liefern als Wert ins andere Blatt kopieren.

Gruß Daniel

Beiträge aus dem Excel-Forum zum Thema "Datei öffnen & schließen Fehler im Code?"