Macro FileSearch works in 2003, not in 2007

[JustATheory]

Greetings,

This code loops through excel files in a directory and performs a process on each file. I wrote it in MS-Excel VBA 2003, works well. I tried it in 2007 and it halts. It halts on the FileSearch. Is this a known issue and can someone point me in the direction to fix this? Other code has migrated well, this is the only one that has been an issue with 2007. ('Loop Code Here, is proprietary and works in 2007, just a place holder)

Thanks in advance for your help,
Andy


Sub FileCount()
Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook

Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next

Set wbCodeBook = ThisWorkbook

With Application.FileSearch
.NewSearch
'Change path to suit
.LookIn = "C:\Directory"
.FileType = msoFileTypeExcelWorkbooks
'.Filename = "Book*.xls"

If .Execute > 0 Then 'Workbooks in folder
For lCount = 1 To .FoundFiles.Count 'Loop through all.
'Open Workbook x and Set a Workbook variable to it
Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)

‘Loop Code Here


Next lCount
End If
End With

On Error GoTo 0
Application.ScreenUpdating = True
Application.DisplayAlerts = True
Application.EnableEvents = True
End Sub

 

[AnotherHiggins]

Have a look here:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=450752&SiteID=1

or here:

http://support.microsoft.com/kb/935402

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[fumei]

I still would very much like to know WHY they removed FileSearch from the object model. There were things you could do with FileSearch that you can not do with FSO.

For example, you can not use the .PropertyTests method with an FSO object. This is also not available to Dir.

I have procedures that use a combination of FileSearch and FSO, using unique features of both. These can not work in 2007. Well the FileSearch parts can not, and I really would like to know why this functionality was taken away from us.

grrrrr

faq219-2884

Gerry
My paintings and sculpture

 

[JustATheory]

Greetings Gerry and John,

To Gerry, I agree, why do they take things away when they function well.

To John, Thanks for pointing me in the right direction, it works like a charm.


Thanks,
Andy

 

[WalkerEvans]

Gerry,

There is an old "Three-Part Test" that the government uses to determine the worth of any project. Based on my acute familiarity with that test it is my solemn opinion that Miscrosoft has borrowed it and incorporated it into their daily operations as a 'must do' consideration.

Briefly, the test goes as follows:

"1. Does it work?
2. Does it provide good service to the customer?
3. Is it cost-effective?

If the answer to any of these questions is "Yes" then ...


DO IT SOME OTHER WAY!!"



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[fumei]

Maybe so, and while it can be postulated that government is driven by "the market", in my mind it is a different market. Microsoft though is utterly driven by the market....except....they have to a large degree reversed the direction. They drive the market.

The overwhelming dominance of Office within the business world (and more and more the academic world - students now must hand in Word files to professors) means that most of us are trapped. We must use their products.

I may have personal opinions about monopolies and capitalism...but who cares? The point is, while we may joke about deliberate inefficiencies, deliberate removal of access seems counterintuitive.

WHAT does Microsoft gain by taking away under-the-hood access to functionality? The vast majority of users never did anything with FileSearch, and indeed most likely knew nothing about it.....or cared less.

The only ones who knew about it, or used it, were - for the most part - non-Microsoft Office product VBA developers. People like us.

WHAT does Microsoft gain by removal of a significant functionality? It is not like it fell off by accident. The removal of FileSearch from the Object Model had to be a deliberate, conscious, decision. A decision was made, and work (billable hours?) was expended to action that decision.

Again, it did not fall off the Object Model by accident. Someone, or someones, took time, and effort, to remove it.

Why?

Mind you...who cares? It is a done deal. I very much doubt that those of us annoyed with having no FileSearch, OR a reasonable replacement functionality, have much say in the matter.

faq219-2884

Gerry
My paintings and sculpture

 

[WalkerEvans]

Gerry,

Sorry if my last post sounded flippant; while it has been a long-standing joke in certain areas of government service there is more than a grain of truth to it. This is exemplified by a recent message announcing that our little corner of paradise was going to be switched to Vista/Office 2007 in the near future. We're using XP now and things are running quite smoothly, so of course we have to change things; can't let the users get too comfortable or efficient after all!

Unfortunately, I think you have answered all of our questions in your post:

The only ones who knew about it, or used it, were - for the most part - non-Microsoft Office product VBA developers. People like us.

According to a fellow who is a permanent beta-tester for Microsoft one thing he is asked to do is rate the various functionalities as to his perception of their worth to the average user. Since he is an instructor who teaches various levels of Word, Excel, Access (etc) he actually is in a position to have a pretty good idea on this, and answers accordingly. If the boys in Redmond get enough "hardly anyone uses this" answers it seems they delete that function. I can't swear this is true, but it's what my man says. Considering how bloated all the Microsoft stuff is, they may try to strip stuff that isn't well used just to keep the size down - only a guess, of course. I wish they could get people like Steve Gibson (

http://www.grc.com)

to write their code; his assembly language stuff is a study in how coding should be done.

I dearly wish they would leave all of the code we use alone. Having a copy of Office 2007 at home has shown me that nearly all of the stuff I have written for use in this office will have to be re-done once we make the shift. If only one or two people were impacted it would be one thing, but some of my stuff is used by dozens here, and I don't know how many out there in the so-called "real world". At this time it appears that some of these may not work under 2007 at all, no matter how they're tweaked.

A complaint/request has been forwarded to Redmond on this, but to date no reply has been received - and I'm not holding my breath. As you note, it appears to be a completely done deal, and I really don't believe that a relatively few complaints from our group are likely to change anything, but I had to at least give it a shot.



----------------------------------------------------------------------------------
"A committee is a life form with six or more legs and no brain." -- L. Long

 

[fumei]

Steve Gibson is awesome. I have admired his stuff for years. Both grc products themselves, but also his clear and concise writing.

As for Vista, my work place is fighting a rearguard action, but eventually, we will be forced to it. I dread the day. We bought 200+ new machines (with Vista) and blew Vista away on all of them. I am fighting like heck to hold back Office 2007 for as long as I can, but I am fairly sure we will forced to it as well.

faq219-2884

Gerry
My paintings and sculpture