Checking for Open Files

[Marston]

I have a section of code that isn't executing and
I can't seem to figure out why...

Assume that the variable sStr hold the name
of a folder. I've checked this part and I know
that its working. When I run this section of code
with a file open it doesn't seem to be picking up
the Err = 0 piece, it just runs right past it.



Sub()

Dim fnStr,sStr as At String

With Application.FileSearch
.NewSearch
.LookIn = sStr
.SearchSubFolders = False
.Filename = ".xls"
.FileType = msoFileTypeExcelWorkbooks
If .Execute() > 0 Then
For j = 1 to .FoundFiles.Count
If WorkbookIsOpen(.FoundFiles(j)) Then
fnStr = .FoundFiles(j)
Workbooks(fnStr).Activate
ActiveWorkbook.Close([False])
End If


End With


Private Function WorkbookIsOpen(wbname) As Boolean
Dim X as Workbook
On Error Resume Next
Set X = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function

 

[Frank Kabel]

Hi
try
Private Function WorkbookIsOpen(wbname) As Boolean
Dim X as Workbook
On Error Resume Next
Set X = Workbooks(wbname)
If Err = 0 Then
WorkbookIsOpen = True _
Else
WorkbookIsOpen = False
end if
on error goto 0
End Function

 

[Tom Ogilvy]

wbname will have a value like

C:\Myfolders\MyFile.xls

so this will never work as an argument to the Workbooks collection which is
expecting something like "Myfile.xls" and you will always get the result
that the file is not open.

Likewise, these lines will fail as well:

fnStr = .FoundFiles(j)
Workbooks(fnStr).Activate



Sub()

Dim fnStr as String,sStr as At String

With Application.FileSearch
.NewSearch
.LookIn = sStr
.SearchSubFolders = False
.Filename = ".xls"
.FileType = msoFileTypeExcelWorkbooks
ilen = Len(sStr)
if right(sStr,1) <> "\" then ilen = ilen + 1
If .Execute() > 0 Then
For j = 1 to .FoundFiles.Count
fnStr = Right(.Foundfiles(j),len(.FoundFiles(j))-ilen)
If WorkbookIsOpen(fnStr) Then
Workbooks(fnStr).Close False
End If
Next
End if
End With
End Sub

Private Function WorkbookIsOpen(wbname) As Boolean
Dim X as Workbook
On Error Resume Next
Set X = Workbooks(wbname)
If Err = 0 Then WorkbookIsOpen = True _
Else WorkbookIsOpen = False
End Function