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

Funktion GetDataClosedWB

Forumthread: Funktion GetDataClosedWB

Funktion GetDataClosedWB
21.11.2015 11:33:14
Michael
Hallo Zusammen,
das folgende Makro verwende ich schon lange, um aus einer geschlossenen Datei Daten auszulesen (habe ich aus dem Internet):
Public Function GetDataClosedWB(SourcePath As String, _
SourceFile As String, sourceSheet As String, _
SourceRange As String, TargetRange As Range) As Boolean
Dim strQuelle       As String
Dim Zeilen          As Long
Dim Spalten         As Byte
On Error GoTo InvalidInput
strQuelle = "'" & SourcePath & "[" & SourceFile & "]" & sourceSheet & "'!" & Range( _
SourceRange).Cells(1, 1).Address(0, 0)
Zeilen = Range(SourceRange).Rows.Count
Spalten = Range(SourceRange).Columns.Count
With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
.Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
.Value = .Value
End With
GetDataClosedWB = True
Exit Function
InvalidInput:
MsgBox "Die Quelldatei oder der Quellbereich ist ungültig!", vbExclamation, "Get data from"
GetDataClosedWB = False
End Function


Public Sub Daten_holen()
Dim Pfad            As String
Dim DateiName       As String
Dim Blatt           As String
Dim Bereich         As String
Dim Ziel            As Range
Pfad = Range("Dateipfad").Value
Application.Calculation = xlCalculationManual
DateiName = Range("Dateiname").Value
Blatt = Range("Tabellenname").Value
Bereich = Range("Kopierbereich").Value
Set Ziel = Tabelle5.Range("K11")
If GetDataClosedWB(Pfad, DateiName, Blatt, Bereich, Ziel) Then
Jahresdaten1_holen
End If
Application.Calculation = xlCalculationAutomatic
End Sub
Das klappte auch immer, weil die Zieldatei immer einen festen Namen hatte. Jetzt hat die Zieldatei immer einen anderen Namen, nur ein Bestandteil (6 Zeichen, z.B. "Einkauf") sind irgentwo innerhalb des Dateinamens immer gleich.
Wie muss ich denn das Makro ändern, damit das Makro die richtige Datei findet?
Vielen Dank für Eure Unterstützung
Michael

Anzeige

7
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Folgeproblem
21.11.2015 11:49:45
Michael
Hallo Zusammen,
ich habe leider zu spät bemerkt, dass sich auch der Blattname in der Zieldatei ändert. Es ist das einzige Blatt in der Mapppe.
Kann ich dieses Tabellenblatt im Makro auch anders "anprechen"?
Mit Sheets(1) hat es leider nicht funktioniert?!
Vielen Dank!
Gruß
Michael

Anzeige
Glaskugelproblem
21.11.2015 16:59:22
Michael
Hallo Michael,
anhand welcher Kriterien ist denn erkennbar, welche Datei die richtige ist? Wir sind keine Hellseher, Excel auch nicht.
Also: liegt sie in einem festen Ordner oder variiert der auch? Ist eine Datumsangabe enthalten?
Gibt es in besagtem Ordner am Ende mehrere Datein, die "Einkauf" enthalten?
Überleg Dir mal, wie Du händisch herausfindest, welche Datei die richtige ist, dann kann man versuchen, das in Excel nachzuvollziehen.
Schöne Grüße,
Michael

Anzeige
Eindeutig zu identifizieren
21.11.2015 17:30:35
Michael
Hallo Michael,
jede Datei hat einen String, anhand dessen die Zieldatei eindeutig zu identifizieren ist!
Also z.B. kommt innerhalb des Dateinamens der Zieldatei der Begriff "Einkauf" vor, jeweils von den anderen Begriffen durch einen _ getrennt.
Der Zielordner steht ebenfalls fest.
Also benötigt man zum Glück keine Glaskugel, sondern "nur" eine Möglichkeit, die Zieldatei anhand des eindeutigen Begriffes zu identifizieren.
Viele Grüße
Michael

