使用VBA打开.xlsx，“文件使用中"错误.只读不起作用 [英] Opening .xlsx with VBA, File in Use error. Read-only not working

问题描述

我正在尝试浏览特定的文件夹并打开最近标题为Excel的文件.这些文件的名称分别为"10 1 13"和"10 2 13".我的子可以正确识别最新文件.但是，当它尝试打开它时，出现以下错误:

I am attempting to scan through a specific folder and open the most recently titled Excel file. The files are named '10 1 13' and '10 2 13' ect. My sub correctly identifies the most recent file. However, when it attempts to open it, I get the following error:

'Filename' is currently in use. Try again later.

该文件通常会被某人使用，但每天仅修改一次.我需要做的就是打开一个只读工作簿并从中复制数据.无需修改或保存，这就是为什么我尝试了"ReadOnly:= True"的说法，但仍然收到错误消息的原因.

The file will usually be in use by someone, but is only modified once a day. All I need to do is open a read-only workbook and copy data from it. No modifying or saving is required, which is why I tried the 'ReadOnly:= True' arguement, but I still get an error message.

文件路径'\ Hsrkdfs \ hsdata \ rk \ grp06 ....'是因为我是从一个网络中提取的，每个人的网络访问都不相同.有些人可以从G:驱动器访问此文件夹，而其他人则可以通过R:访问该文件夹，并且该宏必须在所有计算机上都可以使用.调试将我指向工作簿.打开文件名:= strFilename，只读:= True"行.

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. Debug points me to the 'Workbooks.Open Filename:=strFilename, ReadOnly:=True' line.

是否有一种更强大的方法来打开工作簿?不管使用什么，强制将其打开的另一种方法?还是一种完全避免冲突的方法?谢谢.

Is there a more robust way to open the Workbook? Another method to force it open regardless of use? Or a way to avoid the conflict entirely? Thank you.

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.Path
        End If
    Next objFile
    Workbooks.Open Filename:=strFilename, ReadOnly:=True  

    Set FileSys = Nothing
    Set myFolder = Nothing

End Sub

推荐答案

如果只需要读取文件，请尝试使用GetObject.像这样:

Try using GetObject if you only need to read the file. Something like this:

Dim wb As Workbook

Set wb = GetObject(strFilename)

'Change this line to reflect the ranges you need to copy/paste.
wb.Sheets("Sheet1").Range("A1").Copy ThisWorkbook.Sheets("Sheet1").Range("A1")

wb.Close

使用此应该，您可以从工作簿中复制它是否被其他用户(包括您)打开.

Using this should allow you to copy from the workbook whether it's open by another user or not (including you).

我注意到，如果工作簿受到保护，或者您要从中复制的工作表受到保护，则此方法不起作用.

I have noticed that this approach doesn't work if the workbook is protected or if the sheet you're trying to copy from is protected.

此外，如果代码与要粘贴到的工作表位于同一工作簿中，则仅像上面一样使用ThisWorkbook即可.

Also, only use ThisWorkbook like I did above if the code will be in the same workbook as the sheet you want to paste to.

这篇关于使用VBA打开.xlsx，“文件使用中"错误.只读不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！