努力在指定的文件夹中打开最近的.xls。找不到文件错误 [英] Struggling to open most recent .xls in specified folder. File Not Found error

问题描述

这是我的第一篇文章，所以如果我没有提供足够的信息，我很抱歉。我会尽力。

This is my first post so I apologize if I fail to give enough information. I'll do my best.

我正在尝试扫描特定的文件夹，并打开最近的标题为的Excel文件。这些文件被命名为'9 1 13'和'9 2 13'等等。我的sub正确地引导自己到正确的文件夹，并标识最新的文件。但是，当我尝试打开它，我得到一个运行时错误1004。

I am attempting to scan through a specific folder and open the most recently titled Excel file. The files are named '9 1 13' and '9 2 13' ect. My sub correctly steers itself to the right folder and identifies the most recent file. However, when I attempt to open it, I get a runtime error 1004.

文件'9 2 13.xlsx'找不到，检查拼写....

File '9 2 13.xlsx' could not be found, check spelling....

它显然找到了正确的文件和路径，所以为什么不能打开它？我当前的子下面。在任何人要求之前，文件路径'\\Hsrkdfs\hsdata\rk\grp06 ....'是因为我正在从一个网络网络访问映射不一样。有些从G：驱动器访问这个文件夹，其他的则是R :,这个宏必须在所有的计算机上运行。错误发生在'Workbooks.Open strFilename 行。

It clearly has found the right file and path to it, so why can't VBA open it? My current sub is below. Before anyone asks, the file path '\\Hsrkdfs\hsdata\rk\grp06....' is because I am pulling from a network where everyone's network access isn't mapped the same. Some access this folder from the G: drive, others the R:, and this macro must be functional from all computers. The error occurs on the 'Workbooks.Open strFilename line.

Sub GetMostRecentFile()

Dim FileSys As FileSystemObject
Dim objFile As File
Dim myFolder
Dim strFilename As String
Dim dteFile As Date

'set path for files - CHANGE FOR YOUR APPROPRIATE FOLDER
Const myDir As String = "\\Hsrkdfs\hsdata\rk\grp06\Rockford Repair Station   Quality\DELIVERY\Daily Status report - commercial"


'set up filesys objects
Set FileSys = New FileSystemObject
Set myFolder = FileSys.GetFolder(myDir)


'loop through each file and get date last modified. If largest date then store Filename
dteFile = DateSerial(1900, 1, 1)
For Each objFile In myFolder.Files
    If objFile.DateLastModified > dteFile Then
        dteFile = objFile.DateLastModified
        strFilename = objFile.Name
    End If
Next objFile
Workbooks.Open strFilename

Set FileSys = Nothing
Set myFolder = Nothing

End Sub

推荐答案

尝试使用 .Path ，返回完整路径，而不是 .Name 它只返回文件的名称和扩展名。

Try using .Path which returns the full path, rather than .Name, which only returns the name and extension of the file.

strFilename = objFile.Path

这篇关于努力在指定的文件夹中打开最近的.xls。找不到文件错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！