Checking if files open prior to running macro

  • Thread starter Thread starter Mikey May
  • Start date Start date
M

Mikey May

Is it possible to check if a number of files are open
prior to running a macro that pulls data from the required
files.

After testing with a file open, Excel does not prompt you
that the files is already open but when trying to save the
files to overwrite the original the macro falls down.

Thanks in advance!!!
 
This is a repost--newsreader problems. But I did want to add that if you're
using xl2k or higher, you can remove the split97 function and just change this
line:

mySplit = Split97(FileNameToCheck, "\")
to
mySplit = Split(FileNameToCheck, "\")

(Split was added in xl2k.)

This is the original post:

How about something like this:

Option Explicit
Sub testme01()

Dim wkbk As Workbook
Dim myFileName As Variant

Set wkbk = ActiveWorkbook 'for testing
myFileName = Application.GetSaveAsFilename

If myFileName = False Then
Exit Sub 'user hit cancel
End If

If isFileNameInUse(wkbk, CStr(myFileName)) Then
MsgBox "there's a file with that name already open" _
& vbNewLine & "Try again"
Exit Sub
End If

Application.DisplayAlerts = False
wkbk.SaveAs Filename:=myFileName
Application.DisplayAlerts = True

End Sub
Function isFileNameInUse(wkbkToSave As Workbook, _
FileNameToCheck As String) As Boolean

Dim testWkbk As Workbook
Dim JustFileName As String
Dim mySplit As Variant

If StrComp(wkbkToSave.FullName, FileNameToCheck, vbTextCompare) = 0 Then
isFileNameInUse = False 'ok to SaveAs
Exit Function
End If

mySplit = Split97(FileNameToCheck, "\")
JustFileName = mySplit(UBound(mySplit))

Set testWkbk = Nothing
On Error Resume Next
Set testWkbk = Workbooks(JustFileName)
On Error GoTo 0

isFileNameInUse = Not CBool(testWkbk Is Nothing)

End Function
'from Tom Ogilvy
Function Split97(sStr As Variant, sdelim As String) As Variant
Split97 = Evaluate("{""" & _
Application.Substitute(sStr, sdelim, """,""") & """}")
End Function

It prompts the user for a name and then looks to see if you're just saving the
original file. If you are, then that seems like it's ok to me.

But if you're trying to duplicate a name of a file already open, then it stops
and yells.

Another way is just to turn off error checking and then check to see if an error
would have occurred:

On Error Resume Next
ActiveWorkbook.SaveAs Filename:="book1.xls", FileFormat:=xlNormal
If Err.Number <> 0 Then
MsgBox "something bad happened--save didn't occur"
Err.Clear
End If
On Error GoTo 0

(I had a different workbook named book1.xls already open.)
 
Thanks Dave

unfortunately a lot of that code is way above me, still a
beginner I'm afraid. I can see what you are attempting to
do with the code, but not sure how I can apply it to my
particular situation.

I have about 15 files and I want to check whether any of
them are open prior to running a macro that pulls data
from each of the 15 files.

I am assuming that the files will have to be opened
individually and once the check is done then a message box
giving the path name (path name is the name of the user)
will appear if the file is still being used by another
user.

I appreciate your help as this is a major problem I have
at the moment and I have macros falling down left, right
and centre.
 
You could set up an array of your filenames:

Option Explicit
Sub testme()

Dim iCtr As Long
Dim testWkbk As Workbook
Dim myFileNames As Variant

myFileNames = Array("file1.xls", "file2.xls", "file3.xls", "file4.xls")
'but keep going to all 15

For iCtr = LBound(myFileNames) To UBound(myFileNames)
Set testWkbk = Nothing
On Error Resume Next
Set testWkbk = Workbooks(myFileNames(iCtr))
On Error GoTo 0

If testWkbk Is Nothing Then
'that's good--nothing is open
Else
MsgBox myFileNames(iCtr) & " is already open" & vbNewLine & _
"please close it and try again"
Exit Sub
End If
Next iCtr

MsgBox "ok to continue"

End Sub
 
I have tried the code supplied and it doesn't seem to
work. I have one of the files open on another pc on the
network and no error occurs. I do get the OK message once
the macro has run.

Not sure if the foloowing is relevant but all the files
have the same file name ie 'CTS Master' but are placed in
different folders withing another folder named 'CTS
Transfers'

-----Original Message-----
You could set up an array of your filenames:

Option Explicit
Sub testme()

Dim iCtr As Long
Dim testWkbk As Workbook
Dim myFileNames As Variant

myFileNames = Array
Click to expand...
("file1.xls", "file2.xls", "file3.xls", "file4.xls")
 
Back
Top