AW: mögliche Fährte....
14.08.2013 14:01:59
Peter
Hallo Steffen
Vielen Dank für dein Input.
Ich konnte nun Dropdown-Liste ertellen - und angezeigt wird die Datei, welche zuletzt geändert wurde.
Gruss, Peter
Option Explicit
Sub Find_NewestFile()
Dim Dateiname As String, lRow As Integer, sPfad As String, lPfad As Long, choice As String, Rng _
As Range
sPfad = ThisWorkbook.path
lPfad = Len(sPfad) - InstrRevN(sPfad, "\")
sPfad = Left(sPfad, lPfad) & "\Austesten\*.xls*"
If xSheetExists("Temp", Date) Then
Sheets("Temp").Cells.ClearContents
Else
Sheets.Add
ActiveSheet.Name = "Temp"
End If
Dateiname = Dir(sPfad) 'Pfad anpassen
Do While Dateiname ""
If InStr(Dateiname, "ABC") > 0 Then 'String anpassen
lRow = Sheets("Temp").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Temp").Cells(lRow + 1, 1) = Dateiname
Sheets("Temp").Cells(lRow + 1, 2) = FileDateTime(Application.WorksheetFunction. _
Substitute(sPfad, "*.xls*", "") & Dateiname)
End If
Dateiname = Dir()
Loop
lRow = Sheets("Temp").Cells(Sheets("Temp").Rows.Count, 1).End(xlUp).Row
Sheets("Temp").Names.Add Name:="Dateiname", RefersToR1C1:= _
"=Temp!R2C1:R" & lRow & "C1"
Sheets("Temp").Visible = False
choice = "=Dateiname"
With Sheets("Cockpit").Range("A5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:= _
choice
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Set Rng = Range("Dateiname")
Set Rng = Rng.Resize(Rng.Rows.Count, Rng.Columns.Count + 1)
Rng.Sort key1:=Sheets("Temp").Range("B2"), order1:=xlDescending, Header:=xlNo
Sheets("Cockpit").Range("A5").Value = Sheets("Temp").Range("A2") 'Auswahl neuste Position
Set Rng = Nothing
End Sub