Opening .xlsx with VBA, File in Use error. Read-only not working

Question

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.

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.

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

hit ctrl+alt+delete and just make sure YOU do not have an extra instance of excel hanging under Processes tab. It is possible that you are have the file open in another invisible instance thats why you can't reopen it. Another thing is to switch Application.DisplayAlerts = False when trying to open the workbook but I doubt this would help much in your case. — user2140173
– user2140173, Commented Oct 1, 2013 at 16:41
If you only need to extract some data from it, maybe you can use ExecuteExcel4Macro method? Or, you could maybe create a copy (FileSys.CopyFile(strFilename, _Destination_, True) and then kill the copy when you're done? — David Zemens
– David Zemens, Commented Oct 1, 2013 at 20:00

ARich · Accepted Answer · 2013-10-01 20:47:42Z

0

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.

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.

answered Oct 1, 2013 at 20:47

ARich

3,2995 gold badges33 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Opening .xlsx with VBA, File in Use error. Read-only not working

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related