5

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.

ljacqu
  • 2,132
  • 1
  • 17
  • 21
francis
  • 53
  • 1
  • 1
  • 3
  • I'd suggest adding a reference to the Microsoft Scripting Runtime (Tools, References...) and use the FileSystemObject to find the files you are looking for. Note your code uses Excel but the tag is MS Access. I wouldn't use the Excel automation unless you really have to. You can use the Shell method to launch the xls file. – rheitzman Aug 25 '14 at 17:18

2 Answers2

14

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.

trekkertx
  • 5
  • 4
george
  • 3,102
  • 5
  • 34
  • 51
  • thank for very much for your input. I forgot to move the boundary condition inside the do while loop haha. Im actually just looking for one file in that naming format since all previous files are actually archived. – francis Aug 25 '14 at 18:06
  • In the loop, I would expect `FileName = Dir(Directory)`? – Timo Jun 14 '21 at 12:10
  • Just working great in Excel, thank you! – OLLI_S May 26 '23 at 14:45
0

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
micstr
  • 5,080
  • 8
  • 48
  • 76
francis
  • 53
  • 1
  • 1
  • 3