0

I have been looking for codes on the internet and writing some myself to open the latest pdf file in a sharepoint folder. The files that I am interested in the folder are all named as such "SD Progress_YYYYMMDD.pdf". So I tried having a for loop through all the files in this folder and comparing the YYYYMMDD in each file names and keeping the highest value (classic max value programming). Unfortunately I am quite new with vba and I believe that I have a mistake with string or array dimensions in my code below but I can't quite figure it out. The following error occurs at the first If statement:

Run-time error '13': Type mismatch

You guys are the experts so if you have any advices for my code below please I am very interested. Thank you

CODE BELOW HAS BEEN EDITED AND WORKS NOW. THANK YOU.

Sub Shop_Drawing_Status()
Dim MyPath As String
Dim LatestDate As Integer
Dim MyFile As String

MyPath = "C:\Users\Documents...etc\"
MyFile = Dir(MyPath & "*.pdf", vbNormal)

While Len(MyFile) > 0
    If Right(MyFile, 3) = "pdf" Then
        LatestFile = Split(MyFile, ".")
        If Right(LatestFile(0), 4) > LatestDate Then
            LatestDate = Right(LatestFile(0), 4)
        End If
    End If
    MyFile = Dir()
Wend

ActiveWorkbook.FollowHyperlink (MyPath & "SD Progress_2020" & LatestDate & ".pdf")
On Error Resume Next

End Sub
Johndoeuf
  • 1
  • 1
  • 1
    `MyFile` is not an array but a string with the name of the first file matching the pattern you passed to `Dir()` - process that, then call `Dir()` (with no arguments) to get the next match (or an empty string if there are no more matches) Eg see https://stackoverflow.com/questions/10380312/loop-through-files-in-a-folder-using-vba – Tim Williams Oct 08 '20 at 16:29
  • Thank you so much for your answer, I understand how `Dir()` works now. I edited my code in the post with what is working. Just FYI I encountered another error after fixing the mistakes that you spotted. It was an overflow because comparing an 8 digits number was to much I guess. So I ended up comparing only MMDD and not YYYYMMDD. – Johndoeuf Oct 08 '20 at 18:14
  • `LatestDate` should be a String, not Integer - your formatted dates are not really numbers as such, and a string comparison will work fine with this format. – Tim Williams Oct 08 '20 at 20:06

0 Answers0