Microsoft Excel

Herbers Excel/VBA-Archiv

nur bestimmte Dateien aus Liste öffnen

Betrifft: nur bestimmte Dateien aus Liste öffnen von: Norman
Geschrieben am: 25.10.2020 18:25:12

(Rekonstruierter Text, da Thema nicht mehr in "meinen Beiträgen" angezeigt)

!!! Für aktuelle Rückfrage bitte in den letzten Abschnitt springen!!! Bzw. ab fettgeschrieben: Aktuelle Rückfrage zu lesen ist


Hallo weises Forum,

ich habe (auch in diesen) Forum nach einem ähnlichen Fall gesucht, aber nur unzureichende Lösungen gefunden.

Daher eine seperate Anfrage:

Ich möchte mit VBA nur bestimmte Dateien aus einer Tabelle mittels Bedingung per VBA öffnen (um die INDIREKT-Formeln berechnen zu lassen, die die zu öffnenden Datein benötigen).

Alternativ ginge es auch, eine Liste mit den zu öffnenden Dateien anzulegen, um die Bedingungsabfrage zu vermeiden, die die zu öffnenden Dateien ermitteln würde (SVERWEIS oder ZÄHLENWENNS).

Die Tabelle baut sich folgendermaßen auf:

Dateiname (A:A), Datei für aktuelles Quartal vorhanden? (B:B)

Nun sollen nur die Dateinamen geöffnet werden, die in Spalte B die Bedingung erfüllen. Alle anderen nicht. Alle zu potenziell zu öffnenden Dateien liegen in einem Ordner.

Ich habe Lösungen gefunden, die alle Dateien eines Ordners öffnen (falls gewünscht nur bestimmte Excel-Dateiformate. Diese Lösungwürde aber zu viel Rechenzeit beanspruchen, da mehrere hundert Dateien geöffnet werden müssten.

Meine Kenntnisse von VBA sind eher auf Beginner-Level. Daher suche ich mir noch die Dinge zusammen, die ich denke zu benötigen ;-)

Die Grundlage des Code zum öffnen einer Datei wäre diese:

Sub Referenz_Datei_öffnen()
  Dim objWb As Workbook 
  Dim strVerzeichnis As String, strTyp As String, strDateiname As String
  Dim Endung As String
  Dim Trennstrich As String
  Dim Pfad As String
  Dim Wert As String

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

End With
  
    Wert = Range("C9")
    Quartal = Range("B9")
    Trennstrich = "_"
    Endung = ".xlsx"

strVerzeichnis = ActiveWorkbook.Path & "\" & Wert & Trennstrich & Quartal
strDateiname = Dir(strVerzeichnis & Endung)

do While strDateiname <> ""
  Set objWb = Workbooks.Open(Filename:=strDateiname)
    Calculate
    objWb.Close False
    Next

With Application
    .EnableEvents = True
    .ScreenUpdating = True
    .DisplayAlerts = True
    .Calculation = lngCalc
  End With
  
  Set objWb = Nothing
End Sub


Ich würde gern den Code anpassen, damit er nun nur die Datein öffnet, die für das aktuelle Quartal als Wert in der Tabelle vorliegen.

Zur Zeit öffnet der Code nur eine einzelne (bestimmte) Datei (erstellt aus aus B9:C9 plus Trennstrich, aktuellem Quartalwert und Dateiendung). Das soll der automatischen Öffnung von Datein dienen, bei der sich der Dateiname varaibel selbst erstellt. Daher benötige ich die INDIREKT-Funktion (zur Verlinkung der auszulesenden Daten). Mit der INDIREKT-Funktion muss ich aber nur die zu öffnenden Dateien mittels VBA öffnen lassen.

Ih hoffe ich habe mich einigermaßen verständlich ausgedrückt und hoffe auf mögliche Antworten. Bisher fand ich immer Lösungen hier im Forum - diemal leider nicht.

Danke vorab

Norman

nach oben nach unten
Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: ralf_b
Geschrieben am: 26.09.2020 16:05:58

Nur aus Neugierde: do while - next funktioniert bei dir ?

nach oben nach unten
Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: Norman
Geschrieben am: 30.09.2020 12:58:05


