AW: Zellen aller Dateien in einem Ordner auslesen
21.06.2018 17:38:23
Michael
Hallo!
zB so:
Sub a()
Dim WbZ As Workbook, WbQ As Workbook
Dim WsZ As Worksheet, WsQ As Worksheet, Pfad$, Datei$
Application.ScreenUpdating = False
With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "Bitte Verzeichnis wählen"
.AllowMultiSelect = False
If .Show -1 Then
MsgBox "Vorgang abgebrochen", vbInformation
Exit Sub
Else: Pfad = .SelectedItems(1) & "\"
End If
End With
Set WbZ = Workbooks.Add(template:=xlWBATWorksheet)
Datei = Dir(Pfad & "*.xls*", vbDirectory)
Do Until Datei = vbNullString
Set WbQ = Workbooks.Open(Pfad & Datei)
Set WsQ = WbQ.Worksheets(1)
Set WsZ = WbZ.Worksheets(1)
WsQ.UsedRange.Copy WsZ.Cells(WsZ.Rows.Count, 1).End(xlUp).Offset(1, 0)
WbQ.Close False
Datei = Dir
Set WbQ = Nothing: Set WsQ = Nothing: Set WsZ = Nothing
Loop
Set WbZ = Nothing: Set aL = Nothing
End Sub
LG
Michael