Private Function GetMaxNummer() As String
Dim oFile As Object, sNr As String, sPath As String
sPath = "D:\Test"
With CreateObject("Scripting.FileSystemObject").GetFolder(sPath)
For Each oFile In .Files
sNr = Split(Split(oFile.Name, ".")(0) & "-", "-")(1)
If Val(sNr) > Val(GetMaxNummer) Then GetMaxNummer = sNr
Next oFile
End With
End Function
Private Sub Workbook_Open()
With ThisWorkbook.Sheets("Tabelle1")
.Cells(7, 4).value "PSM2020-" & Right$("000" & Val(GetMaxNummer) + 1, 4)
End With
End Sub