Hallo Ralf,

:-) sicher geht dies bei mir. Ich hab nur das Problem, dass ich irgendwie eine Denkstruktur besitze, die es mir deutlich erschwert die Schleifen-Logik ins Bewusstsein zu bekommen - ich kappiers einfach nicht!

Werd´ mir mal andere Do-While-Bsp anschauen, ob ich das was finde. Scheinbar ist die Lösung so einfach/nahe das ich wohl keine direkte Hilfe bekomme ;-)

Trozdem Danke
Norman

nach oben nach unten
Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: Norman
Geschrieben am: 30.09.2020 13:13:03

Beitrag als noch offen markiert, da Lösung noch nicht gefunden

-Norman


nach oben nach unten
Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: ralf_b
Geschrieben am: 30.09.2020 22:34:41

Hallo Norman,

Ich frage ja nicht umsonst. Es gibt do while, oder for next Schleifen . Aber do next gibts nicht. Bei mir läuft der Debugger auf einen Fehler "Next ohne for". Es ist nicht hilfreich für eine Lösungssuche das man einen Code vorgesetzt bekommt, der angeblich funktioniert aber gar nicht funktionieren kann.

Nun zu deinem Problem. Also du benötigst eine Schleife über den Zellbereich wo die Dateinamen drin stehen. Zumindest hab ich das so verstanden.
z.b.
for i = ersteZeile to letzteZeile
' Für jede Zelle im Bereich prüfst du die Bedingung mit einer If abfrage z.b.
If Range("B" & i) = "x" then
' zusammenbau des Pfades
'Öffnen der datei
end if
next


aktuelle Rückfrage:

Hallo und Danke Ralf,

sorry für die verspätete Rückmeldung - war leider beruflich verhindert.

Ich hab meine bescheidenen VBA-Kenntnisse genutzt um deinen Vorschlag umzusetzten. Leider bekomme ich den Laufzeitfehler 1004 Anwendungs- oder objektorientierterFehler

Mein Code sieht derzeit wie folgt aus:

Sub nur_vorhandene_Dateien_oeffnen()

 Dim objWb As Workbook
 Dim strVerzeichnis As String, strTyp As String, strDateiname As String
 
With Application
    .EnableEvents = False
    .ScreenUpdating = False
    .DisplayAlerts = False

End With

strVerzeichnis = ActiveWorkbook.Path & "\"
strDateipfad = Dir(strVerzeichnis)

For i = ersteZeile To letztezeile
' Für jede Zelle im Bereich prüfst du die Bedingung mit einer If abfrage z.b.
If Range("B" & i) = "x" Then
' zusammenbau des Pfades:
    Set objWb = Workbooks.Open(Filename:=strDateipfad & Range("A"))
'Öffnen der datei --> hab ich vorher schon referenziert und beim zusammenbau des Pfades  _
alternativ getan

End If
Next

End Sub

Hab den Code ins entsprechende Tabellenblatt eingefügt.
Hab auch versucht die Range mittels

Dim myRange As Range
Set myRange = ActiveSheet.Range(ActiveSheet.Cells(AE, 1), ActiveSheet.Cells(AE, 30))
myRange.Select

zu ändern, leider ohne Ergebnis.

Ich habe die Vermutung, dass es an der Referenziereung des Dateipfades/-names oder der Range Methode liegt.

Irgendwelche Fehler die fortgeschrittene Excelanwender erkennen?

Danke & Gruß
Norman

Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: Norman
Geschrieben am: 25.10.2020 18:33:15

Nachtrag, nachdem ich den Code mit F8 einzeln durchgegangen bin:

Der Laufzeitfehler erscheint in Zeile:

If Range("B" & i) = "x" Then

-Norman

Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: Norman
Geschrieben am: 25.10.2020 18:34:27

Nachtrag, nachdem ich den Code mit F8 einzeln durchgegangen bin:

Der Laufzeitfehler erscheint in Zeile:

If Range("B" & i) = "x" Then

-Norman

Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: Werner
Geschrieben am: 25.10.2020 18:45:23

Hallo,

was erwartest du denn?
Die beiden Variablen ersteZeile und letzteZeile sind in deinem Code weder deklariert, geschweige denn sind ihnen Werte zugewiesen. Beide Variablen sind somit leer.
For i = ersteZeile To letztezeile

