My routine work in Excel is such that I should constantly download some data from some websites and saving each data in different folders. After downloading, the data files are saved in download folder. When downloading files with the same name in different days, the files are saved with ' (1)', ' (2)', etc. added to their name. Now I want to have a code to open the most recent file of specific data. I have written the following code to do so, but it fails (based on attached screenshot, the code is expected to open "statement (7).xls" but it opens the "statement.xls"). Can you give me some guides on this issue?


Option Explicit
Sub Opendownload()

Dim MyPath As String
Dim MyFile As String
Dim myfilename As String
Dim myfilename02 As String
Dim Nmyfile As String
Dim n As String
Dim temp As String


'Specify the path to the folder
MyPath = "C:\Users\s.kalantari\Downloads"
myfilename = "statement"


Nmyfile = Len(myfilename)


'Make sure that the path ends in a backslash
If Right(MyPath, 1) <> "" Then MyPath = MyPath & ""

MyFile = Dir(MyPath & "*.xls", vbNormal)

temp = 0
Do While Len(MyFile) > 0 And Mid(MyFile, 1, Nmyfile) = myfilename
'by n I expect to extract 1,2,etc from (1), (2), etc
n = Mid(MyFile, Nmyfile + 3, 1)

If n > temp Then
temp = n
myfilename02 = MyFile
End If

Attached Images

download_folder.PNG (47.3 KB, 10 views)

Attached Files

opendownload.xlsm (18.9 KB, 2 views)