Funktion GetDataClosedWB

Informationen und Beispiele zu den hier genannten Dialog-Elementen:
MsgBox
Bild

Betrifft: Funktion GetDataClosedWB
von: Michael
Geschrieben am: 21.11.2015 11:33:14

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

Bild

Betrifft: Folgeproblem
von: Michael
Geschrieben am: 21.11.2015 11:49:45
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

Bild

Betrifft: Glaskugelproblem
von: Michael
Geschrieben am: 21.11.2015 16:59:22
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

Bild

Betrifft: Eindeutig zu identifizieren
von: Michael
Geschrieben am: 21.11.2015 17:30:35
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

Bild

Betrifft: AW: Eindeutig zu identifizieren
von: Michael
Geschrieben am: 21.11.2015 18:48:53
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

Bild

Betrifft: Danke (mit Rückfrage)
von: Michael
Geschrieben am: 21.11.2015 19:43:06
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


Bild

Betrifft: AW: Danke (mit Rückfrage)
von: Michael
Geschrieben am: 22.11.2015 16:48:48
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

Bild

Betrifft: Nachtrag
von: Michael
Geschrieben am: 22.11.2015 17:01:21
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.

 Bild

Beiträge aus den Excel-Beispielen zum Thema "Funktion GetDataClosedWB"