Dynamischer Verweis auf andere Dateien

08.04.2025 14:31:43

Jacqueline Behrens

Moin Zusammen,

Ich habe eine Liste die - vereinfach - aussieht wie folgt. Nun möchte in in der dritten Spalte Daten ausweisen, die ich in Abhängigkeiten von Standort und KW in andere Dateien haben. Dieser steht je KW in einer eigenen Datei. Mit indirekt kann ich zwar verweisen, habe dann aber das Problem, dass die Dateien geöffnet sein müssen. Ihr habt bestimmt eine Idee? Vielen Dank! LG Jacqueline

Standort KW
1 1
1 2
2 1
2 2
3 1
3 2

AW: Dynamischer Verweis auf andere Dateien

08.04.2025 14:44:25

{Boris}

Hi,

ohne VBA mit einer "Krücke": https://www.herber.de/excelformeln/pages/Auf_Zellwert_mehrerer_geschlossener_Dateien_zugreifen.html

Ansonsten per VBA die einzelnen Zellverknüpfungen erzeugen (mit Stringverkettung und der Formula-Eigenschaft).

VG, Boris

AW: Dynamischer Verweis auf andere Dateien

08.04.2025 15:11:27

velo

Hallo,

das ganze kann man z.B. so lösen:

Option Explicit



Sub getValues()

    Dim ws As Worksheet

    Dim i As Long

    Dim path As String

    

    Set ws = ThisWorkbook.Worksheets("Tabelle1")

    

    With ws

        For i = 2 To .Range("A" & .Rows.Count).End(xlUp).Row

            path = "='C:\Ordner\[KW" & .Cells(i, 2).Value & ".xlsx]Tabelle1'!A1" 'hier wird die KW aus Spalte 2 in den Pfad "gepflanzt"

            .Cells(i, 3).FormulaLocal = path 'in Spalte 3 dann der Wert aus der KW-Datei

        Next i

    End With

End Sub

Du musst halt hier noch den Pfad stark anpassen - welches Laufwerk, welche Ordner, welche Tabelle in der Datei und vor allem aus welcher Zelle der Wert geholt werden soll.

VG
velo

Variante mit ExecuteExcel4Macro

08.04.2025 15:42:59

{Boris}

Hi,

mit der ExecuteExcel4Macro-Methode kann man auf Zellwerte in geschlossenen Dateien zugreifen, ohne eine Verknüpfung zu erzeugen.

Dazu gehört diese UDF in ein allgemeines Modul:

Private Function GetValue(myPath, myFile, mySheet, myRef)

Dim sCellConnection As String

If Right(myPath, 1) > "\" Then myPath = myPath & "\"

If Dir(myPath & myFile) > "" Then

    sCellConnection = "'" & myPath & "[" & myFile & "]" & mySheet & "'!" & Range(myRef).Address(, , xlR1C1)

    GetValue = ExecuteExcel4Macro(sCellConnection)

Else

    GetValue = "File Not Found"

End If

End Function

Der Aufruf erfolgt dann in dieser Art, wobei ich zum Testen nur Konstanten verwendet habe. Diese musst Du auf Deine Bedürfnisse anpassen.
Am Ende kann man statt der Konstanten Variablen verwenden und diese nacheinander (z.B. in einer For-Each/For-Next-Schleife füllen):

Sub Wert_aus_geschlossener_Datei_auslesen()

Const sPath         As String = "C:\Users\boris\OneDrive\Dokumente\Excel\" 'Pfad

Const sFile         As String = "aktive_Zeile_färben.xlsx" 'Dateiname mit Dateiendung

Const sSheetName    As String = "Tabelle1" 'Blattname in der geschlossenen Datei

Const sRef          As String = "B7" 'Zelle in der geschlossenen Datei, die ausgelesen werden soll

Const sTarget       As String = "A1" 'Zielzelle

Range(sTarget) = GetValue(sPath, sFile, sSheetName, sRef)

End Sub

VG, Boris

In der Anlage ist ein...

08.04.2025 21:32:27

Case

Moin, :-)

… Beispiel mit Power Query: ;-)
https://www.herber.de/bbs/user/176660.zip

In N2 und N4 sind Formeln und den Zellen wurde jeweils ein Name vergeben. ;-)
Hier wird immer der Pfad angepasst. Es ist immer der Pfad der Datei plus "Standort". ;-)
Ich habe im Beispiel noch einen Unterordner "Standort". ;-)
Ist das bei dir anders, musst du natürlich anpassen. ;-)
Es sind drei Beispiele drin. Erst wird nur eine Datei gelesen und verglichen. Dann alle Dateien im Ordner Standort. Es werden auch leere, also nicht vorhandene ausgegeben. Die sind im dritten Beispiel weggefiltert. ;-)

Wenn du also den Download entpackst, funktioniert es gleich. ;-)

Hier noch ein Überblick zu Power Query: ;-)
https://excelhero.de/power-query/power-query-ganz-einfach-erklaert/
https://learn.microsoft.com/de-de/power-query/power-query-what-is-power-query
https://support.microsoft.com/de-de/office/hilfe-zu-power-query-f%C3%BCr-excel-2b433a85-ddfb-420b-9cda-fe0e60b82a94

Servus
Case

Oder sicher auch mit PQ...oT

08.04.2025 14:45:11

{Boris}

VG, Boris

AW: Oder sicher auch mit PQ...oT

08.04.2025 15:08:25

Jacqueline Behrens

PQ...oT? Verstehe ich nicht ...

PQ = Power Query - oT = ohne (weiteren) Text.....oT ;-)

08.04.2025 15:09:44

{Boris}

VG, Boris

Dynamischer Verweis auf andere Dateien

Forumthread: Dynamischer Verweis auf andere Dateien

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Dynamischer Verweis auf andere Dateien

Forumthread: Dynamischer Verweis auf andere Dateien

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

Forumthreads zu verwandten Themen

7
Beiträge zum Forumthread

Beiträge zu diesem Forumthread