Anzeige
AW: Eindeutig zu identifizieren
21.11.2015 18:48:53
Michael
Hi,
das geht mit dem Dir-Befehl recht gut.
Hier eine Test-Routine, die auf Pfade in Spalte A und Datei-"Schnipsel" in Spalte B zugreift und das Ergebnis in Spalte C schreibt: 
Sub testen()
Dim i As Long
For i = 1 To 4
Range("C" & i) = Dir(Range("A" & i) & "*" & Range("B" & i) & "*")
Next
End Sub
In Deine Funktion eingebaut sähe das dann etwa so aus: 
Public Sub Daten_holen()
Dim Pfad            As String
Dim DateiName       As String
Dim Blatt           As String
Dim Bereich         As String
Dim Ziel            As Range
Dim trennen         As Long
Pfad = Range("Dateipfad").Value
DateiName = Range("Dateiname").Value
DateiName = Dir(Pfad & "*" & DateiName & "*")
If DateiName = "" Then
MsgBox "Pfad+Datei " & Pfad & DateiName & " nicht gefunden."
Exit Sub
End If
MsgBox "Pfad:  " & Pfad & vbLf & _
"Datei: " & DateiName
'       Blatt = Range("Tabellenname").Value
'       Bereich = Range("Kopierbereich").Value
'       Set Ziel = Tabelle5.Range("K11")
'       Application.Calculation = xlCalculationManual
'       If GetDataClosedWB(Pfad, DateiName, Blatt, Bereich, Ziel) Then
'       Jahresdaten1_holen
'       End If
'       Application.Calculation = xlCalculationAutomatic
End Sub

Die Kommentarzeichen in den unteren Zeilen entfernst Du bitte wieder.
Allerdings ist die ganze Geschichte etwas umständlich. Erstens kann man nicht aus "geschlossenen" Dateien lesen - wie man es auch anstellt, die Datei ist vorübergehend geöffnet (d.h., sie wird vom Datenträger ins RAM geladen), sonst könnte man sie ja nicht "auslesen".
Darin liegt letztlich auch die Lösung des Problems mit den Blattnamen. In VBA geht "Sheets(1)" natürlich klaglos, aber was in Deinem Code passiert, ist, daß eine Excel (NICHT VBA)-Funktion nachgebildet wird, nämlich der schlichte Verweis =Datei/Blatt/Zelle, wie Du es nach einem = auch tatsächlich Zell-, Blatt- oder auch Datei-übergreifend eingeben würdest - und hier geht eben das "sheets(1)" nicht.
Ich würde schlicht die zweite Datei öffnen, den Bereich kopieren und sie wieder schließen, das ist in ein paar Zeilen erledigt: wenn die zweite Datei nämlich nur ein Blatt hat, ist das immer das, was ohne Zutun aktiv ist.
Schöne Grüße,
Michael

Anzeige
Danke (mit Rückfrage)
21.11.2015 19:43:06
Michael
Hallo Michael,
vielen Dank für Deine Mühe, insbesondere für Deine ausfühlichen Anmerkungen!
Wenn ich alles richtig verstanden habe (bin VBA-Laie), dann ist es in "meiner" Lösung unmöglich, das Tabellenblatt abzugreifen, wenn der Name des Blattes unbekannt ist?
Das wäre schade, denn das öffnen der Dateien hier im Netzwerk dauert oft sehr lange, insbesondere wenn sehr viele (ca. 18 Dateien) eingelesen werden sollen. Bisher klappte das mit meiner alten Lösung prima.
Dann werde ich mit wohl eine Lösung mit Öffen der Datei basten müssen?!
Viele Grüße
Michael

