Solved: Checking if a file is in use before Overriding

[SherryO]

Hi,
I have fourteen files that I routinely have to archive a copy of, then override the existing file with a new one. I have all of that code working fine, but I can't seem to be able to adapt Firefly's code from the KB about checking if a file is in use to more than one file. Can someone show me how to walk through a directory to see if any of the files are open. If so, I merely want to exit the sub, to try again later. Any help would be greatly appreciated.
Thansk,
SherryO

[Zack Barresse]

Hi there SherryO!

Can you explain what you are trying to do here? Are you wanting to check the local machine only or over a network? Shared files?

[SherryO]

I want to check to see if the network files are in use by any one else before I copy my version from a different network share to that share overriding the existing copies. Basically I run a bzillion reports everyweek and then publish to the network share. I want to make sure no one is in anything before I attempt to copy the files, which is in code, but with no error handling and I had a problem last week because one of the files was in use, so I had to manually copy it later. Not a big deal, but I'd rather do it right the first time, so I wanted to add to the code to see if the files were in use first.
Thanks for your help!!

[mdmackillop]

Not too sure about this, but if you try to open the file for ExclusiveAccess, this may generate an error you can use to determine your code actions.

[stanl]

you might also consider the workbook.MultiUserEditing property. Stan

[Ken Puls]

Ivan F Moala has 2 methods to accomplish this. Both are linked from his site here (http://www.xcelfiles.com/IsFileOpen.html).

The advantage of his methods are that they actually tell you who has the file open as well. This is kind of handy if you want to go yell at the user to get out. ;)

[SherryO]

How do I go about applying it to an entire directory on the network share? Thank you!!

[Zack Barresse]

Sherry, the solutions Ken linked are on a per client/user basis. This means that the check would need to be done by each user each time they tried to access the file. One option might be to install this in an addin in a shared directory and have each user link to it.

[Ken Puls]

Huh? Zack, what are you talking about?

Using the VBA Method from Ivan's site, you can use a routine like the following:
Sub RunIt()
Dim strFiles As String
Dim aryFiles() As String
Dim lFile As Long

strFiles = "H:\Test\File1.xls,H:\Test\File2.xls,H:\Test\File3.xls"
aryFiles = Split(strFiles, ",")

For lFile = 0 To UBound(aryFiles)
If IsFileOpen(aryFiles(lFile)) Then
'File is locked
MsgBox aryFiles(lFile) & vbNewLine & _
"is currently being edited by " & LastUser(aryFiles(lFile))
Else
'File is open
MsgBox aryFiles(lFile) & vbNewLine & _
"Is currently not in use!"
End If
Next lFile

End Sub

Just replace the file names and paths in the following lines with your 14 filenames:
strFiles = "H:\Test\File1.xls,H:\Test\File2.xls,H:\Test\File3.xls"

Make sure you separate them with commas, as that is necessary to split them up in the next step.

The next piece, deciding what you want to do if the file is open or not, I'll leave up to you. Currently it will tell you if it is available, or if it is locked and by whom.

HTH,

[stanl]

I don't know folks, but WorkBook.UserStatus is viable. If the return value is not an array, then the workbook is not shared, if it is then each array element is a csv line with 1=user name 2=when logged in 3=type of use [Exclusive=1,Shared=2]. The key is to use GetObject( xlFileName ). just .02... Stan

[Ken Puls]

Stan,

Would that method not require the workbooks to be opened first, so that you can check the UserStatus of the Workbook Object? Are you suggesting to open each workbook and test if you have exclusive access at that point, then close it and replace it?

[Ken Puls]

How do I go about applying it to an entire directory on the network share? Thank you!!

Hi,

I missed this the first time round. You want to check each and every .xls file in the directory?

Still using Ivan Moala's VBA version of the code to check if the file is in use, you could you FileSearch to check each file:

Sub ProcessFiles()
'Use FileSearch To Do Each .xls file in the directory

Dim fs As Object, i As Integer
Dim strDirectory As String

strDirectory = "D:\My Documents"

'Create the FileSearch object
Set fs = Application.FileSearch

With fs
'Set the directory to look in to the directory arguement supplied by the user
.LookIn = strDirectory
'Set to look for Excel files
.FileType = 4 '4 = Excel files
'Execute the search
.Execute

