Intermittent error - cannot open file - with VBA

Question

I have a Visual Basic macro in Excel 2010 that includes opening, closing and saving a number of different workbooks - both xlsx files and csv files.

Recently, I have started getting the error message below when it tries to open one of the csv files using the following code.

Workbooks.Open "Y:\InvoiceLog\Exports\apptexport.csv"

Microsoft Excel cannot access the file "Y:\InvoiceLog\Exports\apptexport.csv" There are several possible reasons:

• The file name or path does not exist
• The file you're trying to open is being used by another program. Close the document in the other program, and try again.
• The name of the workbook you're trying to save is the same as the name of another document that is read-only. Try saving the workbook with a different name.
• The file definitely does exist at this location and I can open it on another computer and it does not say that the file is in use
• If I stop the macro, close Excel and start the macro again, then it does open this file successfully
• The error does not happen every time that I run the macro
• By the time that the error occurs, it has already successfully opened and closed other csv files (i.e. it does not occur on the first csv file)
• When the error does reoccur, it is not necessarily when trying to open the same file (although it does always seem to be a csv file)
• I have used this spreadsheet for about 8 years and have never had this problem before
• It seems that this error is only occurring for one particular user, I have not seen this problem occur for any other user
• We have recreated this users roaming profile, which fixed the error at first, but it has now started to happen again for this user
• I have figured out that it is only an error relating to csv files as the same line of code still works to open .xlsx files from the same location

I'm running out of ideas as to why this error is happening. Any help is greatly appreciated!

Thanks

Tim

Answer 1

It is obvious that the server / windows locks that file. Maybe it is a similar issue as the "access denied" when you try to copy and rename a folder.

Windows 7 creates thumbs.db files while browsing network folders. So if you copy a folder on a network drive, Windows 7 creates the thumbs.db file in the background and locks that folder until the creation is done. Sounds fast, but on networks drives it can take a while. If it tried to delete the copied folder immediately, I got always an access denied. But when I browse the folder it was empty!

In fact the folder isn't empty, there is a thumbs.db in that folder and this file is the reason.

Follow this steps to disable the thumbs.db on network folders:

1. Open your registry editor.
2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Windows\
3. If key “Explorer” doesn’t exists create a key named “Explorer”
4. In key “Explorer” create a new DWORD 32 bit value named “DisableThumbsDBOnNetworkFolders” with value = 1
5. Reboot your system

If that doesn't work, try to open the file readonly:

  Workbooks.Open "Y:\InvoiceLog\Exports\apptexport.csv", ReadOnly:=True

If you need to write into that file, check if the file is closed before you open the file.

Andreas.

PS.: I'm on vacation the next 3 weeks, an answer may take a while.

Function IsOpen(ByVal FileName As String) As Boolean

  'True if FileName is open

  Dim slot As Integer

  If Dir(FileName) <> vbNullString Then

    On Error Resume Next

    slot = FreeFile

    Open FileName For Binary Access Read Lock Read As #slot

    IsOpen = Err.Number <> 0

    Close #slot

  Else

    IsOpen = False

  End If

End Function