Missing VBA Code in Excel 2007

[Rob]

Hi,

I have been trying Excel 2007 and one of my models that works fine in Excel
2000, doesn't run the VBA code below, if I retype this the option from the
prompt , suggests that FileSearch is no long an option. Surely this can't
be true?

With Application.FileSearch
.NewSearch

Thanks, Rob

 

[Bob Phillips]

I am afraid that it is true, FileSearch has been dropped from 2007 (it was
buggy, and MS decided to drop it rather than invest in fixing it).

Look at Dir in help, it will achieve the same results.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Rob]

Bob,

Shame about the change in 2007, have looked at help but unsure where to go
with this change in code as had removed Excel 2000 and can't search help to
find out what FileSearch was doing. Is it a case of changing just part of
the code or all of it? I have placed part of the code below that seems to
be falling over. Thanks, Rob

With Application.FileSearch
.NewSearch
.LookIn = "N:\UniSE\" & Worksheets("Sheet1").Range("Year") &
"\SALES\"
.SearchSubFolders = False
.Filename = "SALE" & Worksheets("Sheet1").Range("WK") & ".xls"
.MatchTextExactly = True
If .Execute() Then

 

[Bob Phillips]

Try this instead

If Dir("N:\UniSE\" & Worksheets("Sheet1").Range("Year") & "\SALES\" & _
"SALE" & Worksheets("Sheet1").Range("WK") & ".xls") <> "" Then


--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Rob]

Thanks for you help. The revised code worked insomuch that the run error
happened after the DIR code on

Workbooks.Open Filename:=.FoundFiles(1)

If the code could work, I'd then somehow need to save the file in DBF format
which Excel 2007 doesn't appear to support. Hopefully, 2008 will see a
version which is more compatible.

Thanks, Rob