WVERWEIS mit Bezug auf mehrere dynamische, externe Dateien (VBA)

paul.david, Mittwoch, 08.05.2019, 11:18 (vor 135 Tagen)

Hallo Zusammen,

ich habe eine Tabelle, in der für jeden Mitarbeiter (Spalte B3:B14) und Monat (Zeile C2:Z2) erfasst werden soll, wieviel Stunden derjenige gearbeitet hat.
Nun sollen die entsprechenden Werte aus einzelnen Dateien gezogen werden. Der Dateiname ist immer gleich: Info_“Name“ („Name“ entsprechend dem Wert in Spalte B).
Auch der Aufbau in den Dateien, aus denen die Werte ausgelesen werden sollen, ist immer gleich. In Spalte E4:E14 stehen die Monate (Jan-Dez) rechts daneben in Spalte F3:F14 die entsprechenden auszulesenden Werte.

Es gibt also zwei Herausforderungen:
1. Die Werte aus verschiedenen Dateien mit dynamischen Dateinamen auslesen
2. Die Suchkriterien in der Zieldatei sind horizontal angeordnet (Zeile C2:Z2), in den auszulesenden Dateien jedoch vertikal (E4:E14 bzw. F3:F14).

Ich habe bereits einen SVERWEIS mit Bezug auf Dateien mit dynamischen Dateinamen hinbekommen, jedoch gelingt es mir bisher nicht es entsprechend umzuschreiben :( unten seht ihr den alten Code)

Vielen Dank vorab für eure Unterstützung!

Paul

Sub LookupValues()
 
    Dim r As Range
    Dim wbLookup As Workbook, wbDestiny As Workbook
    Dim searchRange As Range
    Dim searchValue As Variant
    Dim sPfadQuelle As String, sDatei As String
    Dim varWert
 
    Application.ScreenUpdating = False
    sPfadQuelle = "C:\Users\xxx\Desktop\Test\"      'Pfad ggf. anpassn
    On Error GoTo Errhandler
 
    Set wbDestiny = ThisWorkbook ' Workbooks("Paul.xlsm") 'HIER NAME DER ZIELDATEI ENTSPRECHEND  _
 _
 _
ÄNDERN
    
    'HINWEIS QUELLDATEIEN R DURCHSCHLEIFEN
    For Each r In wbDestiny.Sheets("A").Range("D3:D21").Cells 'Blattname ggf anpassen
        If r.Text = "Projekt" Then
            r.Offset(0, 4).Value = "" '??? ggf. Zeile weglasen
        Else
            sDatei = sPfadQuelle & "Info_" & r.Text & ".xlsx" 'HIER Syntaxt für Dateiname ggf.   _
 _
_
ANPASSEN
            If Dir(sDatei) = "" Then
                MsgBox "Datei """ & sDatei & """ niht gefunden"
            Else
                searchValue = r.Offset(0, -3).Value
                Set wbLookup = Workbooks.Open(sDatei, ReadOnly:=True)
                Set searchRange = wbLookup.Sheets(1).Range("A4:C27")
 
                varWert = Application.VLookup(searchValue, searchRange, 3, False)
                If IsError(varWert) Then
                    r.Offset(0, 4).Value = "#NV!"
                Else
                    r.Offset(0, 4).Value = varWert
                End If
                wbLookup.Close savechanges:=False
            End If
        End If
    Next r
 
    GoTo Beenden
Errhandler:
 
    MsgBox Err.Description, vbCritical
 
Beenden:
    Application.ScreenUpdating = True
 
End Sub

WVERWEIS mit Bezug auf mehrere dynamische, externe Dateien

Martin Asal @, Mittwoch, 08.05.2019, 14:55 (vor 135 Tagen) @ paul.david

Hallo Paul,

erst mal vorab: Das ist ein typisches Beispiel, das zeigt, dass irgendwann die Möglichkeiten von Excel eher ausgeschöpft sind. An diesem Punkt solltesst du über eine Datenbank nachdenken, z.B. Access.

Grundsätzlich kannst du das aber doch auch ganz ohne VBA lösen? Öffne z.B. mal die Datei "C:\Info_Maier.xlsx". Danach gehst du in deiner Auswertungsdatei auf F4 (Januar für den Mitarbeiter Maier). Dort gibst du ein "=" ein, und wählst in "C:\Info_Maier.xlxs" in die Zelle mit dem zugehörigen Wert. Das schließt du mit Enter ab, und dann steht in F4 ca folgende Formel:

='C:\Info_Maier.xlsx]Tabelle1'!$F$3

Den Rest solltest du mit geschicktem Copy/Paste hinbekommen. Du solltest nur wissen, was die "$"-Zeichen bedeuten und sie bei Bedarf ganz oder teilweise rausnehmen.

Die Werte bleiben auch erhalten, wenn die Info-Dateien geschlossen werden.


Martin

WVERWEIS mit Bezug auf mehrere dynamische, externe Dateien

Paul, Mittwoch, 08.05.2019, 15:08 (vor 135 Tagen) @ Martin Asal

Hallo Martin,

ersteinmal danke für deine Antwort.

Das Problem ist, dass wenn ich es nicht über VBA löse, sondern direkt mit Formeln mich auf die Dateien beziehe, diese immer geöffnet sein müssen, damit die Werte abgerufen werden können, oder nicht?

Geht es nicht, indem man meinen Code entsprechend anpasst?


Beste Grüße,
Paul

WVERWEIS mit Bezug auf mehrere dynamische, externe Dateien

Martin Asal @, Mittwoch, 08.05.2019, 16:13 (vor 135 Tagen) @ Paul

Das Problem ist, dass wenn ich es nicht über VBA löse, sondern direkt mit Formeln mich auf die Dateien beziehe, diese immer geöffnet sein müssen

Nein

RSS-Feed dieser Diskussion
powered by my little forum