finding latest file in a folder and opening it (vba access)

I'm trying to open the latest file in a folder via button macro in access with the following code.

Tested using the if statement and I didn't see any problems. But once I used do while, i receive an error message of run time 6, overflow.

does len(dir()) not work with loops?

Below is my code.

Private Sub Command4_Click() Dim ~~~~ As Object Set ~~~~ = CreateObject("Excel.Application") Dim path As String Dim name As String Dim count As Long Dim number As Long path = "C:\Users\~~~~~\Desktop\~~~~~~~~~~~~\" number = Len(Dir(path & "~~~~~~~ - " & Format(Now() - count, "MMMM dd, yyyy") & ".xlsm")) Do While number = 0 count = count + 1 Loop ~~~~~.workbooks.Open path & "~~~~~~~ - " & Format(Now() - count, "MMMM dd, yyyy") & ".xlsm" End Sub 

the ~ lines are just placeholders due to confidentiality.

Thank you very much.

2 Answers

You simply go in stack overflow because your loop does not have an end point. It will continue running as long as number = 0 and since in the loop the variable number always equals 0 then the loop never stops. You should either put some bound to your while loop so that it reaches some end point when it breaks or not use it at all. What you are trying to achieve is probably the following

Function NewestFile() Dim FileName As String Dim MostRecentFile As String Dim MostRecentDate As Date Dim FileSpec As String 'Specify the file type, if any FileSpec = "*.*" 'specify the directory Directory = "C:" FileName = Dir(Directory & FileSpec) If FileName <> "" Then MostRecentFile = FileName MostRecentDate = FileDateTime(Directory & FileName) Do While FileName <> "" If FileDateTime(Directory & FileName) > MostRecentDate Then MostRecentFile = FileName MostRecentDate = FileDateTime(Directory & FileName) End If FileName = Dir Loop End If NewestFile = MostRecentFile End Function 

This loop will stop when it loops through all files.

Here is the code I used eventually. It works fine but I hope there is not additional problems associated with memory leaks or security or something.

Private Sub Command4_Click() Dim ~ As Object Set ~ = CreateObject("Excel.Application") Dim path As String Dim count As Long Dim number As Long path = "C:\Users\fkong\Desktop\~\" count = 0 Do While Len(Dir(path & "~ - " & Format(Now() - count, "mmm dd, yyyy") & ".xlsm")) = 0 number = Len(Dir(path & "~ - " & Format(Now() - count, "mmm dd, yyyy") & ".xlsm")) count = count + 1 Loop ~.Visible = True ~.workbooks.Open path & "~ - " & Format(Now() - count, "mmm dd, yyyy") & ".xlsm" End Sub