Gruppe
Datei
Problem
In Spalte A sollen die Werte aus A1 aus allen Tabellenblättern "Tabelle1" aller Arbeitsmappen aus dem in Zelle B1 genannten Verzeichnis eingelesen werden.
StandardModule: Modul1
Sub Einlesen()
Dim iCounter As Integer, iRow As Integer
Dim sfile As String, sPath As String
iRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
With Application.FileSearch
.LookIn = Range("B1").Value
.FileType = msoFileTypeExcelWorkbooks
.Execute
For iCounter = 1 To .FoundFiles.Count
sfile = Dir(.FoundFiles(iCounter))
sPath = WorksheetFunction.Substitute(.FoundFiles(iCounter), sfile, "")
With Cells(iRow, 1)
.Formula = "='" & sPath & "[" & sfile & "]Tabelle1'!A1"
.Value = .Value
End With
iRow = iRow + 1
Next iCounter
End With
End Sub