Option Explicit
Private Function get_last_Number() As String
Dim fso As Object
Dim oFolder As Object
Dim oFile As Object
Dim temp As String
Dim fileName As String
Dim i As Integer
Set fso = CreateObject("Scripting.FileSystemObject")
Set oFolder = fso.GetFolder("C:\Users\senoglukext\Desktop\KaanSenoglu\Kaan\TEST ORDNER")
temp = "0000"
For Each oFile In oFolder.Files
i = InStrRev(oFile.Name, ".") - 1
fileName = Left(oFile.Name, i)
If Right(fileName, i - 4) > temp Then
temp = Right(fileName, 4)
End If
Next oFile
If temp < "" Then get_last_Number = temp
Set fso = Nothing
Set oFolder = Nothing
End Function
Private Sub Workbook_Open()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)
With ws
.Cells(7, 4).Value = "PSM2020" & CStr(get_last_Number)
End With
End Sub