Exclude a single xl file from macro

[starsky]

Hi,

I use the code below to delete all xl files at a certain destination on a weekly basis. Because someone in the organisation has left one open it won't work. Therefore I want to exclude that single file (let's call it "Obstacle.xls") so that the macro will delete all the other xl files within the parent folder and subfolders. Can't figure out the syntax though..

Thanks.

[VBA]Sub DeleteAllXLSFiles()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook
Dim fs

Set fs = CreateObject("Scripting.FileSystemObject")

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "\\Parent directory\"
.SearchSubFolders = True
.FileType = msoFileTypeExcelWorkbooks

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count ' Loop through all.

fs.DeleteFile .FoundFiles(lCount)

Next lCount

End If
End With
End Sub[/VBA]

[JP2112]

Inside the inner For loop, can you check if .FoundFiles(lCount) = "Obstacles.xls" and act accordingly? You might also have to adjust the loop so it runs backwards.

[Bob Phillips]

How about just wrapping the delete statement in On Error resume Next ... On Error Goto 0

[Zack Barresse]

If you're ever going to upgrade your Excel (to 2007 or above), I recommend finding another solution now, because some knuckle head arbitrarily decided that the FileSearch method didn't need to be in the Object Model anymore, because nobody uses it. You can use either the Dir() or FSO methods, both forward and backward compatible.

[Bob Phillips]

If you're ever going to upgrade your Excel (to 2007 or above), I recommend finding another solution now, because some knuckle head arbitrarily decided that the FileSearch method didn't need to be in the Object Model anymore, because nobody uses it. You can use either the Dir() or FSO methods, both forward and backward compatible.

That is totally disingenuous. It was no knucklehead, it was not arbitrary.

The MVP community (at least) was asked and responded that they had no problem with it being removed. Poor old MS, damned when they do, damned when they don't.

[Zack Barresse]

No they weren't! At least I wasn't! The gentleman I spoke with from MS more or less said someone just "made a decision" to take it out. And it was absolutely a knucklehead, Bob. The FileSearch method was awesome. And even if it wasn't, it certainly shouldn't have been taken out once it was put in!

Poor old MS??? Seriously? No, not on this one.

[Bob Phillips]

Sorry, but I was there, and I remember MS posting the question.

The 'gentleman .. from MS' didn't necessarily know, MS is famous for being devolved units.

[Zack Barresse]

Well whaddya know, I was there too. I'm glad somebody remembers it. At least MS took the time to ask a few people what they thought, even if they weren't ever going to act on it. I can't imagine what goes through people's heads when they make decisions like that.

Okay, off the soap box, and done hijacking the thread.

[Bob Phillips]

If you were there, how come you don't remember them asking?

They, MS, did listen, as I said, the majority of those who responded said they didn't use it and felt it would be no loss.

[starsky]

Thanks for the responses. As any of 250 workbooks might be left open, xld's suggestion seems most appropriate, no need for me to identify the workbook(s) in question at this stage . I can then use another macro to list whatever is left after deletions.

The 'hijack' is of interest too. I will look into the alternative search methods suggested.

[JP2112]

You might have mentioned in the first place that it could be any workbook. Your OP implied that it was only one particular workbook that could be open.

[Zack Barresse]

If you were there, how come you don't remember them asking?

They, MS, did listen, as I said, the majority of those who responded said they didn't use it and felt it would be no loss.

I remember being told about it, not being asked. Must've been a different meeting eh. Regardless of the semantics, it's absurd. I know it wasn't used much, but put it in then take it out? Arbitrarily make people not be able to have their code forward compatible? Craziness.

[Bob Phillips]

I remember being told about it, not being asked. Must've been a different meeting eh. Regardless of the semantics, it's absurd. I know it wasn't used much, but put it in then take it out? Arbitrarily make people not be able to have their code forward compatible? Craziness.

It was done via the NGs, not a meeting. But my point is how can it be absurd and arbitray if they asked and the answer was saying it was fine to get rid of it. I know Mericun isn't the same as English but I thought we defined arbitray the same way.

Anyway, maintaining backwards compatibility for all products, all functionality is a huge constraint on MS, it gets harder and harder to make changes. Oracle, Sun, Applewould never constrain themselves so.

[Zack Barresse]

Mericun? Oh it's English, Bob, just not your English. Moving on.

Tell you what, let's not call it absurd or arbitrary, you can be happy about that, and I'll just call it retarded. Is that ok with you? I don't care if they asked the Prime Minister, my President, or my Mother, I still think it was a retarded thing to do, regardless of how difficult it was to keep that small piece of coding in the OM.

Why exactly are we arguing about this anyway?