Checking for Open Files
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
--
Regards,
Tom Ogilvy
"Marston" wrote in message
om...
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
|