Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema MsgBox
Screenshot zu MsgBox	MsgBox-Seite mit Beispielarbeitsmappe aufrufen

Daten aus geschlossener Arbeitsmappe auslesen

• Daten aus geschlossener Arbeitsmappe auslesen von Z vom 26.10.2014 15:03:21
• Das geht mit Makro auch nur, wenn die ... - von Luc:-? am 26.10.2014 15:26:43
  • AW: Das geht mit Makro auch nur, wenn die ... - von Z am 26.10.2014 15:38:44
• AW: Daten aus geschlossener Arbeitsmappe auslesen - von Hajo_Zi am 26.10.2014 16:56:25
  • AW: Daten aus geschlossener Arbeitsmappe auslesen - von Z am 26.10.2014 17:14:46
    • AW: Daten aus geschlossener Arbeitsmappe auslesen - von Z am 26.10.2014 17:30:48
      • AW: Daten aus geschlossener Arbeitsmappe auslesen - von Hajo_Zi am 26.10.2014 17:35:36
        • AW: Daten aus geschlossener Arbeitsmappe auslesen - von Z am 26.10.2014 18:12:16
          • AW: Daten aus geschlossener Arbeitsmappe auslesen - von Z am 27.10.2014 00:24:21
            • Vorschlag zum testen - von Tino am 28.10.2014 16:09:23

Betrifft: Daten aus geschlossener Arbeitsmappe auslesen von: Z
Geschrieben am: 26.10.2014 15:03:21

Hallo Excel Freunde,
ich versuche Daten aus geschlossenen Dateien (ganze Bereiche) auszulesen. Wie ich nach Recherchen festgestellt habe gehts zwar mit indirekt, aber nach schließen der Datei ist dann eben eine Fehlermeldung "#Bezug" da. Scheint also nur mit Makro zu gehen. Und da kenne ich mich leider überhaupt nicht aus, nicht einmal wohin genau das Makro gespeichert werden muss (Modul? in der Arbeitsmappe?) Ok wie man da hinkommt das weiß ich (Alt+F11 :)).
Könnt ihr mir da weiter helfen?

Folgender Aufbau:
Es gibt einen Pfad wo die Datei "Auslesen" gespeichert ist. Hier werden aus den verschiedenen Unterordnern der Projekte aus einem File mit gleichem Namen wie der Unterordner Daten ausgelesen. In der Bsp Datei sind die Pfade auch mit angegeben (Spalten B - D, werden aber ausgeblendet). In der Datei habe ich ein Formularsteuerelement eingefügt, ist das überhaupt notwendig?
https://www.herber.de/bbs/user/93379.xlsm

Die Daten gibt der Anwender in der Datei (z. B. 14_001.xls) im Deckblatt ein, ausgelesen werden Sie aber aus einem zweiten Tabellenblatt (Reiter "Projektdaten") wo sie gesammelt hintereinander stehen. Die Datei steht in einem Unterordner (hier Ordner 14_001)
https://www.herber.de/bbs/user/93380.xlsx

Nochmal der Pfadaufbau:
C:\C-TRANSFER\Auslesen geschlossener Dateien - hier steht die Datei "Einlesen"
C:\C-TRANSFER\Auslesen geschlossener Dateien\Projektnummer (BSP: 14_001) - hier steht die Datei "Auslesen" (BSP: 14_001.xls)

Vielen Dank schon mal im Voraus und noch einen schönen Sonntag
Gruß
Raphael

Betrifft: Das geht mit Makro auch nur, wenn die ... von: Luc:-?
Geschrieben am: 26.10.2014 15:26:43

…jeweilige Mappe im Hintergrund geöffnet wird, Raphael;
die sieht man dann zwar nicht, aber offen ist sie trotzdem (im VBEditor erkennbar). Ohne INDIREKT (mit direktem FernBezug) geht's ja bekanntlich immer, aber wirklich empfehlen kann ich das aus Gründen der Datensicherheit nicht, also dann doch besser per VBA die andere Datei öffnen, Daten einlesen und QuellDatei wieder schließen. Das sollte sich auch per Recorder aufzeichnen lassen. Die betrof­fenen QuellDateien könnten auch per Liste vorgegeben wdn.
Gruß, Luc :-?

Betrifft: AW: Das geht mit Makro auch nur, wenn die ... von: Z
Geschrieben am: 26.10.2014 15:38:44

Hallo Luc,
das die Mappe im Hintergrund (schreibgeschützt) geöffnet werden muss hab ich in den Internetrecherchen auch schon gelesen, stellt aber kein Problem dar.
Wie funktioniert dasd mit der Vorgabeliste der Quelldateien? Kann die z. B. aus der Datei "Einlesen" genommen werden (Spalte B-D), dann würden aber immer alle Dateien im Hintergrund kurz aufgehen. Das kann irgendwann mal viel werden. Besser wäre es doch wenn Daten die bereits drin sind nicht noch mal abgefragt werden und nur eine Pfadangabe zur "neuen" Datei die ausgelesen werden soll angegeben wird?

