在文件夹中查找最新文件并打开它（vba 访问）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/25490868/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
finding latest file in a folder and opening it (vba access)
提问by francis
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.
使用 if 语句测试，我没有发现任何问题。但是一旦我使用了 do while，我就会收到一条运行时 6 溢出的错误消息。
does len(dir())not work with loops?
不适len(dir())用于循环？
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.
非常感谢。
回答by george
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
您只需进入堆栈溢出，因为您的循环没有终点。只要 number = 0，它就会继续运行，并且由于在循环中变量 number 始终等于 0，因此循环永远不会停止。您应该对 while 循环设置一些绑定，以便它在中断时到达某个终点，或者根本不使用它。您要实现的目标可能如下
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.
当循环遍历所有文件时，此循环将停止。
回答by francis
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
