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

by

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.

More Related Contents:

  1. ms access vba code for update records through form
  2. How to add default signature in Outlook
  3. Combine rows / concatenate rows
  4. Is it possible to pass parameters programmatically in a Microsoft Access update query?
  5. Getting Error 3340 Query ‘ ‘ is corrupt while executing queries DoCmd.RunSQL
  6. Too few parameters Expected 1, recordset issue
  7. Show “Open File” Dialog
  8. How do I display a messagebox with unicode characters in VBA?
  9. Determining whether an object is a member of a collection in VBA
  10. Distinction between using .text and .value in VBA Access
  11. MS Access VBA Error: Run time error ’70’ Permission Denied
  12. Automatically generating handling of issues
  13. Is it better to use ADO or DAO in Access 2007?
  14. VBA – Folder Picker – set where to start [duplicate]
  15. MS Access: how to compact current database in VBA
  16. How to loop through all controls in a form, including controls in a subform – Access 2007
  17. FileDialog doesn’t work
  18. Working with Unicode file names in VBA (using Dir, FileSystemObject, etc.)
  19. Determine real cause of ODBC failure (error 3146) with ms-access?
  20. Is there an equivalent to Thread.Sleep() in VBA
  21. Exporting MS Access Forms and Class / Modules Recursively to text files?
  22. How to get logged-in user’s name in Access vba? [duplicate]
  23. Pass a parameter into an access report programmatically
  24. Insert multiple records with a date range in MS Access
  25. MS Access VBA: Sending an email through Outlook [closed]
  26. SendKeys is messing with my NumLock key via VBA code in Access form
  27. Runtime error 287. Sending Emails through Outlook using VBA in Access
  28. VBA: Selecting range by variables
  29. Use autofilter on more than 2 criteria
  30. Can I simultaneously declare and assign a variable in VBA?

Leave a Comment