Gruß
Raphael

Betrifft: AW: Daten aus geschlossener Arbeitsmappe auslesen von: Hajo_Zi
Geschrieben am: 26.10.2014 16:56:25

Hallo Raphael,

das ist aufwendig, ich würde die Datei öffnen

Option Explicit

Public Function GetDataClosedWB(SourcePath As String, _
    SourceFile As String, sourceSheet As String, _
        SourceRange As String, TargetRange As Range) As Boolean
    'Holt einen Bereich aus einer _geschlossenen_ Arbeitsmappe
    'Nur in VBA zu verwenden; nicht aus einer Tabellenzelle heraus
    '© t.ramel@mvps.org
    ' wird durch die HoleDaten aufgerufen
    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  _
closed Workbook"
    GetDataClosedWB = False
End Function

Public Sub HoleDaten()
    ' Die Funktion arbeitet mit der obrigen GetDataClosedWB zusammen
    Dim Pfad            As String
    Dim Dateiname       As String
    Dim Blatt           As String
    Dim Bereich         As String
    Dim Ziel            As Range
    Pfad = "L:\Eigene Dateien\Hajo\Internet\Test\2009\"
    Dateiname = "Beispiel Forum 30.xlsm" ' aus welcher Datei soll er holen?
    Blatt = "Tabelle1"  ' von welcher Tabelle soll er holen?
    Bereich = "A1:B9"   ' aus welchem Bereich soll er holen?
    Set Ziel = ActiveSheet.Range("A1")  ' in welchen Bereich soll er kopieren? Genauer gesagt:  _
Bei welcher Zelle soll er anfangen, Datein reinzukopieren? Bsp: ActiveCell geht auch
    If GetDataClosedWB(Pfad, Dateiname, Blatt, Bereich, Ziel) Then
        MsgBox "Daten importiert"
    End If
End Sub

Betrifft: AW: Daten aus geschlossener Arbeitsmappe auslesen von: Z
Geschrieben am: 26.10.2014 17:14:46

Hallo Hajo,
hab den Code kopiert, angepasst und mit dem Steuerelement verknüpft. Beim ersten Test kamm Syntaxfehler und die Zeilen
Public Function GetDataClosedWB(SourcePath As String, _
SourceFile As String, sourceSheet As String, _
SourceRange As String, TargetRange As Range) As Boolean
wurden markiert. Bedeutet das einen Fehler im Pfad?
Gruß
Raphael

Betrifft: AW: Daten aus geschlossener Arbeitsmappe auslesen von: Z
Geschrieben am: 26.10.2014 17:30:48

Hallo nochmal
hab den Pfad und die Datei kontrolliert, finde keien Fehler. Nach dem Pad habe ich auch das \ drin und die Datei und das Tabellenblett stimmen auch???????
Was mache ich da falsch
Gruß
Raphael

Betrifft: AW: Daten aus geschlossener Arbeitsmappe auslesen von: Hajo_Zi
Geschrieben am: 26.10.2014 17:35:36

Hallo Raphael,

schaue in den Code der ist nicht von mir.
Ich vermute die Verknüpfung mit dem Steuerelement ist das Problem?
TakeFocusOnClick mal ändern?

Gruß Hajo

Betrifft: AW: Daten aus geschlossener Arbeitsmappe auslesen von: Z
Geschrieben am: 26.10.2014 18:12:16

Hallo Hajo,
ich kenne VBA gar nicht. Was bedeutet "Nur in VBA zu verwenden; nicht aus einer Tabellenzelle heraus?" kann man kein Steuerelement verwenden?
Und wie ist es dann wenn ich dann Daten aus einer neuen anderen (z.B. 14_002) Datei holen will in die nächste Zeile? Jedes mal das VBA umschreiben?
Gruß
Raphael

Betrifft: AW: Daten aus geschlossener Arbeitsmappe auslesen von: Z
Geschrieben am: 27.10.2014 00:24:21

Hallo nochmal,
nach langen probieren und suchen hats nun geklappt :)
Jetzt schreibt mir das Makro eine Zeile wie explizit im Makro angegeben.

Aber, wenn ich nun immer wieder in verschieden Zeilen was reinschreiben muss, wie mache ich das? Kann ich dem Makro beibringen zu fragen, aus welchem Pfad, welcher Datei er lesen soll und ab welcher Zeile er eintragen soll. Die bereits vorhandenen Zeilen sollen dabei natürlich nicht überschrieben werden.