somit hat hier
If Range("B" & i) = "x" Then

die Variable i den Wert 0 - und eine Zelle B0 gibt es nicht.

Gruß Werner

Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: ralf_b
Geschrieben am: 25.10.2020 18:52:36

Set myRange = ActiveSheet.Range(ActiveSheet.Cells(AE, 1), ActiveSheet.Cells(AE, 30))
activesheet kannst du weglassen

Range(.Cells(1,"AE"), Cells(30,"AE"))

cells(Zeile, Spalte) aber Range( "Spalte & zeile")

Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: Norman
Geschrieben am: 25.10.2020 20:16:05


Hallo Werner und Ralf,

Danke für die angebrachte Schelte und den Hinweis :-)

hab den Code entsprechend angepasst und alles auf ein neues Tabellenblatt ausgelagert, das die Werte nur noch in Spalte A u B stehen.

Bekomme nun aber den Laufzeitfehler 1004

in der Zeile:
myRange.Select

kommentiere ich die Zeile aus, dann läuft der Code fehlerfrei aber ohne eine Datei zu öffnen.

Hab den Code erstmal so belassen das die zu öffnende Datei(en) ja offen bleiben müsste(n) - siehe aktuellen Code weiter unten.
Da ich nicht sicher bin ob die Referenzierung für das Öffnen der Datei(en) funktioniert, weis ich nicht welchen Lösungsansatz ich wählen muss.

Die Dateinamen setzten sich aus dem Dateipfad der Excel-Berechnungs-Datei (wo der Code eingefügt ist) und
dem per "INDIREKT-Funktion" erstelltem Exceldateinamen, die in Spalte "A" stehen geöffnet werden.

Ist die unzureichende Referenzierung der zu öffnenden Dateinamen oder "myRange.Select" die Fehlerquelle?

-Norman



Exceldatei ist wie folgt aufgebaut:

Spalte A:
Dateinamen mit INDIREKT-Funktion aus "Sensornummer" & "_" & "Quartal" & ".xlsx" zusammengebaut

Bsp: CBI218491_03-19.xlsx



Spalte B:
ermittelt ob Datei im Verzeichnis vorhanden und wenn ja, dann "x"

Alle Dateien mit "x" in Spalte B sollen geöffnet werden.



Der Code sieht aktuell so aus:

Derzeit werden zu Testzwecken nur 30 Zeilen überprüft, später sollen aber bis zu 9000 Zeilen per SChleife abgearbeitet werden.

Sub nur_vorhandene_Dateien_oeffnen()

 Dim objWb As Workbook
 Dim strVerzeichnis As String, strTyp As String, strDateiname As String

 Dim myRange As Range
 Set myRange = Range(Cells(1, "A"), Cells(30, "B"))
 myRange.Select

 Dim ersteZeile As Integer
 Dim letzteZeile As Integer
 
ersteZeile = 1
letzteZeile = 30

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

End With


strVerzeichnis = ActiveWorkbook.Path & "\"
strDateipfad = Dir(strVerzeichnis)

For i = ersteZeile To letzteZeile
If myRange("B" & i) = "x" Then
    ' zusammenbau des Pfades & öffnen:
    Set objWb = Workbooks.Open(Filename:=strDateipfad & Range("A"))

End If
Next

End Sub


Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: ralf_b
Geschrieben am: 25.10.2020 21:58:33

wofür brauchst du das select? die zeile kannste weglassen.

Betrifft: AW: nur bestimmte Dateien aus Liste öffnen
von: Werner
Geschrieben am: 26.10.2020 06:26:22

Hallo,

ziemliches Durcheinander.
Sub nur_vorhandene_Dateien_oeffnen()
Dim strVerzeichnis As String, i As Long, objWb As Workbook

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

strVerzeichnis = ThisWorkbook.Path & "\"

With Worksheets("Tabelle1")
    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
    Next i
End With
            
Set objWb = Nothing
Application.EnableEvents = True
End Sub
Gruß Werner

Beiträge aus dem Excel-Forum zum Thema "nur bestimmte Dateien aus Liste öffnen"