Anzeige
AW: Danke (mit Rückfrage)
22.11.2015 16:48:48
Michael
Hi Michael,
ich habe noch ein bißchen hinterherrecherchiert: excel sheet name of closed file
Es scheint tatsächlich so zu sein, daß das nicht geht; der MrExcel-Hauptlink enthält ein Stück VBA (unten, Januar 2012), zu dem aber kommentiert wird, daß die Datei ja denn doch geöffnet wird.
Insbesondere habe ich dabei irgendwo gelesen, daß eine Zuweisung zu einer *geschlossenen* Datei OHNE Blattname nicht geht (also entsprechend händisch eingegebenem =[Datei1]!B3)
Die deutsche Recherche: [excel blattname geschlossene datei] ergibt u.a. das:
https://www.herber.de/forum/archiv/652to656/654320_Blattnamen_und_Inhale_aus_geschlossenen_Dateien.html
Das ist im Prinzip das Gleiche, nur daß die englische Quelle dem Objekt alle Worksheets zuweist, die in unserem Forum die komplette Datei.
Warum lange theoretisieren? Probieren wir's aus: 
Public Sub Daten_holen()
Dim pfad            As String
Dim DateiName       As String
Dim Blatt           As String
Dim Bereich         As String
Dim Ziel            As Range
Dim trennen         As Long
pfad = Range("Dateipfad").Value
DateiName = Range("Dateiname").Value
DateiName = Dir(pfad & "*" & DateiName & "*")
If DateiName = "" Then
MsgBox "Pfad+Datei " & pfad & DateiName & " nicht gefunden."
Exit Sub
End If
MsgBox "Pfad:  " & pfad & vbLf & _
"Datei: " & DateiName
'***********neu ************
Dim obj As Object
Set obj = GetObject(pfad & DateiName)
Blatt = obj.Sheets(1).Name
MsgBox Blatt
Set obj = Nothing
'***********neu ************
'       Blatt = Range("Tabellenname").Value  ' diese Zeile dann natürlich ganz raus!
'       Bereich = Range("Kopierbereich").Value
'       Set Ziel = Tabelle5.Range("K11")
'       Application.Calculation = xlCalculationManual
'       If GetDataClosedWB(Pfad, DateiName, Blatt, Bereich, Ziel) Then
'       Jahresdaten1_holen
'       End If
'       Application.Calculation = xlCalculationAutomatic
End Sub
Der neue Code braucht auf meiner lokalen Maschine schon spürbare Sekundenbruchteile; probiere es aus, wie es sich im Netz gestaltet.
Schöne Grüße,
Michael

Anzeige
Nachtrag
22.11.2015 17:01:21
Michael
Hi Michael,
ich habe nochmal rumgeschaut; es gibt hier einen alten Thread zum Thema, der etwas anders arbeitet:
https://www.herber.de/forum/archiv/320to324/322712_Daten_aus_geschlossener_Arbeitsmappe_kopieren.html
Aber: es ist *immer* nötig, den Blattnamen zu wissen.
So, wie sich Dein Problem gestaltet, sollte man vielleicht insgesamt Überlegungen zu Datenstrukturen anstellen, die, wenn sie denn stückweise über Jahre entstanden sind, nicht immer optimal sind.
Eine kleine Krücke wäre, evtl. auf dem Server zu jeder Datei den Blattnamen zusätzlich als Textdatei zu speichern, so á la Blabla_Einkauf_Blabla.Blatt.
Dann müßtest Du nicht die Excel-Tabelle "öffnen", um den Blattnamen zu ermitteln, sondern könntest die Textdatei auslesen.
Ansonsten sehe ich wirklich nur die Möglichkeit, die Datein zu öffnen und den Bereich zu kopieren.
Ciao,
M.
Anzeige
;
Anzeige

Infobox / Tutorial

Daten aus geschlossenen Excel-Dateien abrufen

Schritt-für-Schritt-Anleitung

Um Daten aus einer geschlossenen Excel-Datei abzurufen, kannst du das folgende Makro verwenden. Stelle sicher, dass du die Pfade und Dateinamen entsprechend anpasst.

  1. Öffne Excel und drücke ALT + F11, um den Visual Basic for Applications (VBA) Editor zu öffnen.
  2. Füge ein neues Modul ein: Rechtsklick auf "VBAProject (DeineDatei.xlsx)" > Einfügen > Modul.
  3. Kopiere den folgenden Code in das Modul:
Public Function GetDataClosedWB(SourcePath As String, _
SourceFile As String, sourceSheet As String, _
SourceRange As String, TargetRange As Range) As Boolean
    Dim strQuelle       As String
    Dim Zeilen          As Long
    Dim Spalten         As Byte
    On Error GoTo InvalidInput
    strQuelle = "'" & SourcePath & "[" & SourceFile & "]" & sourceSheet & "'!" & Range(SourceRange).Cells(1, 1).Address(0, 0)
    Zeilen = Range(SourceRange).Rows.Count
    Spalten = Range(SourceRange).Columns.Count
    With TargetRange.Cells(1, 1).Resize(Zeilen, Spalten)
        .Formula = "=IF(" & strQuelle & "="""",""""," & strQuelle & ")"
        .Value = .Value
    End With
    GetDataClosedWB = True
    Exit Function
InvalidInput:
    MsgBox "Die Quelldatei oder der Quellbereich ist ungültig!", vbExclamation, "Get data from"
    GetDataClosedWB = False