'Loop through the Excel files, processing ".xls" files
For i = 1 To .FoundFiles.Count
If Right(.FoundFiles.Item(i), 3) = "xls" Then
If IsFileOpen(.FoundFiles.Item(i)) Then
'File is locked
MsgBox .FoundFiles.Item(i) & vbNewLine & _
"is currently being edited by " & LastUser(.FoundFiles.Item(i))
Else
'File is open
MsgBox .FoundFiles.Item(i) & vbNewLine & _
"Is currently not in use!"
End If
End If
Next i

End With

'Release the FileSearch object
Set fs = Nothing

End Sub

HTH,

[Zack Barresse]

@Ken: A side note, did you know that there is no FileSearch method in the Access 2007 object model?! I think it's there for the Excel, but don't remember (I uninstalled it). I think MSFT is trying to get away from using it. I've been changing all of my scripts over to use the FSO method. Quite the PITA, I know. Just fyi.

[Ken Puls]

@Ken: A side note, did you know that there is no FileSearch method in the Access 2007 object model?!

You're kidding! To be completely honest, I tend to use FSO for most of my solutions anyway. (For whatever reason, I just got used to using it.) FileSearch does have a pretty nice feature that you can limit to just one file type though. Saves a bunch of looping through unnecessary files, and works really well for this type of application.

[Bob Phillips]

You're kidding! To be completely honest, I tend to use FSO for most of my solutions anyway. (For whatever reason, I just got used to using it.) FileSearch does have a pretty nice feature that you can limit to just one file type though. Saves a bunch of looping through unnecessary files, and works really well for this type of application.

So does Dir.

[stanl]

Stan,
Would that method not require the workbooks to be opened first

precisely why I said use GetObject().

[Zack Barresse]

Call me thick, but why do we need the GetObject method here??

[Ken Puls]

So does Dir.

Hey Bob,

I mocked up a quick test using Dir, but I can't get it to pull just xls files. Is there a way to limit it, like with FileSearch?

Here's what I used:
Sub ListFiles()
Dim strDirectory As String
Dim strFile As String
strDirectory = "J:"

strFile = Dir(strDirectory)
Do Until strFile = vbNullString
Debug.Print Dir
strFile = Dir
Loop
End Sub

[Norie]

Ken

Don't you just add a wildcard?

strDirectory = "J:\*.xls"

[stanl]

Call me thick, but why do we need the GetObject method here??

Sorry, I was thinking ADSI or a server-based module.

[Ken Puls]

Ken

Don't you just add a wildcard?

strDirectory = "J:\*.xls"

Apparently so! Thanks, Norie! :thumb

[stanl]

and just to continue the GetObject() logic before y'all clobber me completely. You can use WMI and the CIM_DataFile class to interrogate server files. Then, the following, albeit not 100% is available:



Option Explicit

Dim oWMI, oFile, strFilePath
strFilePath = "H:\ExcelFiles\myfile.xls"

Set oWMI = GetObject("winmgmts:\\.\root\cimv2")
Set oFile = oWMI.Get("CIM_DataFile.Name='" & strFilePath & "'")

MsgBox oFile.InUseCount ' --> Most likely Null

[Ken Puls]

Hi Stan,

Works better if you change the MsgBox line to the following:

Debug.Print oFile.InUseCount
Otherwise you get an "invalid use of Null" error. When I had a file open on my own PC, though, I still get Null as a response...?

[Zack Barresse]

Gotcha Stan. :thumb

[stanl]

Hi Stan,
though, I still get Null as a response...?

Yes, that is a fact, though I think if multiple user had a file open on a network it is viable. To be honest, when I used to overwrite and backup files I would schedule such for midnight, just after the server had kicked everyone offline.: pray2:

[Bob Phillips]

Apparently so! Thanks, Norie! :thumb

FSO doesn';t have that option of course, but it does allow you to test the file type (Microsoft Excel File), or even the extension. It is flexible, but Dir is quicker.

[Ken Puls]

It feels weird to use Dir though... at least to me. I'm used to the "For each obj in collection" approach. Using Dir in a Do loop feels... don't know... less controlled in a way...

Does work though, can't argue that. It's too bad that fso couldn't limit right off the bat. :)

[Zack Barresse]

Usually all paradigm shifts feel "weird". LOL!