Folgendes nutze ich zum reinschreiben:

Public Sub HoleDaten()
    ' Die Funktion arbeitet mit der obrigen GetDataClosedWB zusammen
    Dim Pfad            As String
    Dim Dateiname       As String
    Dim Blatt           As String
    Dim Bereich         As String
    Dim Ziel            As Range
    Pfad = "C:\Test\Projekte\14_001\"
    Dateiname = "14_001.xlsx" ' aus welcher Datei soll er holen?
    Blatt = "Datenübertrag"  ' von welcher Tabelle soll er holen?
    Bereich = "A4:AN4"   ' aus welchem Bereich soll er holen?
    Set Ziel = ActiveSheet.Range("F5")  ' in welchen Bereich soll er kopieren? Genauer  _
gesagt: _
Bei welcher Zelle soll er anfangen, Datein reinzukopieren? Bsp: ActiveCell geht auch
    If GetDataClosedWB(Pfad, Dateiname, Blatt, Bereich, Ziel) Then
        MsgBox "Daten importiert"
    End If
End Sub

Die fett geschriebenen müssten demnach immer angepasst werden. Kann in einer MAske abgefragt werden.
Den Pfad (mit letztem \, die Datei mit Endung und der Name des Tabellenblattes habe ich auch in der jeweiligen Zeile schon gebildet (Funktion &), kann man dem Makro sagen, es soll eben eine Zelle auslesen?
Ist in der Beispieldatei https://www.herber.de/bbs/user/93379.xlsm auch schon beschrieben

Danke schon mal
Gruß
Raphael

Betrifft: Vorschlag zum testen von: Tino
Geschrieben am: 28.10.2014 16:09:23

Hallo,
erweitere Deine Tabelle wie in Zeile 3 gezeigt,
Adresse wo sich die Daten in der Tabelle stehen. (kann ja auch ausgeblendet werden)
Achte darauf das unterhalb von Spalte B keine anderen Daten stehen und rechts von Spalte 3 auch nicht!

	A	B	C	D	E	F	G	H	I
3					C7	F7	I7	C9	F9
4	Nummer	Pfad	Datei	Blatt	Anforderer	Abteilung	akt. Datum	Ziel der Kalkulation	bis
5	14_001	C:\C-TRANSFER\Auslesen geschlossener Dateien\14_001\	14_001.xlsx	Projektdaten

In ein Modul kommt dieser Code, diesen weist Du dem Button zu.

Sub Lese_Daten()
Dim ArDataFile, ArDataCell, ArErgebnis()
Dim sFormel$, sFileFullPath$
Dim n&, nn&

With ThisWorkbook.ActiveSheet  'Tabelle evtl. anpassen 
    n = .Cells(.Rows.Count, 2).End(xlUp).Row
    If n < 5 Then Exit Sub
    ArDataFile = .Range("B5", .Cells(n, 2)).Resize(, 3)
    
    n = .Cells(3, .Columns.Count).End(xlToLeft).Column
    If n < 5 Then Exit Sub
    ArDataCell = .Range("E3", .Cells(3, n).Resize(, 2))
    Redim Preserve ArDataCell(1 To 1, 1 To Ubound(ArDataCell, 2) - 1)
    
    Redim Preserve ArErgebnis(1 To Ubound(ArDataFile), 1 To Ubound(ArDataCell, 2))
    
    On Error Resume Next
    
    For n = 1 To Ubound(ArDataFile)
        If ArDataFile(n, 1) <> "" Then
            sFileFullPath = ArDataFile(n, 1) & ArDataFile(n, 2)
            ChDrive ArDataFile(n, 1)
            ChDir ArDataFile(n, 1)
            If Dir$(sFileFullPath, vbNormal) <> "" Then
                For nn = 1 To Ubound(ArDataCell, 2)
                    If ArDataCell(1, nn) <> "" Then
                        sFormel = "'" & ArDataFile(n, 1) & "[" & ArDataFile(n, 2) & "]" & _
                        ArDataFile(n, 3) & "'!" & .Range(ArDataCell(1, nn)).Address(ReferenceStyle:=xlR1C1)
                        ArErgebnis(n, nn) = ExecuteExcel4Macro(sFormel)
                    End If
                Next nn
            Else
                For nn = 1 To Ubound(ArDataCell, 2)
                    ArErgebnis(n, nn) = "'Error"
                Next nn
            End If
        End If
    Next n
    
    .Range("E5").Resize(Ubound(ArErgebnis), Ubound(ArErgebnis, 2)) = ArErgebnis
End With
End Sub

Gruß Tino

Beiträge aus den Excel-Beispielen zum Thema "Daten aus geschlossener Arbeitsmappe auslesen"