在文件夹中查找最新文件并打开它(vba访问) [英] 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.

使用if语句进行了测试，但没有发现任何问题.但是一旦使用do while，我会收到运行时间6溢出的错误消息.

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.

len(dir())不能与循环一起使用吗?

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

下面是我的代码.

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.

非常感谢您.

推荐答案

由于循环没有终点，因此您只是进入堆栈溢出.只要number = 0，它就将继续运行，并且由于在循环中变量号始终等于0，因此循环永远不会停止.您应该将一些绑定绑定到while循环中，以使其在中断时到达某个终点，或者完全不使用它.您想要达到的目标可能是以下

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.

这篇关于在文件夹中查找最新文件并打开它(vba访问)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！