Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for Open Files
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Checking for Open Files
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 -- Regards Frank Kabel Frankfurt, Germany "Marston" schrieb im Newsbeitrag 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Checking for open workbook... | Excel Programming | |||
Checking to see if a Workbook is Open | Excel Programming | |||
Checking if workbook is open | Excel Programming | |||
Checking for Open Workbook | Excel Programming | |||
Using wildcard for checking whether files are open | Excel Programming |