End Function
  1. Definiere eine Subroutine, um die Daten zu holen:
Public Sub Daten_holen()
    Dim Pfad            As String
    Dim DateiName       As String
    Dim Blatt           As String
    Dim Bereich         As String
    Dim Ziel            As Range

    Pfad = Range("Dateipfad").Value
    DateiName = Range("Dateiname").Value
    Blatt = Range("Tabellenname").Value
    Bereich = Range("Kopierbereich").Value
    Set Ziel = Tabelle5.Range("K11")

    If GetDataClosedWB(Pfad, DateiName, Blatt, Bereich, Ziel) Then
        ' Weitere Verarbeitung hier
    End If
End Sub
  1. Stelle sicher, dass die Zieldatei und der Quellbereich korrekt in den Zellen definiert sind, bevor du das Makro ausführst.

Häufige Fehler und Lösungen

  • Fehler: Die Quelldatei oder der Quellbereich ist ungültig!

    • Lösung: Überprüfe den Pfad und den Dateinamen. Achte darauf, dass der Blattname korrekt angegeben ist.

  • Fehler: Die Datei wird nicht gefunden.

    • Lösung: Stelle sicher, dass der Pfad zur Datei korrekt ist und dass die Datei tatsächlich existiert.

  • Blattname nicht gefunden.

    • Lösung: Verwende Sheets(1) nicht, da dies nicht für geschlossene Dateien funktioniert. Stelle sicher, dass du den Blattnamen korrekt abgreifst oder die Datei vorher öffnest.

Alternative Methoden

Wenn du die Datei nicht direkt über VBA auslesen kannst, könnte eine alternative Methode das Öffnen der Datei sein. Hier ist ein Beispiel, wie du das machen kannst:

Public Sub Daten_holen_und_kopieren()
    Dim Pfad            As String
    Dim DateiName       As String
    Dim Ziel            As Range
    Dim wb              As Workbook

    Pfad = Range("Dateipfad").Value
    DateiName = Dir(Pfad & "*" & Range("Dateiname").Value & "*")

    If DateiName <> "" Then
        Set wb = Workbooks.Open(Pfad & DateiName)
        ' Hier kannst du die Daten kopieren
        wb.Close SaveChanges:=False
    Else
        MsgBox "Datei nicht gefunden."
    End If
End Sub

Praktische Beispiele

  • Beispiel 1: Wenn du Daten aus einer Datei mit dem Namen "Einkauf_2023.xlsx" abrufen möchtest, stelle sicher, dass der Dateipfad und die Zellen für Dateiname und Blattname korrekt befüllt sind.

  • Beispiel 2: Wenn die Datei nicht im erwarteten Ordner liegt, kannst du den Ordner durch einen Dialog auswählen:

Pfad = Application.GetOpenFilename("Excel Files (*.xls; *.xlsx), *.xls; *.xlsx")

Tipps für Profis

  • Verwende den Dir-Befehl, um nach Dateien zu suchen, die einem bestimmten Muster entsprechen. Dies kann dir helfen, die korrekte Datei zu finden, auch wenn der Dateiname variiert.

  • Implementiere einen Mechanismus, um den Blattnamen dynamisch zu ermitteln, indem du die geschlossene Datei vorübergehend öffnest.

  • Halte deine VBA-Umgebung sauber, indem du nicht verwendete Variablen und Module regelmäßig entfernst.

FAQ: Häufige Fragen

1. Kann ich Daten aus einer geschlossenen Datei lesen, ohne sie zu öffnen? Ja, du kannst Daten aus einer geschlossenen Datei lesen, indem du eine Funktion wie GetDataClosedWB verwendest.

2. Was mache ich, wenn der Blattname unbekannt ist? Wenn der Blattname unbekannt ist, kannst du die Datei zunächst öffnen und den Namen des ersten Blattes programmgesteuert abrufen.

3. Wie finde ich eine Datei, wenn ich nur einen Teil des Dateinamens kenne? Nutze den Dir-Befehl in VBA, um nach Dateien zu suchen, die einen bestimmten Teil des Namens enthalten.

4. Ist das Öffnen der Datei immer notwendig? Um den Blattnamen zu ermitteln oder auf die Daten zuzugreifen, musst du die Datei in der Regel öffnen, insbesondere wenn der Blattname nicht bekannt ist.

Beliebteste Forumthreads (12 Monate)

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